Mastering hierarchies in IBM Planning Analytics (TM1)

With IBM TM1 10.2.2 end of support coming in September 2019 now is an ideal time to consider upgrading to IBM Planning Analytics.

Before upgrading, an important consideration is whether or not to incorporate Planning Analytics’ new Hierarchy feature into your applications. There are many aspects to this decision, so the objective of this article is to give you enough information to be able to answer this question:

Let’s begin by answering this question: “What is a Hierarchy?” 

Until the arrival of Planning Analytics, what was commonly called a “hierarchy” in TM1 was simply a specific roll-up of C and N level elements in a dimension. For example, in a “Period” dimension, the same N-level elements could roll up to multiple C-level elements, with the roll-ups having names like “Full Year” and “Jun YTD”:

TM1’s basic cube structure has not changed since TM1’s invention in 1984: a cube is made of two or more dimensions, a cell’s value is attached to the N-level elements in those dimensions, and each dimension can have multiple consolidation paths that roll up N and C elements. In other words, there was a direct relationship between a dimension and its elements.

Planning Analytics has introduced “real” hierarchies to TM1. Instead of a straight path from a dimension to its elements, there is now the option of inserting an intermediate level. This container object is called a “Hierarchy”, and a dimension can have as many hierarchies as you wish.

By default, the new Hierarchy feature is not turned on – it must be enabled by adding the line EnableNewHierarchyCreation=T in the tm1s.cfg file. When you turn on Hierarchies:

• An extra level can be added between dimensions and elements in TM1’s object model
• Dimensions are no longer a container of elements, they are a container of Hierarchies.
• A default Hierarchy is created, which has the exact same name as the dimension itself (this is to maintain backward compatibility)
• Each dimension can now have multiple Hierarchies, with each Hierarchy containing its own set of consolidations and can include one or more of the leaf elements that are shared across Hierarchies.

The “before” and “after” of TM1’s object model looks like this:

It might look a bit more complex, but Hierarchies provide greater design flexibility and substantial performance benefits.

Hierarchies behave like “virtual dimensions”, enabling you to overcome one of the legacy limitations of TM1 – the need to rebuild cubes to accommodate new analysis dimensions.

Provided the analysis Hierarchy, your application becomes more flexible and agile to accommodate evolving business requirements. Adding a Hierarchy does not require recreating a cube, nor the modification of existing load processes or reports.

A major performance benefit of Hierarchies is that you can reduce the number of dimensions in an analysis cube, and fewer dimensions increases query performance. For every dimension that is removed, at minimum one level is removed from the cube index (the actual number of levels removed depends on the number of N level elements in the dimension). The net impact of a smaller index is that a query will require fewer “passes” to retrieve a cell value, resulting in greater query performance.

However, using Hierarchies in lieu of dimensions will not necessarily reduce memory consumption, as data structures still need to be created whether the alternate rollups exist within a single dimension or in separate hierarchies.

Dimensions will still exist but they are not used in the cube, they are only used for establishing the dimension order.

After creating your first Hierarchy, the “Leaves” Hierarchy will be automatically added to the list of available Hierarchies. In the example below, the “Type” Hierarchy has been added to the Department dimension, and three Hierarchies (“Department”, “Leaves” and “Type”) are now available:

Each Hierarchy can have its own leaf elements; when you delete a leaf element from a Hierarchy, the element is not deleted from other hierarchies or the Leaves Hierarchy. What this means is that data is still stored for this element in the underlying cubes.
If you want to delete a leaf element and delete the data in the cube (i.e. TM1’s traditional behaviour), you must delete it from the Leaves Hierarchy. This will delete the element from all hierarchies, and remove all cube data referenced by the leaf element.

To work with Hierarchies in TurboIntegrator (TI), Planning Analytics has introduced a set of new Turbo Integrator Functions. IBM has ensured these new functions are very similar to the TI functions for dimensions, and there is usually a 1-1 corresponding function. For example, you would use HierarchyExists (check if a Hierarchy exists) instead of DimensionExists (check if a dimension exists).

In the example below, the TI code on the left creates a dimension and on the right it creates a Hierarchy – you can see they are very similar:

Working with Hierarchies will make your cube rules a bit more complex, because to reference an element in a Hierarchy, you must use the new “Hierarchy-qualified” syntax, as follows:

• DimensionName:HierarchyName:ElementName

Note: You would need the DimensionName only if the ElementName is ambiguous.

If you omit the Hierarchy name, the “default” Hierarchy is used, which has the same name as the dimension.

In the example below, you can see two DB calls, the first one without Hierarchy and the second one referencing departments only in the “Type” Hierarchy:

