The Parent Child hierarchy in HANA

There are two types of hierarchies supported in HANA:

• Parent-Child hierarchy
• Level hierarchy

In this document, I would like to take the readers through the Parent child hierarchy: to create a very simple Parent Child hierarchy and to use it in a reporting tool that supports multidimensional reporting, like A-Office.

Parent Child hierarchy:

A parent child hierarchy could be described as the more general case where a node’s semantics is not directly determined by its level (i.e. distance from the root). Parent-child hierarchies are suitable to model tree structures having unbalanced branches of different length and whose depth are not known at design time. Technically, parent-child hierarchies are usually implemented by a recursive self-reference within a table.
Example of such hierarchy would be EmployeeId and ManagerId.

I will create a simple Parent child hierarchy. For this, I would use data from the .csv file, which contains employee data.

As a first step I would import this data into SAP HANA using Import->Data from Local File and select ‘Next’:

Here, I would select the target system and select ‘Next’:

In the next screen I would mention the location, where I would like to import the data in the HANA system and click 'Next':

Here I would maintain the proper mappings and click 'Next':

As a next step, I would click 'Next' to preview the data and click on Finish:

I would now create a calculation view on this data:

For this, on my package, I would right click and select ‘new’ and then ‘calculation view’. Here I would assign name to my calculation view and my calculation view would be of type, data category ‘cube’:    

Here, I would add the fields to output like:

I would now 'Save' and 'Activate' and check the output.

I would now add Parent Child hierarchy to this view:

For this, I would move to the hierarchies tab and click on ‘+’ sign and enter the details in the next screen:

Here the ‘Root Node’ is root node of the overall hierarchy representation. Stepparent is to specify where to place the orphan parent-child pair.

We assign ‘0’ for Stepparent because we wish to map the orphans to the step parent which would be same as root node ‘0’.

In our example, ‘0’ is the root node. I can enter that by clicking on ‘Open Root Node Dialog’ in the Root Node Column:

Here, we have two types: ‘Fixed’ and ‘Input Parameter’, where we could specify the value for the root node. Since in our example the root node is ‘0’ we choose ‘Fixed’ and ‘0’ respectively in ‘Type’ and ‘Threshold’.

Now, I would move to the ‘Advanced’ tab, where I would specify the values for other properties of the hierarchy like:

‘Aggregate All Nodes’ is set to true if we wish to aggregate the values of the nodes, while aggregating the data, else the value can be ‘False’.

‘Default Member’ is used to specify the default member. Each hierarchy has a default member. Normally, the system automatically chooses a default member based on the below:

• For a hierarchy with a visible root node (the All member), then the All member is chosen
• If there is no visible root member, then the first apex node at level 0 is chosen

A ‘default member’ is usually useful in cases when a member from a dimension is not specified while querying for a hierarchy, in such a case the default member is automatically taken.

In my example, I would leave it blank.

‘Orphan Nodes’ is used to specify how to handle orphan nodes using the dropdown. The options available are:

• Root Nodes: with this option the orphan nodes are taken as Root Nodes
• Error: Error is thrown on encountering orphan nodes
• Ignore: will Ignore the orphan nodes
• Stepparent: With this option, the orphan nodes are added under the stepparent node. The stepparent node must exist in the hierarchy and must be at the root level. Enter the node ID for the stepparent node in the ‘stepparent’ column under the ‘Node’ tab.

Here in this example, I will assign them to ‘Step parent’ ‘.

‘Stepparent’ takes in the same value as specified in the ‘stepparent’ column under the ‘Node’ tab. Here, we find it as ‘0’, the value being taken from the ‘Node’ tab.

‘Root Node Visibility’ is used to define the root node's visibility in the hierarchy result for parent-child hierarchies.  This is an optional parameter and the options available are:

• Add Root Node: if we wish to create a root node. This would be in case if the hierarchy does not have any root node.
• Do Not Add Root Node: the root node is hidden in this option.
• Add Root Node If defined: is the default option. In this case, the physical root node is visible if explicitly defined in the hierarchy definition and not null, otherwise hidden.

Here I would go with the 'default' value.

‘Multiple Parent’ is selected if the hierarchy needs to support multiple parents for its members.

We can sort siblings in a parent child hierarchy based on a column value. In the Order By Column, we can select a column that we would want to use to sort the hierarchy. We can choose a Sort Direction for the column we select.

We can now save and activate the view to see if there are no errors. We could check the data of the view using data preview.

Note: We cannot validate the hierarchy definition, in order to do that, we would have to execute the below SQL:

select * from <location of the hierarchy view in the _SYS_BIC folder>

This would point out errors, if any, in the hierarchy defined.

Now I would consume this view in A-Office. For this, I would connect to HANA view from A-Office and use the created calculation view for reporting.

For this, I would use http connectivity to establish connection between HANA system and A-Office, using the Select Data Source, where we right click and select the option:

and enter the HANA System credentials:

Once the connection is established, I would be able to select the hierarchy view that I just created and click 'OK':

Under Analysis, I would be able to see all measures and dimensions of the view, and I would be able to see the hierarchy under employee_id as:

I drag this to our rows display and I would be able to see my hierarchy in the display as:

With this I would like to conclude this document providing a basic overview on Parent Child hierarchy.

Source: scn.sap.com