Advance Hierarchy Modeling with SAP HANA
Advance Hierarchy Modeling with SAP HANA.
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.
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.
Using a flat file, I have loaded the product sales data in a table in SAP HANA.
Using flat file load, we will insert records in Product Hierarchy table.
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.
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
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.
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.
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
Nice blog - Could you upload these sample data sets to a file share by any chance?
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.
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?
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
Did you figure out how to fix this issue? We are having the same one.
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...
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.
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.
Can you share some screenshots ?
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)
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.
You probably need to assign the hierarchy variable within the calc view
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!
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.