Everything you need to know about working with hierarchies in rules can be found here:

• How to work with hierarchies in rules

In Planning Analytics, Hierarchies are stored as separate .dim files within the dimension’s “ }hiers” folder in the TM1 Server’s data directory.

Although you can store attribute values for the same named element on different hierarchies, everything is stored in a single }ElementAttributes_ cube. This makes sense since that’s exactly how it works for all the data cubes as well.

In processes and rules, “Attr” functions such as AttrS & AttrPutS functions still work for hierarchies, you just need to use DimensionName:HierarchyName for the dimension name instead of simply the dimension name.

For each “Attr” functions, IBM has introduced new functions to cater for hierarchies starting with “ElementAttr“, for instance:

• Attrs -> ElementAttrs
• AttrPuts -> ElementAttrPuts
• AttrInsert -> ElementAttrInsert

These new functions behave the same as the old ones with some slightly changes as you can see below:

AttrInsert vs ElementAttrInsert

To create a new attribute on a Hierarchy only, you should use the new function ElementAttrInsert instead of AttrInsert so the syntax will look like this:

• ElementAttrInsert(cDimSrc, cHierarchy, ”, cAlias, ‘A’);

Instead of 

• AttrInsert(cDimSrc | ‘:’ | cHierarchy, ”, cAlias, ‘A’);

The ElementAttrInsert will avoid duplicate alias on one consolidation which appears in two hierarchies of the same dimension.

With hierarchies, Subsets are not attached to a dimension but to a Hierarchy, if you are using IBM’s PAX, or Cubewise Arc, you will see the Subsets attached to Hierarchies.

If you do not specify a Hierarchy when creating subset, it will be added to all existing Hierarchies in the dimension.

The syntax of Picklists is also impacted by Hierarchies.

As you might expect by now, the syntax for subsets must also be “hierarchy-qualified”, so instead of SUBSET:Dimname:Subname you must specify SUBSET:Dimname:Hierarchy:Subname

i.e. replace subset:Entity:FE – Division with subset:Entity:Entity:FE – Division

To reference an element in a Hierarchy in MDX, you must specify the Hierarchy name in your query, for example, instead of [Time].[2018] you will need to use [Time].[Time].[2018] to specify element “2018” from the default Hierarchy (remember, the default Hierarchy has the exact same name as its containing dimension).

One of the advantages of using the cube viewer in PAx or Arc versus Perspectives is that you will not need to specify a selection for all dimensions to retrieve cube values. In fact, selecting even a single dimension in a cube view will result in some cube values being retrieved.

This is because for all the other dimensions not referenced in the cube view, TM1 will use the dimension’s default member. To define default members, you will need to specify the defaultMember value in the }HierarchyProperties cube:

If the defaultMember doesn’t exist in the }HierarchyProperties cube the first element will be used (via index order).

It should be noted that in this cube view, hierarchies are treated as “virtual” dimensions (Time, Time:Fiscal Year, Time Half Year…). If you use levels in PAx or Cognos Analytics, this is where you will define them. To apply the changes, you must run the RefreshMdxHierarchy process function.

Currently with Planning Analytics v2.0.5, the main weakness of Hierarchy is on the reporting side. Unfortunately, the DBR function in Active Forms does not support hierarchies. If you are a heavy user of Active Form, it might be challenging to reproduce your Active Forms using hierarchies.

Alternatives to PAX Active Forms are:

• Cognos Analytics – this is a good option for read-only reporting, as Cognos Analytics fully supports MDX and the new Hierarchy structure.
• If you need both read and write capability (i.e. planning + reporting), Cubewise Canvas is a great solution, as the DBR in Canvas supports hierarchies.

As you might have guessed, the answer is “it depends”.

By using Hierarchies, you will gain in performance and flexibility, but you possibly lose some Excel reporting capabilities if you are a heavy user of Active Forms.

If you think you can reproduce your Active Forms with PAX’s Exploration Mode without using DBRs, or you do not need Excel-based reports at all, then it is a definite “yes” to implement Hierarchies.

Another way to build Active Forms with Hierarchies is to use Slice:

Building Active Forms with hierarchies

Re-architecting your applications to take advantage of the new Hierarchy capability will require some time and effort, but it is an investment that will pay off in the long, as your solutions will become nearly “future-proof” in accommodating evolving business requirements.

• IBM – Using Multiple Hierarchies
• Building a new Hierarchy with a TM1 process
• Upgrading from TM1 10.x to Planning Analytics
• Enabling hierarchies, how does it impact your current model
• Optimizing your TM1 and Planning Analytics server for performance