How to handle TM1 cube model with more than one parent (or alternative hierarchies)

Check list of all posts

There are three options to handle model with more than one parent: 1) build an alternative hierarchy; 2) add an attribute to a dimension; 3) build two dimensions based on the same level and different level. This document is intended to share some ideas with these three options and then to figure out the best design option.

1. Build an alternative hierarchy

Please see sample below

Advantages:

• Very good for data input, as all data will be rolled up as expected. (Data is saved at lowest level for customer and product.)
• Easy to correct data. When there is need to change their relationship between customer and group, or relationship between customer and manager, we can simply correct hierarchy, instead of changing data itself with different dimensions.

Disadvantages:

• It is impossible to have two hierarchies in a cube view, which can be needed in some cases from business point of view.
• It is impossible to have clear level definition by using Hierarchy property.
• It is very difficult to use in Cognos report, please check IBM document Avoiding BI Problems with Duplicate TM1 Cube Elements

2. Add an attribute to dimensions

Advantages:

• Very easy to build, as we only add a new attribute to the element
• Very easy to correct data. When there is need to change their relationship between customer and group, or relationship between customer and manager, we can simply correct attribute.

Disadvantages:

• You cannot get rollup data by attribute, the only way to do is to create framework manager and then use Cognos BI to get rollup data.
• Very difficult to be used for simple view, you can use TM1 prospective with active form for analysis.
• It is easy to use in Cognos report; however, this property will be exposed in all levels, and it could become confused.

3. Build two different dimensions based on the same lowest level

This option is very similar in Cognos BI to share the same column but model it as separated query subjects

Dimension	Customer with group	Manager
Simple element	Customer	Customer

Advantages:

• Very good for report, as you can generate any report without any special handing
• Easy to correct data, as we need to correct hierarchy, instead of correct measure itself

Disadvantages:

• It is very difficult or impossible to input data, as you need to make sure both dimensions are sharing the same element. Otherwise it does not make any sense. One smart idea to resolve this problem is with “TM1 Element Holder” (please check post How to use  “TM1 Element Holder”). This solution is difficult to implement.

4. Build two different dimensions based with different level

In this case, customer hierarchy with group becomes a dimension, while manager can become separated dimension based on element manager only. There are two simple elements, the one is customer, and another is manager.

Dimension	Customer with group	Manager
Simple element	Customer	Manager

Advantages:

• It is easy to input data, as you can simply to consider as real two different dimensions.
• It is very good to report as well

Disadvantages:

• Very difficult to maintain data consistence. Even there are two dimensions, but these two dimensions are related. When there is need to change their relationship between these two dimensions, the only way to resolve is to correct data itself, which is much more complicated than to correct hierarchy.

5. The best option

Obviously, each option has their advantages and disadvantages. The best solution is

• Create cube with alterative hierarchy for data input, such as budget and forecast
• Create cube with two different dimensions with different level for data analysis, such as BI report with framework manager