Skip to Content
Author's profile photo Mrityunjay Pandey

Advance Hierarchy Modeling with SAP HANA

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

Assigned tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Nice blog - Could you upload these sample data sets to a file share by any chance?

      Regards,

      Edward Stark

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Kenneth Murray
      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?

      Author's profile photo Eddie Ng
      Eddie Ng

      Hi Kenneth, have you resolve your issue with regards to the HIER_PRODNode in the where statement? i am getting the same issue where it is using the PARENTID in the where statement

      cheers

      Eddie

      Author's profile photo Laura Horosko
      Laura Horosko

      Did you figure out how to fix this issue?  We are having the same one.

      Author's profile photo Kenneth Murray
      Kenneth Murray

      Hi, Been a long while since this project, but no there was no solution along that path at the time. I'm sure I ended up using a copy of the BW InfoObject Hierarchy and mangling it somehow.  Sorry, couldn't be of more help...

      Author's profile photo Former Member
      Former Member

      Nice article.

      Author's profile photo Cote Adams
      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.

      Author's profile photo Former Member
      Former Member

      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

       

      Author's profile photo Mrityunjay Pandey
      Mrityunjay Pandey
      Blog Post Author

      Can you share some screenshots ?

      Author's profile photo Former Member
      Former Member

      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)

       

      Author's profile photo Alex Slagter
      Alex Slagter

       

      Raunak,

      How did you get the GL_HIERAR variable to show up with the input to select (like F4 help)?  Are you using Analysis for office?  What version are you using?  Whenever I try to assign a variable to the hierarchy, it doesn't seem to work with Analysis for office.

      Thanks,

      Alex

      Author's profile photo Former Member
      Former Member

      You probably need to assign the hierarchy variable within the calc view

      Author's profile photo Kenneth Murray
      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!

      Author's profile photo Akshay Bhandari
      Akshay Bhandari

      Hello Experts,

      Is this method of creation of hierarchy prompts valid for Level Hierarchy created in case of Profit center hierarchy/ Cost Center Hierarchy?

      I tried creating the same but the values are not getting filtered in case of reports.

      Regards

      Akshay