Skip to Content

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:

/wp-content/uploads/2013/08/1_257681.png

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:

/wp-content/uploads/2013/08/2_257682.png

The output of this SQL is as shown below:

/wp-content/uploads/2013/08/3_257683.png

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.

/wp-content/uploads/2013/08/4_257699.png

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 2nd 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:

/wp-content/uploads/2013/08/5_257700.png

Activate this Attribute View.

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

/wp-content/uploads/2013/08/6_257701.png

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):

/wp-content/uploads/2013/08/7_257706.png

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:

/wp-content/uploads/2013/08/8_257707.png

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:

/wp-content/uploads/2013/08/9_257712.png

/wp-content/uploads/2013/08/10_257713.png

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:

/wp-content/uploads/2013/08/11_257726.png

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.

To report this post you need to login first.

7 Comments

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

  1. Marc Bernard

    Hello Piyush,

    nice blog and solution. We should just mention that this only works for basic BW hierarchies. BW hierarchies can be much more complex with time-dependent node assignments, intervals as nodes, or even requiring sign reversal of the posted values (http://help.sap.com/saphelp_nw73/helpdata/en/4a/407c6aacb51cece10000000a42189b/content.htm?frameset=/en/a3/fe1140d72dc442e10000000a1550b0/frameset.htm).

    So just check the BW settings before you start to write SQL and model in SAP HANA 🙂

    Regards,
    Marc

    SAP Customer Solution Adoption (CSA)

    (0) 
  2. Jomy Joy

    Hi Piyush,

    I followed this approach but it failed with an error stating:

    [6993] Hierarchy Source data: Reference to physical root missing. The root node is not referenced in the PRED result column. The root node must be the predecessor of atleast one node.

    It seems the error is due to the some blank and new Profit Centers which is there in the Transactional Data but it is not there in the Hierarchy.

    It is a common case at clients where there might be records with blank or new profit centers.

    Let me know how to tackle this challenge.

    Regards,

    Jomy

    (0) 
    1. Piyush Jha Post author
      Hi Jomy,  The method will work even if you have some profit centers in transaction table which are not present in your master data. I have tried this scenario as well which seem to be working fine. Can you please provide some more input like queries, data and where have you created the hierarchy?  Regards, Piyush
      (0) 
      1. Jomy Joy

        Hi Piyush,

        As suggested,

        a) I’ve created and persisted the Profit Center Hierarchy in a table with Parent Node of Root Node being ‘?’.

        b) Then Created an Attribute View on top of this table.

        c) Then created an Analytic View and Joined Profit Center from Transactional table to to Attribute View as Left Outer Join

        d) Then Created a Calculation View having Union with Analytic View and Attribute View.

        e) Created a Profit Center Hierarchy in this Calculation View.

        Now I’m getting below error:

        Reference to physical root missing;PopHierarchyCreateSelect pop1, The root node is not referenced in the PRED result column. The root node must be the predecessor of at least one node. The nodes causing errors are : ] Dimension [ZH_PCH] hierarchy [ZH_PCH].[ZH_PCH]

        Thanks & regards,

        Jomy

        (0) 
  3. V Dinakar Reddy

    Hi Piyush,

    We are trying to implement this approach for one of our requirement.

    But requirement is a little different – here are the details.

    Our  Profit center object is a compounded object ( combination of Controlling area, Source system and profit center ).

    So we have created a calculated column in the analytical view, but we can not join this calculated column with attribute view in the LOGICAL JOIN of analytical view. So in the calculation view we have used “JOIN – analytical view with Attribute view ” and then used UNION and then created Parent Child Hierarchy as you have mentioned.

    But now when we tried to view this using Excel , it is giving an error  “” SQL Processing error :      The root node is not referenced in the PRED result column. The root node must be predecessor of at least one node.””

    Any idea on this ? could you please help us out ?

    Regards,

    V Dinakar Reddy.

    (0) 

Leave a Reply