CreateHierarchyByAttribute Function in TM1: Syntax and Use

We once did a project where we needed to create an excel report showing attributes from the same dimension as the rows and columns in a report. Traditionally in TM1, we would have needed to create a reporting cube with these attributes recreated as dimensions and then place those dimensions into both rows and columns. However, using the new hierarchy capabilities of TM1, we were able to do this by creating virtual dimensions from the existing attributes and then use those virtual dimensions as the rows and columns in Excel, even though they came from the same physical dimension! Magic! We use the CreateHierarchyByAttribute function to do this.

We use TurboIntegrator (TI) process and the example noted below illustrate how we complete this.

There are two ways of creating alternative hierarchy as virtual dimension:

1. Via Planning Analytics Workspace (PAW);
2. Via TurboIntergrator processes.

Note that CreateHierarchyByAttribute is TM1 TurboIntegrator function and valid only in TurboIntegrator processes.

Syntax of CreateHierarchyByAttribute

The syntax is:

CreateHierarchyByAttribute ( Dimension, Attribute , BlankSubstitute , RootName ); where

• Dimension – is the dimension where it contains the attribute and is also the dimension where the hierarchy will be created
• Attribute – the attribute to create the hierarchy from
• BlankSubstitute – where it collects the dimension leaves that without an attributes value – OPTIONAL
• RootName – it overrides the root element name which by default is named after the attribute – OPTIONAL

Example of CreateHierarchyByAttribute

Here is an example to create a hierarchy based on an attribute. Firstly, it gauges the existence of the hierarchy and destroys it if it exists. Then it creates the new hierarchy based on the attribute of the element.

cDimName = 'Customer';

sAttrName = 'Customer Group';

sBlankSubstitute = 'No Cust Group';

sRootName = 'All Customer Groups';

sHierName = 'Customer Group';

IF ( HierarchyExists ( cDimName , sHierName ) = 1);

HierarchyDestroy ( cDimName , sHierName );

ENDIF;

CreateHierarchyByAttribute( cDimName, sAttrName , sBlankSubstitute , sRootName );

What this example is doing is testing if the hierarchy ‘Customer Group’ exists in the dimension ‘Customer’ and if so, then delete it. We then create the hierarchy, defining those elements that have no entry in the attribute to be ‘No Cust Group’ and the parent of the hierarchy to be ‘All Customer Groups’.