Skip to Content

Advance Hierarchy Modeling with SAP HANA.

 

Scenario:-

While working on one of the reporting requirements, where i had to calculate various KPIs based on hierarchy nodes. Huge volume of transaction data was coming from various flat files sources. Master data files were also being consumed through flat file. The masterdata file was having parent and child relationship.

Solution :-

My first idea was to use SAP BW for maintaining hierarchies using flat files but because of huge volume of transaction data and majority of  modeling  in native HANA, I was little bit resistant to use SAP BW/Bex design.With SPS 10 onward, this new feature available in SAP HANA, will help us a lot while working with Hierarchies in SAP HANA. With this new feature, it is now possible to create hierarchy node variable as well as to us hierarchy in script based SQL view. This feature can be achieved in both Level as well as Parent Child hierarchy.

In this blog, I would like to showcase this new feature using Parent Child hierarchy in SAP HANA. In this example we will be using product sales data and product hierarchy. Below steps will guide me to achieve my end results.

Step 1

Using a flat file, I have loaded the product sales data in a table in SAP HANA.

Step 2

Using flat file load, we will insert records in Product Hierarchy table.

Step 3
Create Calculation view on these tables. First we will create dimension calculation view on Prod_Hier  table and create parent child hierarchy in this view.

Step 4

Next we will create a calculation view with star schema using the fact table and dimension view (created in step 3).

The view will looks like :-

In the properties section of the view, please remember to check in “ENABLE SQL ACCESS” box, so that we can access this view using SQL

Finally after activating the view, the data will look like

Step 5

Once this is complete, go to semantics of the final view (created in step 4), and create a hierarchy variable as shown below:-

Activate the view once this is done.

Step 6

Click on display data as :-

Here as I have selected ‘DETA’ product hierarchy node, we can get the total units sold for DETA product.

We can also see the generated SQL for this query from studio.

Generated SQL:

SELECT TOP 2000 DISTINCT “PARENT”, “CHILD”, SUM(“UNITS_SOLD”) AS “UNITS_SOLD_SUM”

FROM  PROD_SALES” WHERE ((“HIER_PRODNode” IN (‘DETA’) ))

GROUP BY “PARENT”, “CHILD”

ORDER BY “PARENT” ASC, “CHILD” ASC

To report this post you need to login first.

9 Comments

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

  1. Nayan Joshi

    Hi Mritunjay,

     

    Nice blog!

    I have one question however. How to i get the total at the highest level. For example in your case you get the values against DETA. But I did not see the total value for retail in any of the screenshots. If I try to replicate similar example in my system I do not get any values at the higher level. Ideally in your case you should also get total value for retail in your final table.

    It will be big help if you could answer this.

    (3) 
    1. Kenneth Murray

      It looks like my “Generated SQL” does not put in the HIER_PRODNode field .  Instead it is putting in the Parent Node Field. Why would this be?

      I think if I can get the Generated SQL to correctly put in the HIER_PRODNode Field all our problems would be solved including yours.

      @Mritunjay Could you assist us in this matter?

      (0) 
  2. Cote Adams

    Great explanation, thanks!

    Is this functionality compatible with Analysis for Office 2.4? We have a catch 22 scenario where this setting works for Studio and any other ODBC Connected technologies, but does not work with AO 2.4/MDS queries.

    Alternatively, we have another approach that works for AO 2.4, but that doesn’t work for SQL/ODBC/Studio.

    Thanks.

    (0) 
  3. raunak sharma

    HI ,

    I have  created a Hierarchy similar to mentioned above. When i give selection for any intermediate node it works fine in HANA studio; however if i select any intermediate node in AO, instead of showing data for only that Node (and below child nodes) still i see aggregated data of Root node. Somehow seems like Selection Hierarchy node is not restricting data from Calculation view.

    Any insight would be helpful.

    Regards,

    Raunak

     

    (0) 
  4. raunak sharma

    I am selecting some intermediate Hierarchy node(00000208)

    In AO, though I am selecting 00000208, still aggregated values of  ROOT node is shown.

    However, in HANA I see correct values only for (00000208)

     

    (0) 
  5. Kenneth Murray

    Hi,

    Instead of using a custom Hierarchy with simple PARENT/CHILD Layout.

    Is it possible to use the BW Hierarchy Calculation Views <INFOOBJECT>_HIERARCHY generated from BW InfoObjects.  Every time I have tried I’ve been unsuccessful due to various errors.

    Thanks for any help or input!

    (0) 

Leave a Reply