SAP HANA: Using Hierarchies
We shall discuss about how to define “Hierarchies” in SAP HANA.
In SAP HANA, we have a choice of creating 2 types of hierarchies:
- Level Hierarchy
- Parent Child 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 🙂
Thanks for the post.
I have created the level heirarchy. But I am facing the issue in seeing the data in Excel pivot .I am able to connect using the SAP HANA - MDX Provider from data connection wizard in excel.
After selecting the cube in excel , I am getting an initialization error.Could you pls help me on the properties of the connection and excel pivot properties to be set.
I am using windows authentication to access the datasource from Excel.
Thanks in Advance.
I created level hierarchy with customer and Customer Master and created Analytical View and connected to Excel pivot @ the end iam getting the
"excel was unable to get necessary info about this cube the cube might have reorganised
contact the olap administrator if necessary create new datasource."
Pls help us.
Make sure that all your Parent entries are referred in the child column atleast once. Also check if the top most node is having a null parent or not. The requirement for a parent child hierarchy is to have NULL as parent to the top most node of the hierarchy and all the parent should be present in the child column as well.
We had the exact same issue and it got resolved by applying the above method.
Dear krishna.. i really impress for ur blog..so many days onwards iam struggling for SAP- SQL script which plays major role in SAP-HANA..will you please send some imp stuff regarding that one and aslo share some screen shots..
I am facing an issue and would be glad if you could help me out. Whenever I try to implement a Parent-Child hierarchy, and access the analytic view from Excel, I get the following error:
"Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server.
Contact the OLAP cube administrator and, if necessary, set up a new data source to connect to the cube."
The error does not occur when I am implementing a Level Hierarchy.
The Attribute View contains the following fields:
Brand (Key attribute)
The underlying table has the following data:
Product_Id (Key field)
The join with the fact data in the analytic view is on Product_Id field.
Any ideas what could be wrong?
This problem occurs when we do not have proper ODBC connectivity defined .Please create specific ODBC connection with HANA DB then use it to open Hierarchy in Excel .It will definetly work.
Thanks for the wonderful blog, Were you able to implement the parent child hierarchies and get output into excel successfully?
Please share your efforts.
Still struggling with the Parent Child Hierarchies, Not able to view in Excel, I am using Excel 2013 and HANA DB rev 52 with Studio rev 52.
Thanks for the post
I am using HANA STUDIO 1.0.56
I have Products. Each product is linked to a ProductGroup.
ProductGroups are ParentChild relationship.
So each ProductGroup has a ParentCode, which is another ProductGroup itself. They all reside in the same table.
I could create an Attribute view , with PRODUCTS linked to PRODUCT_GROUPS.
Here is where Parent Child Hierarchy is defined.
I created an analytical view with Data Foundation having tables Orders, OrderLines.
Logical JOIN has the above AttributeView with Hierarchy. OrderLines are linked to Products.
I can get everything saved, activated. I can also see the RawData in DataPreview.
All ok so far.
The error I get when extracting into EXCEL using MDX is
sql processing error: Hierarchy [ATRBV_PRODUCTS_HIER].[HIER_PROD_GRP_PC] has more than one HIERARCHY_KEY_ATTRUBTE row.
was wondering what this error could be.
I will appreciate your guidance on this.
I have written a document on how to consume BW Parent Child hierarchies in SAP HANA and ultimately in front end tool. This document deals with how to consume BW Parent Child hierarchies but you can also follow the document to understand why you get the error while consuming the parent child hierarchy in front end tool and its solution. Here is the link to it:
Great Document for Beginners!!! Keep up the good work!!!
Thanks for your comments on the document.
Sorry for not being able to respond as i no longer can use that account and i cannot see the notifications.
I will try my best to answer your questions.Thanks once again.
Can we have time dependant hierarchies in a pure SAP HANA scenario (without BW) ?
we can do it model it manually...
Any documentation on it? any feedback ?
Can this done in a calculation view ? If yes ,Is it possible to get all the parents of the passed input employee. For ex: I want to fetch all the mangers of the employee pased as a input parameter.
Can someone tell me how to implement the hierarcies we have in BW regarding 0WORKCENTER? I have made a calculated view wuhere I link the fact table with the hierarchy table of BW. It works fine when a
work center is only present in a hierarchy because the left join only finds a record in the hierarchy table but when a same workcenter bolongs to more than one hierarchy then the left join duplicates data.