The Future of TM1 Hierarchies

As I started working with the still fairly new TM1 REST API last year and working with dimensions, I came across an entity related to dimensions within the REST API which didn’t make sense in the way I had worked with TM1 in the past, through the frontend tools or the API. In between the Dimension and Element was the Hierarchy entity. I was expecting “Dimension:Element” but what was required by the API was “Dimension:Hierarchy:Element”.

Hierarchies or consolidations, as they currently exist within TM1, are not going away or being altered, but they are being extended. In fact, hierarchies always existed in the dimension entity but were limited to one hierarchy per dimension with the same name as the dimension. What the new Hierarchy entity in the data design will allow you to do is to hold multiple discrete rollups or consolidations within the same dimension. These distinct hierarchies will share a common set of leaf or posting elements but the consolidations within these hierarchies will be isolated from each other.

An example of this would be an organisational restructure. Previously you weren’t able to have two rollups in the same dimension representing the previous and current organisation structure of a company rolling up into consolidations with the same names.

tm1-1

However, with the new Hierarchy functionality, multiple consolidations will now be possible as the hierarchy will make the structures distinct. In this example, by adding two hierarchies “Previous Structure” and “Current Structure”.

tm1-2

The hierarchy functionality also reduces the size and complexity of cubes as it will reduce the number of dimensions that will need to be included to facilitate analysis. In the example above, two options were available previously, and you could timestamp the consolidations which would give you similar functionality. However, in this case, you would need to write custom scripts to maintain these consolidations.

tm1-3

Making the rollups unique by appending or pre-pending a timestamp would also introduce complexity with reports needing to be updated or hard coded depending on the consolidation you wanted to use for analysis. The second option available would be to split the Cost Centre dimension into two dimensions. However, this adds complexity with reporting and cube design and users would not visually be able to see the consolidation, the consolidation would be enforced by the intersection of the data points. Something else to consider would be the number of dimensions, as the more dimensions in a cube the slower the query speeds.

Another benefit of the hierarchies’ functionality will be the ability to create virtual rollups based on attributes. An example of this would be if you considered the following Vehicle dimension.

tm1-4

The existing rollup only consolidates by Car and SUV and if the user wanted to consolidate by Drive Type they would have to create this calculation on the reporting layer or create a consolidation for Drive Type. Although the new consolidation for Drive Type is easily done.

tm1-5

What happens if this needed to be done by Vehicle Type and Drive Type is you could change the dimension as follows. But you would still need to keep the consolidation by Drive Type so that you could consolidate by 2wd and 4wd.

tm1-6

Depending on the requirements, this can become complex very quickly and increase maintenance. This is probably the time you are considering moving the Drive Type attribute to a dimension. This will simplify the reporting and maintenance, however it will make the cube more complex, increasing its size and increasing query times. The Hierarchy entity is a very elegant solution for this problem as you can either maintain an additional Drive Type hierarchy or create a virtual hierarchy as required based on the attribute.

tm1-7

This coupled with hierarchies from the same dimensions, being able to be stacked across different view axis (row, column and title) will allow you to satisfy the reporting requirements. You will be able to create a view using the Drive Type hierarchy and 2wd element as a title dimension and have the Vehicle Type hierarchy on the rows or columns to filter by 2wd, and have the consolidation by Vehicle Type.

This functionality is not currently available in the latest release of TM1 or Planning Analytics, however, the work to enable it has already started and can be seen in the REST API. That being said, the functionality when it does become available will most likely only be available in the new front end tools including CAFÉ and PAW and other 3rd party applications that utilise the ODATA REST API.

Share on ...

David Meadway

Practise Area Lead, Solutions

david.meadway@tridant.com.au

1300 737 141

View more articles by David Meadway

Leave a comment