Alternate Hierarchies vs. Ragged Hierarchies
To a TM1 developer, alternate hierarchies are sometimes synonymous with ragged hierarchies. When designing cubes for Cognos BI, it’s important to remember that they are structurally different concepts. How you model them in TM1 will affect your Cognos BI reports. Continuing where we left off last month, we will discuss some best practices for handling these hierarchy structures in a Cognos BI context.
Note: As of TM1 10.2, alternate hierarchies cannot be defined separately in the }HierarchyProperties cube.
Ragged Hierarchies Example
A good example of a true ragged hierarchy is a chart of accounts dimension. This dimension usually has a single top consolidation for all accounts, but not every element leaf in the dimension exists as the same depth (meaning not every element has the same number of parents or levels from the top).
In this example, the Sales element has 2 parents (Earnings before Taxes and Gross Margin) whereas the Advertising element has 3 parents (Earnings Before Taxes, Total Expense, and Marketing). Note: Both elements share the same top consolidation, Earnings Before Taxes.
This dimension would work perfectly in Cognos BI reports, except you would not be able to define any levels. Due to the nature of a chart of accounts dimension, levels are generally not relevant.
Alternate Hierarchies Example
A time dimension with relative time values (YTD, MTD, SMLY, etc.) is a good example of how alternate hierarchies work. You would never roll up YTD, MTD, SMLY, etc. under the same top consolidation.
In this example, Jan_2015 exists twice in two different rollups. You would not create a top consolidation for this type of dimension.
Additionally, you cannot define levels for this type of dimension since you are mixing relative time and standard time values. This can create a very big problem for Cognos BI report developers who want relative time and also the benefit of levels for standard time (Year, Month, Day). There is no easy answer to this problem. Cognos Transformer cubes can easily define alternate hierarchies for your relative time but as it stands now, TM1 does not have the same ability to differentiate between different hierarchies in the same dimension.
If you must have multiple alternate hierarchies with defined levels in your TM1 cube, consider making the alternate hierarchies separate dimensions. Warning: This could cause a lot of sparsity in your cube and hurt performance.
Relative Time Discussion
Defining relative time in TM1 for Cognos BI report developers to consume can be very tricky. The natural choice is to define relative time in the Time Dimension. This creates multiple limitations that have already been discussed in the Alternate Hierarchy example above. The biggest limitation of defining relative time in the time dimension is the inability to define levels and top consolidations.
An alternative solution is to define all relative time in your measure dimension instead. As a TM1 developer you can define rules to calculate each relative time value on the fly without having to reload data into your TM1 cube.
Some examples of these calculations are Sales YTD, Sales MTD, Profit Rolling 12 months, etc.
Relative Time Flag
As a bonus for the Cognos BI report developers, it is recommended that you create a flag for Current Period as an attribute on your time dimension. This can be useful for Cognos BI developers when they want to define their own relative time in their reports using MDX functions.
Top Consolidation in the Time Dimension
If the time dimension does not contain any relative time elements, then make sure there is a top consolidation in the time dimension for all values to roll up into. This makes it easier for Cognos BI report developers to use relative time MDX functions in their reports. Without a top consolidation, some MDX functions in Cognos BI will not work properly, like the LastPeriods function.
If you would like any further information on how to optimize your TM1 cubes for use in Cognos BI, Ironside is here to help. Get in touch with us to see what solutions we can implement for your environment.
