Skip to Content

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.

A291.png

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

A292.png

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

A293.png

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).

A294.png

A295.png

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”.

A296.png

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”.

A297.png

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

A298.png

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 🙂

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. Janakiraman V

    Hi Krishna,

    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.

    Jana

    (0) 
    1. Phani Rayasam

      Hi Krishna,

      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.

      (0) 
      1. Piyush Jha

        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.

        (0) 
  2. Saketh Ram

    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..

    Thank You,

    Reagrds,

    Saketh

    (0) 
  3. Sudarshan Srinivasan

    Hello,

    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.

    Further information:

    The Attribute View contains the following fields:

    Product_Id

    Brand (Key attribute)

    BrandP

    1

    PotChips

    Green

    2

    Freshnax

    Green

    3

    Dipstix

    Red

    4

    Thirsture

    Green

    5

    Premzie

    Blue

    6

    Golzen

    Green

    7

    Kurrfew

    Green

    The underlying table has the following data:

    Product_Id  (Key field)

    Brand

    BrandP

    1

    PotChips

    Green

    2

    Freshnax

    Green

    3

    Dipstix

    Red

    4

    Thirsture

    Green

    5

    Premzie

    Blue

    6

    Golzen

    Green

    7

    Kurrfew

    Green

    The join with the fact data in the analytic view is on Product_Id field.

    Any ideas what could be wrong?

    Cheers,

    Sudarshan

    (0) 
    1. Phani Rayasam

      Hi Sudarshan,

      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,

      Phani

      (0) 
  4. Shreepad Patil

    Hi Krishna,

    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.

    –Shreepad

    (0) 
  5. Indu Shekhar

    Hi Krishna!

    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.

    thanks,

    -Indu

    (0) 
  6. Piyush Jha

    Hi All,

    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:

    http://scn.sap.com/docs/DOC-44971

    Regards,

    Piyush

    (0) 
  7. Krishna Tangudu

    Hi All,

    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.

    Regards,

    Krishna Tangudu

    (0) 
    1. Jacques MARCHAND

      Hi All,

      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 ?

      (0) 
  8. Vignesh Jeyabalan

    Hi All

    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.

    Regards

    Vignesh J

    (0) 

Leave a Reply