In this blog we will look at the possible options for setting up a hierarchy in HANA. We will also see which type of hierarchy to choose depending on the format of the source data as well as the desired visualization.
What is a hierarchy?
A hierarchy in computer science is a data structure in which information is arranged in the form of a tree. This allows information to be shown and given context (relatedness).
Example: 
- A node is an entity of the hierarchical tree
- A root node is a node that has no parent.
- A parent is a higher level node than another node (Continent 1 is the parent of Country 1 and Country 2)
- A child is a node under a parent (Country 1 and Country 2 are the children of Continent 1)
- A leaf is a node with no children (Country 1, City 1...)
Types of hierarchies
With HANA there are two options for setting up a hierarchy. On the one hand using the "hierarchy" module in your Hana IDE (no code required) and on the other using the SQL function "HIERARCHY" (SQL code to be written). The choice of the type of implementation will depend on your source data, below, the types of hierarchies taken into account by each method.
Hana hierarchy module:
- Parent-Child Structure
- Structure by level
SQL "HIERARCHY" function:
- Parent-Child Structure
In the rest of this Blog we will first look at the differences between the two types of hierarchies, then at how to create a hierarchy via the Hana module and via the SQL function before looking at the possible uses of each.
Parent-Child Hierarchy
If a data table has values in one column and parents corresponding to each of these values in another column, then a parent-child hierarchy can be created.
Example: 
Each of the above nodes has its parent node associated with it, except for the World value which is in this case the Root node.
NB: For this hierarchy model all nodes must be of the same type. Here the parent-child link will be on the IDs, the values of these columns must be in the same format (INTEGER in our case).
Hierarchy by level
If instead you have a separate column for each level of the hierarchy (e.g. Country, City, Continent), then you will need to model this data via the level hierarchy.
Example: 
NB: A hierarchy by level must necessarily be balanced, i.e. each branch of the tree structure must be of equal length (3 here). In our case we could not have a country without a city or a continent without a country. This is not the case with the parent-child hierarchy.
Summary
Creating a hierarchy in Hana
Once the type of hierarchy has been determined, it must be implemented within the IDE.
Parent-Child hierarchy via the Hana module
- Create a "Calculation View" showing all the fields needed for the hierarchy. Check that they are all available in the semantic layer.
- Click on the "Hierarchy" tab in the details section, press the +.
- Choose Parent-Child as the hierarchy type, name it and define the child and parent fields. It is also possible to enter the value of the root node, "World" here.
NB: If a child can have more than one parent then the "Multiple Parent" box in the "Advanced" tab must be checked.
Hierarchy by level via the Hana Module
- Create a "Calculation View" showing all the fields needed for the hierarchy. Check that they are all available in the semantic layer.
- Click on the "Hierarchy" tab in the details section, press the +.
- Choose Level as the hierarchy type, name it and define the child and parent fields.
Parent-child or level hierarchies can be used in Web Intelligence. Both types are identical in use, they are presented in the form of a field whose tree structure can be unfolded.
Example:
Source : Hana Handbook BW/4HANA Modeling, blogs.sap.com (Hierarchy in webi)
Parent-Child hierarchy via the SQL function "Hierarchy
There is an alternative way of using hierarchical data. This consists of enriching each record with information that gives context to the line in question. In this case, we will not be able to have an unfoldable display on Web intelligence in the same way as seen in the previous paragraph.
Let's illustrate by example, with the following source data:
The context fields that can be added are for example :
- The level
- Rank
- Tree Size
The level indicates the height of each node in the hierarchy (a country is at level 3).
The Rank is a counter, it can be used to sort in order to find the hierarchical order to see the parent-child relationship.
The "Tree size" field is used to find out the number of children of the record (NB: the node in question counts as 1, "Tree Size" ≠ 0).
Our example table could thus be enriched as follows:
To know the exhaustiveness of the fields that can be added in this way in a standard way, I refer you at the bottom of the page to the editor's guide (Cf "Source").
The data enrichment described involves implementing a scripted view in SQL by invoking the "Hierarchy" function.
Setting up the function
Once the table is available, you must create the scripted view (choose "SQL Script" instead of "Graphical" when creating a new view).
As in the HANA module, the parent and child fields must be defined and renamed respectively PAREND_ID and NODE_ID. You must also call the variables to be created by the function (Level, Rank...) and add the columns that are defined in the script at the output of the "Script View" block.
Below, only the level is added.
Source: SAP Help
Conclusion
There are several options for creating a hierarchy in Hana. You have to choose the most suitable one according to the use you want to make of it, as well as according to the structure of the available data.
If you are looking for an interactive report with the possibility of unfolding the tree structures, then you need to use the Hana module. In this case, the focus is on the visualisation of links and user interaction.
If instead you want to enrich your data table with columns highlighting parent-child relationships, then you need to use the SQL function.
Please feel free to consult the other Blogs written by our consultants to keep up to date with the latest SAP technologies as well as the various projects carried out by our teams.
Alexandre Chevé
Latest articles by Alexandre Chevé (view all)
- Tune your data integration in HANA with Stored Procedure - 25 June 2021
- Hierarchy in HANA - 12 January 2021
- Produce the FEC (accounting entries file) with a modern BI solution - 27 October 2020