· 

Create a parent-child hierarchy in SAP Data Warehouse Cloud

There are different ways to create hierarchies in Data Warehouse Cloud. One way is to use a CSV file and upload it into DWC. Another way is to use the existing hierarchies of your SAP BW system. In this post, I want to show how to use a hierarchy from BW and transform it into a parent-child hierarchy in Data Warehouse Cloud. After the transformation, we use the hierarchy in a view. 

 

First, we need a hierarchy on one InfoObject in SAP BW. 

SAP BW InfoObject with hierarchy
SAP BW InfoObject with hierarchy

As we have now the hierarchy on the InfoObject, we can now transfer the H-Table as a remote table into Data Warehouse Cloud. For the transfer, we use the ABAP Connection and select under ABAP Tables the corresponding table. With click on next step, we can import and deploy the table.

Import remote table into Data Warehouse Cloud
Import remote table into Data Warehouse Cloud

Now we have the H-Table in the Data Warehouse Cloud and can build an SQL View with a parent-child hierarchy. So let's create a new SQL View in the Data Builder of the Data Warehouse Cloud.

Create new SQL View in Data Warehouse Cloud
Create new SQL View in Data Warehouse Cloud

I created a SQL View. The statement select the node name as parent id and the node name as child id from my hierarchy table.

 

 select ( select "NODENAME" 
            from  "SPACE./BIC/HZNETWORK" as a 
           where a."NODEID" = b."PARENTID" ) as PARENTID,
        ( select "NODENAME"
            from "SPACE./BIC/HZNETWORK" as a 
           where a."NODEID" = b."NODEID" ) as CHILD_ID
 
   from "SPACE./BIC/HZNETWORK" as b

 

The result looks like this

Data preview in Data Warehouse Cloud
Data preview in Data Warehouse Cloud

As you see, the hierarchy node 1 has a child with the id 2. The BW view looks like this:

Hierarchy in SAP BW
Hierarchy in SAP BW

Now we can use this view for an association with the dimension Sales Network (The InfoObject is ZNETWORK) (see figure 1) and use the dimension in the Sales View for filtering and navigation (see figure 2).

Figure 1: Association hierarchy and dimension
Figure 1: Association hierarchy and dimension
Figure 2: Association dimension and view
Figure 2: Association dimension and view

In the SAP Analytics Cloud (SAC), the data looks like this:

Output of example in SAC
Output of example in SAC

Conclusion

This is just an example how you can create a parent-child hierarchy. SAP will offer such an option later when you look on the roadmap. I think this is an idea and if anyone has a better solution, please feel free to share it. One restriction has the parent-child hierarchy at the moment. There are no text nodes supported yet. Maybe it comes with the next releases of Data Warehouse Cloud. Feel free to share your thoughts on this idea in the comments.

author.


Hi,

I am Tobias, I write this blog since 2014, you can find me on Twitter, LinkedInFacebook and YouTube. I work as a Senior Business Warehouse Consultant. In 2016, I wrote the first edition of Analysis Office - The Comprehensive Guide. If you want, you can leave me a PayPal coffee donation. You can also contact me directly if you want.


Subscribe


  • In my newsletter, you get informed about new topics
  • You learn how to use Analysis Office
  • You get tips and tricks about SAP BI topics
  • You get the first 3 chapters of my e-book Analysis Office - The Comprehensive Guide for free
* indicates required

You want to know SAP Analysis Office in a perfect detail?
You want to know how to build an Excel Dashboard with your Query in Analysis Office? 
You want to know how functions in SAP Analysis Office works?

 

Then you have to take a look into Analysis Office  - The Comprehensive Guide. Either as a video course or as an e-book.


Write a comment

Comments: 2
  • #1

    Sebastian Gesiarz (Friday, 03 June 2022 16:36)

    Thank you! You saved me a ton of work :)

  • #2

    Tobias (Sunday, 05 June 2022 16:37)

    Hi,

    you are welcome. Good to hear that it helps someone ;)