In this article you will learn how to build a new Hierarchy based on attribute values.

We will be using Cubewise’s “Arc” IDE (Integrated Development Environment), but the concepts can be applied with any Hierarchy-aware TM1 editor e.g. the TI editor in PAW.

In this article you will learn how to build a new Hierarchy based on attribute values by building a new process with Arc.

We will focus on the Employee dimension.

This dimension has currently only one Hierarchy, Employee. All employees have a Department Code attribute:

Our objective is to create a new Hierarchy in the Employee dimension called Actual 2018 Department, which contains all employees with their current department value.

We’ll start by creating a new TI process. To create new process in Arc, just click on the New Process button on the top right:

Then type the new process name,in this example we’ll name it Dim.Employee.BuildHierarchy.Department

Click Create, and Arc will present a new tab with the process name ready for editing. Arc follows the “traditional” TI editor conventions in Architect, so in the Data Source tab, select:

• Type: TM1DimensionSubset

• Dimension: Employee

• Subset: Default

Click Preview and then the Create Variables buttons. You’ll notice that Arc already prefix the variable with a “v” (variable prefixes can be configured in Arc’s settings.yml):

Let’s continue editing the TI process by defining the constants in the Prolog tab, in order to build the Employee Hierarchy we want to create:

• cDimSrc: our dimension source which is also our target dimension.

• cSubsetSrc: The subset source that we are going to loop through.

• cAttr: The attribute we need to build the consolidation.

• cHierarchy: Hierarchy name.

• cTotalHierarchy: The top consolidation of our new Hierarchy.

To work with Hierarchies in Turbo Integrator processes (TIs), IBM Planning Analytics introduced a lot of new functions. Thankfully all new functions to work with Hierarchies are very similar as the ones to work with dimensions. For Hierarchies, you will almost find a one to one match to dimensions operations such as HierarchyExists instead of DimensionExists (to check if a Hierarchy exists).

Cubewise Arc facilitates your learning TI’s new Hierarchy functions with built-in code snippets. To see the available code snippets, hit CTRL + SPACE and type “exists”.

Arc will show you all snippets that contain the search string – we’ll use the “HierarchyExists” snippet.

Before we create a new Hierarchy, we should first verify if it already exists. If it doesn’t we can simply go ahead and create it. If it does, we must delete all of its elements so we can recreate it.

Next, we insert the a new “C” element to represent the apex, or “total”, of the Hierarchy:

We want our TI to loop through all employees (all leaf elements in the Employee dimension). To do that we need first to create a subset that contains only the leaf elements of the dimension and then define this subset as data source of this TI.

We’ll use the open-source Bedrock library to do this, specifically the Bedrock.Dim.Sub.Create.Leaf process, as follow:

When using Arc, you will develop much faster if you are leveraging the snippets:

Now let’s continue to the Metadata tab. In this tab, we are going to create all of the parent-child relationships for our new Hierarchy, as follows:

• Total Department

  • Department

    • Employee

We do this by inserting each new element using HierarchyElementInsert, then making them components of the parent-child path using HierarchyElementComponentAdd. Again, note these new Hierarchy commands, and the method we are using to build a roll-up, are nearly identical to the legacy “dimension” counterparts:

In the process Epilog, we just need to delete the temporary subset, cSubsetSrc we created during the Prolog:

Save the process and then execute it.

Open the Employee dimension, if it was already open, refresh your web browser to get the last changes.

In the Hierarchy dropdown, you should be able to see the new Hierarch we just created:

We used the Department Code to create the consolidation, which is not very meaningful, so let’s now create a descriptive alias on this Hierarchy.

We need first to insert an alias into the Hierarchy. We’ll do this in the Prolog tab with the following lines of code:

#Create Alias for HierarchycAlias = 'Employee and Department Name';AttrInsert(cDimSrc | ':' | cHierarchy, '', cAlias, 'A');

To avoid duplicate alias when creating a new attribute on a Hierarchy only, you could use the new function ElementAttrInsert instead of AttrInsert so the syntax will look like this:

• ElementAttrInsert(cDimSrc, cHierarchy, ”, cAlias, ‘A’);

The ElementAttrInsert function will avoid duplicate alias on one consolidation which appears in two Hierarchies of the same dimension.

IIn the Data tab, we will now grab the alias for department from the Product Category attribute of the Department dimension and for the employee we use the attribute Full Name.

To add an attribute value to a consolidation which exists only in a specific Hierarchy, you need to “qualify” that Hierarchy in the ATTRPUTS function as follows:

• AttrPutS(vDepartmentAlias, cDimSrc | ‘:’ | cHierarchy, vDepartment, cAlias)

Instead of

• AttrPutS(vDepartmentAlias, cDimSrc, vDepartment, cAlias)

If the Hierarchy name is not specified in the ATTRPUTS function, the target element vDepartment will be the one in the default Hierarchy (Department).

To send the value to the vDepartment in our new Hierarchy (cHierarchy), we use cDimSrc | ‘:’ | cHierarchy instead of just cDimSrc.

It can be noted that instead of using AttrPutS, we could have used the new TI function ElementAttrPutS:

• ElementAttrPutS(vDepartmentAlias, cDimSrc, cHierarchy, vDepartment, cAlias)

Save and Execute the process.

Open the Employee dimension, if you don’t see the alias just refresh your browser: