{"id":4130,"date":"2023-10-25T12:08:53","date_gmt":"2023-10-25T12:08:53","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/?p=4130"},"modified":"2026-01-20T16:01:19","modified_gmt":"2026-01-20T16:01:19","slug":"planning-analytics-step-by-step-consolidations","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/planning-analytics-step-by-step-consolidations\/","title":{"rendered":"How Do I Perform Step-By-Step Consolidations in IBM Planning Analytics?"},"content":{"rendered":"

Planning Analytics – Step by Step Consolidations<\/h2>\n

What is Consolidation?<\/strong><\/p>\n

In Planning Analytics\/TM1,\u00a0dimensional elements that represent aggregations<\/strong>\u00a0are called \u201cconsolidated elements\u201d or consolidations<\/strong> (business users may also refer to them as \u201cgroupings\u201d).<\/p>\n

The following image shows three levels of consolidation for elements of a Period dimension. The months (200801) provide the lowest level of detail<\/em> (or Level 0). Groupings also exist for Quarters (Q1 2008) and Fiscal Years (FY2008) and a \u201ctop level\u201d grouping also exists for housekeeping (All Time):<\/p>\n

\"\"<\/p>\n

Where is the Data Stored?<\/strong><\/p>\n

The data you import into a cube provides a snapshot of your business at a specific level of detail<\/strong>. Using this example, you may import sales data at the monthly level and the dimension elements that identify these data points are simple or leaf-level elements in the dimension: i.e., sales for one month. By using consolidated elements, you can easily aggregate\u00a0numeric<\/em>\u00a0data into categories. In this case, we have quarterly elements (that sum quarterly sales amounts) and Fiscal Year elements (that sum sales amounts for the year).<\/p>\n

Subset or Consolidation?<\/strong><\/p>\n

Planning Analytics\/TM1 also provides Subsets<\/strong>. A Subset is\u00a0a \u201cspecific selection of elements\u201d from a dimension. IBM describes a subset is a \u201climited version of the parent dimension\u201d. \u00a0Again, using the periods example, what if we could create a subset of the monthly elements that make up each quarter? If you are intending to have the subset produce an aggregation<\/strong>, it won\u2019t, because the objective of a subset is to assemble a sort of \u201cpick list\u201d to save mouse-clicks when reporting \u2013 NOT to aggerate the values of the elements.<\/p>\n

To illustrate, below we can see that a user has created a subset named \u201cFirst Quarter\u201d consisting of the months Jan, Feb and Mar. Selecting the subset produces a \u201cpick list\u201d of just those months:<\/p>\n

\"\"<\/p>\n

But the resulting cube view does NOT aggregate the sales values of Jan, Feb, and Mar:<\/p>\n

\"\"<\/p>\n

Using PAW to Consolidate<\/strong><\/p>\n

You can edit dimensions with the\u00a0Dimension Editor<\/strong>\u00a0in PAW if you have Modeler or Administrator access. To create a grouping (consolidation) in the period dimension (using Planning Analytics Workspace or \u201cPAW\u201d) I prefer to use the modeling workbench (if you are unfamiliar with workbenches, you can read about it here<\/a>), so you can click on the Home<\/strong> menu, then click New<\/strong> and Workbench<\/strong> (shown below). Note: if you don\u2019t care to use a Workbench, you can access the dimension editor directly from the Data Tree<\/a>.<\/p>\n

\"\"<\/p>\n

From there, expand your database (on the left panel), expand dimensions, find and right-click on your dimension and\u00a0 select Edit dimension <\/strong>(I am using the dimension named \u201cPeriod_For_Testing\u201d for this exercise):<\/p>\n

\"\"<\/p>\n

The subset editing panel<\/strong> will open (shown below). You can click on the Add root member<\/strong> link on the far right:<\/p>\n

\"\"<\/p>\n

You will see the highlighted \u201cAdding 1 member<\/strong>\u201d banner highlighted and can enter the name we want to give to our consolidation, such as \u201cFirst Quarter<\/em>\u201d or \u201cSecond Quarter<\/em>\u201d into the provided textbox:<\/p>\n

\"\"<\/p>\n

Click on Commit<\/strong>:<\/p>\n

\"\"<\/p>\n

Planning Analytics will add the new element (member) to the dimension and give you the \u201cSuccessfully added<\/strong>\u201d message:<\/p>\n

\"\"<\/p>\n

Now you must define which elements you want to aggregate in this new grouping. You can scroll through the list of elements in the dimension, right click to select them and then click Copy<\/strong>:<\/p>\n

\"\"<\/p>\n

Once you have selected the elements, you can find your new grouping element, select it, and then click Paste<\/strong>, and As a child\u00a0 <\/strong>(this establishes the \u201cParent-to-Child\u201d hierarchy relationship between the elements).<\/p>\n

\"\"<\/p>\n

Always make sure to confirm that the new groupings look like what we expected:<\/p>\n

\"\"<\/p>\n

The last step in our little exercise is to see the expected result in our cube view \u2013 that the grouping \u201cFirst Quarter<\/strong>\u201d is an aggregation or \u201csum\u201d of Jan + Feb + Mar sales:<\/p>\n

\"\"<\/p>\n

Wrap Up<\/strong><\/p>\n

If you are wondering if there is another option for creating an aggregation of values without editing the dimension, there is. For example, you have the ability to enhance most any PAW view with various calculations – within the interface – without having to create a cube rule (or edit the dimension) which you can learn more about here<\/a>, but for \u201ccommon\u201d groupings that most users will want (like for quarter or year) you would want to create them by editing the dimension.<\/p>\n

Some things to look out for when adding groupings to a dimension include:<\/p>\n