Consuming BW Parent Child Hierarchy in HANA - A workaround

[Former Member]

We all know how simple it is to create a hierarchy in BW and consume it in BEX. Now with the presence of BW on HANA some of us might want to use the existing Hierarchies, maintained in BW, in HANA .

Well, there is no direct way to do it, but the following workaround will help you extract and replicate the hierarchy in HANA from BW.

So what do you need to start off with?

• An instance of BW on HANA

• Hierarchies maintained in BW

• Basic SQL and HANA Modeling background

• A front end tool to consume the hierarchy created in HANA

In this document, I will take the example of Profit Center Hierarchy.

P.S. – I have used a sample dataset for this document.

So how do we do it?

Here are the Steps to be followed:

• Create a Table: The approach, which we are going to follow here, would require a table to be persisted which would hold the data of Profit Centers and their parent profit center nodes.
• Locate the H table and prepare the SQL: The above mentioned table needs to be populated with some data. This data will be populated using a SQL with a self-join of a table. So to start off with, check if you have the H Table of Profit Center Hierarchy in the SAP HANA Catalog created for the BW instance or not. In my case the name of the Catalog is CUSTOM_TABLES and the name of the tables is HPROFIT_CTR.

The table structure is as shown below:

Here only 14 records are shown. In general you will have thousands of records in this table depending on the number of Hierarchies and number of levels it has in BW. For different hierarchies the Hierarchy ID will be different. For this example I have taken the case of only one Profit Center Hierarchy maintained in BW.

The main columns in this table, which we are going to focus on, are: NodeID, NodeName and ParentID.

The NodeID is a unique identifier for each node and profit center. The NodeName column has the name of the nodes/profit centers. The ParentID column has the detail about the Parent of that node/profit center.

We need to perform a self-join on this H table in order to get the relationship in one single row for a Profit Center. In order to do this we need to write a simple SQL which is as follows:

The output of this SQL is as shown below:

Here the Child Column has the child nodes and the Parent Column has the Parent nodes.

The most important part of this output is the ‘?’ as parent for the ProfitNode1 which is achieved by the Left Outer Join in the self-join. The ‘?’ or null value signifies that the ProfitNode1 does not have any parent and it is the top most node of the hierarchy.

P.S. - Remember, whenever you create a parent child hierarchy in HANA and try to consume it in front end then all the nodes should have one parent. The topmost node should have null as parent. If you do not have this structure in place, you will not be able to consume the hierarchy in Front End Tool and end up getting error. Also, the chain of Parent Child should not break while creating Hierarchies. This will be explained in the later part of the document.

• Create the Persisted Table:  Now, this data needs to be pushed into a persisted table which then, can be utilized in a Calculation View to create a Parent Child hierarchy. To achieve this, you can either schedule the SQL in BODS or you can use an "INTO" clause at the end of the SQL if your nodes are not going to be changed in future.

For this example, I will create a simple 2 column table named "PARENT_CHILD" in CUSTOM_TABLES catalog and load the data using INTO Clause.

We have extracted the Hierarchy Information from BW into HANA. Now this "PARENT_CHILD" table will be used for our modeling.

P.S. – If you do not wish to persist the data into a table and would like to execute the query on the fly when user runs the report, then you can create a script based calculation view using the same SQL. This calculation view will again be used inside the final calculation view.

• Modeling of Views: This is a very simple but most critical step in creating Hierarchy. The general thinking goes in the direction of creating a hierarchy in Attribute View, then consuming this in an analytic view and finally using this analytic view into the Calculation View which then will be used for reporting. But there are several problems with this process like you cannot expose a Hierarchy created in Attribute View to Calculation View through an Analytic View. I will explain the most critical issue, which this approach has: The Break in Parent Child Relationship. So what this issue is all about?

There are 2 constraints when you create a parent child hierarchy. One is that the topmost node should have null as parent which I have already explained. The 2^nd constraint is, when you are creating a parent – child hierarchy, there should not be any child without a parent. For example: If A is parent of B, B is parent of C and C is parent of D, then at no time while creating (not consuming) the hierarchy, this link should break. So you must be wondering, "When this situation will arise?" .

If you look at the HPROFIT_CTR table, you will find that it is a mix of Nodes and the leaves of the hierarchy. A node is that entry which has a child and a leaf is that entry which does not have any child. If you look at the HPROFIT_CTR table, you will find that ProfitNode1, ProfitNode2 and ProfitNode3 are the Node entries as they have at least 1 child whereas the entries from P01 to P011 are the Leaf Entries.

Generally, in the transaction table e.g. FAGLFLEXT table of ECC, the Profit Center column will always have the leaf entries and not the Node entries. So now, if you join this transaction data with an attribute view created on PARENT_CHILD, using Inner Join or Left Outer Join or Referential Join based on Profit Center, the Node Entries of the Attribute View will not come in the output of the Analytic View because they are not present in the transaction table or the data foundation of the analytic view.

So merely joining the Attribute View based on PARENT_CHILD table will not help. What you need to do is, along with the join on this table in Analytic View, perform a union of this table with the analytic view in the Calculation View. In that way, while creating the hierarchy, you will have all the nodes and leaves present in the calculation view. Once this is done, create a Parent Child Hierarchy in the Calculation View and then consume it in Front End Tool.

Let’s do some hands on exercise on this. So to start off with, create an attribute view on PARENT_CHILD table as shown below:

Activate this Attribute View.

Now, create an Analytic View using your transaction table. I have created a dummy transaction table for this exercise:

Create an Analytic View using this table and join the Attribute View, created earlier, to the data foundation using Left Outer Join on Profit Center and Child (Left Table: Data Foundation, Cardinality N:1):

Save and Activate this Analytic View.

Now we will create a calculation view on top of this Analytic View Layer and create Hierarchy there.

So let us name it as CALCULATION_SALES_DETAILS. This calculation view will have the main source as ANALYTIC_SALES_DETAILS having union with the PROFIT_CENTER Attribute View on PARENT_CHILD table:

Now, in the output node, add the attributes and measures to the final output. Also, create a new Parent Child Hierarchy using the below mentioned information:

Save and Activate the Calculation View.

This view can now be consumed in the front end tools. I will use MS Excel to show the data:

Here you can see that the Hierarchy of Profit Center is consumed as we would like it to be.

If you are thinking why we have done the union of the Attribute view then think about the broken linkage issue which I have explained earlier. The union takes care of any broken linkage as all the links are present in the attribute view.

The solution provided here, is completely based on my project experience. Hope this document helps you in understanding the Parent Child Hierarchy better.