Hierarchy is nothing but an arrangement of nodes in a specific order to describe a business function. SAP HANA supports two types of Hierarchies, namely, Level Hierarchy and Parent-Child Hierarchy. This blog explores more in detail about Dynamic Hierarchy, its purpose and how the concept is supported by SAP HANA.

Though most hierarchies are rigid, there are some that can be rearranged/reorganized to address a different business need. Let’s look at an example.

The above-mentioned hierarchy depicts a typical Organization Hierarchy across: Organization -> Department -> Projects -> Time Line -> Teams -> Resources.

We can also reorder the same hierarchy structure to get a different insight. Here, we have reordered the structure to a Resource specific format. This will display the different Projects and Teams where each Resource has worked across various months. Typically, we would create two different hierarchies for the scenarios explained.

A single hierarchy that could allow you to decide the order and level of nodes dynamically from a report, is known as Dynamic Hierarchy. This report was developed using Lumira Designer and SAP HANA.

Steps to create Dynamic Hierarchy in SAP HANA

Dynamic Hierarchy is possible only by leveraging Level Hierarchy in SAP HANA. It is not possible with Parent-Child Hierarchy.

Create a level-based hierarchy model with 5 levels of nodes. Let’s see how we can convert this static hierarchy into a dynamic one.

There are five columns in our Static Level-based Hierarchy model: Department, Project, Period, Role, Person. Now, create five calculated columns and five input parameters – each one corresponding to a level (column) of the hierarchy (Note: If you have ‘N’ number of levels, then you must create ‘N’ Calculated columns and ‘N’ Input Parameters).

From the report, the user can pass a different set of values to each input parameter (each value corresponds to each column). Depending upon the input parameter values, calculated columns will fetch the respective levels (Columns) from the table/view.

Add these calculated columns to the Level Hierarchy.

And finally, your Dynamic Hierarchy in SAP HANA is ready!

To know more about SAP HANA offerings from Visual BI Solutions, click here