The Future of TM1 Time Dimensions in the World of Hierarchies

One of the most important considerations when designing a cube are the time dimensions. The vast majority of TM1 cubes will have at least one time dimension. It is the fundamental concept that allows the comparison of data between different periods.

There are numerous ways to structure time dimensions within a TM1 cube depending on the data that it holds and the requirements of the business.

All the different approaches for creating time dimensions and when each approach is most appropriate, are well described in the Best Practice Cube Design from the Bedrock Whitepapers.

In this article we will review these best practices, taking into consideration the new opportunities brought by IBM Planning Analytics (TM1 v11).

A very common business case is loading monthly data into a cube.

When thinking about how to design a cube, some dimensions are quite obvious. In this case, Version, Store, Release and Measure will be separate dimensions.

But what should we do with the Year and Month columns? Should we have separate or combined the Year and Month dimensions?

To find out about which approach is best, we will:

• Go through the best practices in the world of TM1
• Explain what changed with IBM Planning Analytics.
• Define new best practices when working with Time dimensions in the world of Hierarchies

When deciding how many time dimensions are required for your application, there are a number of key considerations to be made. These considerations include:

• What is the lowest level of time detail required in your cube?
• Will the data in your cube require regular and consistent time cycles?
• Will the data in your cube require any unusual, overlapping or inconsistent time cycles?
• Will your cube require any “rolling” time periods?
• Will your cube be sharing data with other cubes already in your system?

In addition to these considerations, it is essential to think about the level of effort required for the end user (when accessing data from the cube), but also for the administrator (when performing maintenance on the system). Your design should also consider:

• Flexibility when comparing data across multiple time periods
• Flexibility when creating new consolidated time periods in the future
• Maintenance effort required to update time dimensions as new cycles are added

The best approach: separate Year and Month dimensions

In our business case, the best approach is to have two time dimensions: one dimension for Year and another one for Month.

Therefore, the Sales cube will have 6 dimensions in total: Version, Year, Month, Store, Release, Sales Measure.

Having separate Year and Month dimensions makes year on year comparisons easier and requires less maintenance.

With separate Year and Month dimensions, seeing rolling time periods such as Last 12 months or Last 6 months will be quite challenging to achieve in a cube view.

Depending on how your users are accessing the data, you might end up building a reporting cube with a combined Year and Month dimension.

Let’s see now how IBM Planning Analytics can help us see easily rolling time periods and keep the flexibility of separate Year and Month dimensions.

With IBM Planning Analytics and the introduction of the TM1 REST API (relying heavily on MDX). One member is identified with its Member Unique Name (MUN) that looks like this [Dimension].[Hierarchy].[Parent2^Parent1^Element].

Members have always been identified using MUNs, but the limitation that TM1, back in 1999 when they got introduced, didn’t support Hierarchies, made the [Hierarchy] portion always the same as the [Dimension] portion, it was made optional.

Due to the MUN structure, the element 2017 Jan with two parents Jan and 2017, is actually two members.

What has changed in v11 is that the members are created when the server loads. Prior to v11 members were only created the first time it as used in MDX. When upgrading from TM1 to Planning Analytics you might have seen an increase in memory consumption as TM1 needs to allocate memory for each member.

Each element should have only one parent

To get the value, the ‘legacy’ clients such as Architect, Perspectives and TM1 Web are using the elements. But the new clients relying on MDX such as PAfe, PAW, Arc or Apliqo UX are using the members.

When building a new report with these new clients, if the parent of your element is deleted, it is going to change the MUN and therefore it might break your report.

To avoid confusion between elements and members, it is recommended that in each Hierarchy, each element should only roll-up to one parent (in this case elements would be equal to members).

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.

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 desired.

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 lead to increase of 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.

Time dimensions can greatly benefit from Hierarchies. All alternate roll-ups such as YTD, Fiscal Years, Relatives roll-ups (Current Month, Last 12 Months…) become one separate Hierarchy.

In the example of a combined Year Month dimension, you could have a Year hierarchy with periods (2017 Jan) rolling up to years (2017) and a Month hierarchy with period (2017 Jan) rolling up to a month (Jan):

It is safe to delete all elements in a Hierarchy

When a Hierarchy is added to a dimension in TM1 for the first time, a Leaves hierarchy is also automatically created in the background. The Leaves hierarchy has some special properties as it contains only leaf elements.

As it is explained in this article (an unintended benefit of using Hierarchies), a fair benefit of working with Hierarchies is that you can safely delete all elements of one hierarchy using the function HierarchyDeleteAllElement without risking to lose any data as the leaf elements will still exist in the Leaves hierarchy.

Hierarchies bring many new aspects to TM1. To learn more about Hierarchies, just click the link below to find the most relevant articles about Hierarchies:

LEARN MORE ABOUT HIERARCHIES

Let’s see now how we can apply Hierarchies to our business case.

In our business case, our Sales cube that had 6 dimensions will now have just 5, with a Period dimension combining Year and Month reducing the number of dimension will improve the speed of queries, i.e. it will be faster to access the data):

Hierarchies combine the best of the two worlds!

In a cube view, Hierarchies will look like separate dimensions. Hierarchies from the same dimension can be used in rows and columns. For example, having the Years Hierarchy on rows and the Months Hierarchy on columns).

For rolling time period, you could create a Hierarchy for Last 6 Months, Last 12 months, Current Month…). By crossing the Years Hierarchy with one of these relative Hierarchy, you can easily analyse the current month value across all years:

In this article we used Arc for TM1 to build Hierarchies but you could have used IBM Planning Analytics Workspace. Hierarchies cannot be seen with Architect or Perspectives.

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).

Building a dimension could be quick but populating all attributes can be time consuming. When working with Time dimensions, there is a lot of data that you might need such as next/previous year, next/previous month, different aliases…. It is even more time consuming when working with days and weeks. You could use Excel or an SQL server to prepare all the attributes values and then load it with a TI.

Arc for TM1 provides a Time Management tool to create date dimensions (Day, Week, Month & Year) with just few clicks. On top of creating the Hierarchies, it also populates the attributes values:

You can try Arc for TM1 for free for 3 months and it does not require any installation. Just download Arc here:

Hierarchies will challenge your current standard practices. There are a few things that you should be aware of:

• 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.
• DBRW does not support Hierarchies, this could be a showstopper if your applications or users are relying heavily on Active Forms.
• Rules can be specific to a Hierarchy. You just need to add the Hierarchy name between the dimension and element name (DimensionName:HierarchyName:ElementName)
• Security – can’t hide Hierarchies level in the tree but elements are hidden.

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

Building active forms with hierarchies

• How to create a Hierarchy with TM1 Process
• 4 ways to speed-up your TM1 processes