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.
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.
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.
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.
The result looks like this
As you see, the hierarchy node 1 has a child with the id 2. The BW view looks like this:
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).
In the SAP Analytics Cloud (SAC), the data looks like this:
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.
I am Tobias, I write this blog since 2014, you can find me on Twitter, LinkedIn, Facebook 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.
- 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
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.