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