SAP HANA: Using Hierarchies

Hello SCN,

We shall discuss about how to define “Hierarchies” in SAP HANA.

In SAP HANA, we have a choice of creating 2 types of hierarchies:

1. Level Hierarchy
2. Parent Child Hierarchy

Level Hierarchy:

     Each level represents a position in the hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels.Each level above the base (or most detailed) level contains aggregate values for the levels below it. The members at different levels have a one-to-many parent-child relation. For example, Q1-05 and Q2-05 are the children of 2005, thus 2005 is the parent of Q1-05 and Q2-05.

Hierarchies and levels have a many-to-many relationship. A hierarchy typically contains several levels, and a single level can be included in more than one hierarchy.

In our example, let us take “Level Hierarchy”.

Parent Child Hierarchy:

     A parent-child hierarchy is a hierarchy in a dimension that is based on two table columns. Together, these columns define the hierarchical relationships among the members of the dimension. The first column, called the member key column, identifies each dimension member. The other column, called the parent column, identifies the parent of each dimension member. The parent attribute determines the name of each level in the parent-child hierarchy, and determines whether data for parent members should be displayed.

     Now let us discuss the same using a Simple scenario, I will create a Level based hierarchy.

We all know the “E-Fashion” Dataset which is available @Cloudshare.  The following is an analytic view which is created on that dataset.

So when we try to “report” on this view using Excel, we will find the following,

The available “Field/Characteristics” option is as shown as below:

Here you can see all “Key Attributes”, I can select only those 3 fields namely “Article Id” and “Week ID”.Let us create an “Example” Hierarchy (Based on Level as shown below).

Here you can see, I have created a level based hierarchy based on the following “Top down Approach”. As you know I cannot see those fields in my reporting @excel as I can see only “Key Attributes”. Now you can see now I can drill down to “State-wise”, “City-wise”.

Note: If I don’t intend to use “Hierarchies” ( though it is proffered as it gives a nice presentation to the end-user, you can still display the Non-Key attributes by changing the following property to “TRUE” as shown below:

Note: As I have a hierarchy defined for “AT_OUTLET”, it is showing my hierarchy option there “Example”.

I should keep “Hierarchy Active = TRUE”.

Then this field will be displayed along with “Key” attributes on excel.You can see now “QTR” is also enabled in the Excel reporting.

But here you can see the discrepancy as “Year-QTR” has parent-child relationship and it was not properly addressed here. This can be addressed by creating a “Parent-Child Hierarchy”.

Hope you understood the benefits of using “Level Based Hierarchy”. In my next document, I will focus on “Parent-child Hierarchy”.

Thanks for reading this document 🙂