Recreating TM1 Alternate Hierarchies

2/8/2017

Cognos TM1 allows alternate hierarchies which, from a user perspective, is greatly appreciated but from a TM1 Admin perspective, probably not so much.  The official hierarchy may be maintained from a data warehouse or general ledger while TM1 Alternate Hierarchies may be maintained from user defined data or attributes.  Also, TM1 Alternate hierarchies may be subjected to frequent changes e.g. territories assigned to managers.  And when multiple hierarchies are in use, maintaining or rebuilding one hierarchy without jeopardizing the remaining hierarchies can be tricky.  So, you need to create a Turbo Integrator Process to remove all children (including other parents) from a designated parent.

The following example is generic and can be used with any dimension and any parent within the dimension. Also, the rebuild of the hierarchy is not part of this TI Process.

The Data Source of this TI Process is None. (When rebuilding the hierarchy, select the appropriate Data Source.)

TM1 Alternate Hierarchies

There are no Variables or Maps in this generic TI Process. (If rebuilding, designate your Variables and Maps accordingly.)

Parameters:
TM1 Alternate Hierarchies

pDimension allows the Turbo Integrator Process to be generic and used with any dimension. (If rebuilding the hierarchy, pDimension can be omitted and the name of the dimension can be hard coded into the TI Process.)

pParent  is the Top Level Parent from which you want to remove all children. Even if this Turbo Integrator Process will be Dimension specific, skipping this parameter is not recommended since even a Top Level Parent can change. (You can populate the Default Value with the Top Level Parent.)

pYN is an optional parameter to determine if any parent elements below pParent should be deleted.

Parameter Check:

This is optional code to determine if the Parameters entered are valid. Dimension and Top Parent values must exist; pYN must be populated.

TM1 Alternate Hierarchies

Parent/child relationships may be used in more than one hierarchy.  In the example shown below, 1st Half and 2nd Half exist in both the TOTAL YEAR and YEAR hierarchies.

TM1 Alternate Hierarchies

If a parent/child relationship exists in the hierarchy with which you are working as well as another hierarchy, the relationship must be removed intact from the hierarchy with which you are working.

  • Determine the Level of pParent.
  • Set pLevelIndex to one less than the pParent pLevel.  You are looking for the children of pParent so the level of any children would be less than pParent.  So, start at the top and work your way down.  If you were to start at the bottom, you could potentially break other hierarchies as it would be difficult to know at what level the parent/child relationship starts.
  • Cycle through the elements in pDimension.
    • Does the level of the element equal the current index level?
    • Is pParent an ancestor of the element?
    • Does the element have more than one parent?
TM1 Alternate Hierarchies
Multiple Parents:

If the [pDimEle] element has more than one parent, the assumption is it belongs to more than one hierarchy.

  • Set the pLevelIndex2 to one higher than the current pLevelIndex.  You need to determine to which parent of the hierarchy the element with multiple parents belongs.
  • Cycle through the elements in pDimension.
    • Does the level of the element equal the current pLevelIndex2?
    • Is pParent an ancestor of the element?
  • Delete the element with multiple parents from the current parent.  (DeleteDirect is used so that, if needed, the dimension can be rebuilt as part of the TI Process.)
Tm1 Alternate Hierarchies

Because you only need the level above the current pLevelIndex, there is no need to increment pLevelIndex2.

  • pLevelIndex is decreased by one each time.  You need to work your way DOWN the levels of pParent.
Tm1 Alternate Hierarchies
Removing  Elements from TM1 Alternate Hierarchies:

Once all parent/child relationships that exist in other hierarchies are removed, the remaining relationships/elements can be removed from your hierarchy.

  • Reset pLevel to the Level of pParent.
  • Set the LevelIndex to 1.  You are working from the bottom UP.
  • Cycle through the elements in pDimension.
    • Does the Level of the element equal the current LevelIndex?
    • Is pParent an ancestor of the element or does the element equal pParent?
TM1 Alternate Hierarchy
  • Determine the number of Children of the parent element.
  • Determine the Child based on the pChildCount.
  • Delete the Child from the parent element.  (DeleteDirect is used so that the child is removed immediately.)
  • pChildCount is decreased by one each time as the number of children decreases each time one is removed.
TM1 Alternate Hierarchies
  • If the element type is "C" and the number of children is zero and pYN parameter is set to "Y", delete the parent element.
TM1 Alternate Hierarchies
Tm1 Alternate Hierarchies

Hopefully this technique will save you time and endear you to your end users. At Lodestar Solutions our consultants and coaches teach you time saving tips.   Hence, we empower you to deliver

Faster, Cheaper Analytics.

Finally, if you like our tips, consider joining our Lodestar Club to get access to our members only library of education videos. If you would like additional help on your TM1 project please contact us at Services@lodestarsolutions.com.

Leave a Comment

X
Send this to a friend