Planning Analytics Workspace (PAW) comes with the feature to create multiple hierarchies in one dimension. These hierarchies are generally created by the attributes of the dimension. Once a hierarchy is created, it is detached from the attribute values. Therefore, a developer should create a TI (TurboIntegrator) process to maintain the hierarchies which are built based on attribute values. In this article, we will explore both the TI functions, which can be useful for maintaining hierarchies, and a sample code to update a hierarchy.

Some TI functions which are commonly used to maintain a hierarchy are included in this article. Each function has the description, syntax, arguments (parameters), and sample code. We will use some of these functions in our sample code to demonstrate a simple way to maintain a hierarchy in PAW.

  1. HIERARCHYCREATE is the function for creating a new hierarchy in an existing dimension. Please note that the default or main hierarchy of the dimension always has the same name as the dimension name. Therefore, the new hierarchy needs a different name. Please note that a new hierarchy is empty. We should add elements to the newly created hierarchy.

Syntax

HIERARCHYCREATE(DimName, HierName

Sample Code

HIERARCHYCREATE(‘Clothing Items’, ‘Colors’) ;

  1. HIERARCHYDESTROY removed a hierarchy from a dimension.

Syntax

HIERARCHYDESTROY(DimName, HierName

Sample Code

HIERARCHYDESTROY(‘Clothing Items’, ‘Colors’) ;

  1. HIERARCHYEXIST checks whether a hierarchy exists in a dimension. If the hierarchy in the function parameter exists, the function returns 1. Otherwise, the function returns 0.

Syntax

HIERARCHYEXIST(DimName, HierName

Sample Code

IF( HIERARCHYEXIST (‘Clothing Items’, ‘Colors’) = 1 ) ;

# Hierarchy exists – Execute code in the IF statement

ENDIF ;

  1. CREATEHIERARCHYBYATTRIBUTE creates a new hierarchy based on the values of an attribute. The values become the parent of the elements in the main hierarchy.

Syntax

CREATEHIERARCHYBYATTRIBUTE (DimName, AttrName, [,emptyParent , rootName])

Sample Code

CREATEHIERARCHYBYATTRIBUTE( ‘Clothing Items’, ‘Colors’, ‘NA Group’, ‘All Colors’) ;

  1. HIERARCHYELEMENTINSERT adds an element to a hierarchy. However, this function does not have a parameter to set a parent of the newly added element.

Syntax

HIERARCHYELEMENTINSERT(DimName, HierName, InsertionPoint, ElName, ElType);

Sample Code

HIERARCHYELEMENTINSERT( ‘Clothing Items’, ‘Colors’, ‘’, ‘40001’, ‘N’) ;

  1. HIERARCHYELEMENTCOMPONENTADD added an element to a parent.

Syntax

HIERARCHYELEMENTCOMPONENTADD(DimName, HierName, ConsolidatedElName, ElName, ElWeight) ;

Sample Code

HIERARCHYELEMENTCOMPONENTADD ( ‘Clothing Items’, ‘Colors’, ‘Red’, ‘40001’, 1) ;

Before we discuss the sample code, we should review the dimension in our sample model. The dimension is called “Clothing Items”. It has two attributes: Slim Fit and Colors. We will only use the Colors attribute in our example. The goal is to create a new hierarchy based on the Colors attribute. If the color settings change, the new hierarchy will be updated too.

Clothing Items Dimension and Its Attributes

We have two ways to create a hierarchy. We can create a hierarchy based on an attribute by a TI process or we can manually create a hierarchy by a TI process. The manual process is more advanced, and we will not cover it in this article. It requires the functions like HIERARCHYCREATE, HIERARCHYELEMENTINSERT, and HIERARCHYELEMENTCOMPONENTADD.

The sample TI process builds/rebuilds the Colors hierarchy based on the Colors attribute every time the process is run. If the attribute values change, a user will run the sample TI process to update the Colors hierarchy. The TI process also accepts parameters like a dimension name (pDimName), a target hierarchy (pTargetHierarchy), and an attribute name (pAttributeName) to increase the flexibility of the process. It can maintain any hierarchies which are created based on an attribute. Please see the sample code below.

Sample code of a hierarchy maintenance process

Sample process parameters

The Colors hierarchy in the Clothing Items dimension

It is especially important that the hierarchies in PAW are maintained because a user can retrieve incorrect information from an outdated hierarchy. If a hierarchy is built from an attribute, it should be updated by a TI process. An administrator can also schedule the hierarchy maintenance process to run every few hours or more frequently according to the needs of users. The CREATEHIERARCHYBYATTRIBUTE function is powerful because it helps us reduce many lines of code. However, a developer has full control over a hierarchy if he chooses to build a hierarchy without using the CREATEHIERARCHYBYATTRIBUTE function.