Working with hierarchies will bring to your application greater flexibility. For example hierarchies could be the answer to slow-changing dimension. If employees move from one department to another, you could use hierarchies to take a snapshot of the current hierarchy.
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:
Each employee in the Employee dimension has a Department Code attribute value. We want to create a new hierarchy called Actual 2018 Department which contains all employees with their current department value. To do this we are going to create a new process.
To create new process, just click on the New Process button on the top right:
Then type the new process name:
Click Create button, you should now see a new tab with the process name. In the Data Source tab, select:
- Type: TM1DimensionSubset
- Dimension: Employee
- Subset: Default
Click Preview and then the Create Variables buttons, you can notice that Arc already prefix the variable with a “v” (this can be configured in the settings.yml):
Let’s start by defining the constants in the Prolog tab, to build a hierarchy we need to define:
- 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.
Working with processes
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).
Working with Arc will facilitate the transition by using the snippets. To open the snippets, just hit CTRL + SPACE and then start typing “exists” and then you will find the function we need:
To create a dimension or a hierarchy, we need to check first if the hierarchy exists, if it exists we delete all elements if it does not exist we create the new hierarchy:
It can be noted that instead of deleting all elements of a hierarchy using HierarchyDeleteAllElements, you should unwind the hierarchy to avoid the risk of losing data.
Then we insert the new hierarchy total:
Then we need to create the subset source to make sure we include all leaf elements, we use the Bedrock.Dim.Sub.Create.Leaf process and then we attach the subset as source of our process:
When using Arc, you will develop much faster if you are leveraging the snippets:
Now let’s move to the Metadata tab. In this tab, we are going to add elements togethers
- Total Department
First, we need to insert the new elements using HierarchyElementInsert and then we add the components to the consolidations using HierarchyElementComponentAdd:
In Epilog, we just need to delete the cSubsetSrc 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 hierarchy:
We used the department code to create the consolidation which is not very meaningful. Let’s create an alias on this hierarchy.
Populating attribute values
We need first to insert the alias in the Prolog tab with the following lines of code:
#Create Alias for Hierarchy cAlias = '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.
In the Data tab, we are grabbing 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 the hierarchy, you need to add the hierarchy in the ATTRPUTS function, you will need to use:
- AttrPutS(vDepartmentAlias, cDimSrc | ‘:’ | cHierarchy, vDepartment, cAlias)
- 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: