It was quite a bit silent here. That's a fact. I had a lot on my plate since I have a new job and also before the promotion. But now I have a super cool topic on my mind I want to share with you.
Hierarchies are a common topic in companies. They offer the business users' flexibility to navigate in the frontend reports. In this example, I flatten the hierarchy structure to consume a hierarchy with text nodes and infoobjects. The Product Group hierarchy looks like the following screenshot.
The hierarchy has one top node for all entries and then different groups with some nodes. (I only have this screenshot with German descriptions because the data model has here German words used).
The following screenshot shows the hierarchy displayed in SE16 with all levels and relations.
I have this idea from this blog post on blogs.sap.com, and I now use this technique to get my hierarchy into SAP Datasphere. I use here a SQLScript View to build all necessary information in one view. You can also choose a different approach.
This code on the hierarchy table restrict the data to get the top level, so we only have the root in the result.
This code defines on the same hierarchy table all levels, excluding the top level.
To achieve the outcome (a flat hierarchy) I join now the first internal table with the second using nested joins between NODEID and PARENTID to get the parent child relationship.
It is only flattened up to level 3, because the hierarchy does not have more levels, but can be extended even further if needed.
The data preview displays the flatten structure of the hierarchy
Now I can create a dimension with a level hierarchy and consume it in my transactional data. Therefor I change the semantic usages to Dimension
After that is done, I create a hierarchy.
Now we can deploy the dimension and use it in the transactional view. Add the association.
Create the mapping between the fact data and the hierarchy dimension.
The last step is to use the fact model in an Analytic Model and analyze the data.
At the moment (end of May 2023) the hierarchies with text nodes are not supported yet from SAP Datasphere. So I think it is one way to go. If you have another way, please let me know in the comments or through LinkedIn. Next post will be how to use this concept and authorizations in SAP Datasphere.
I am Tobias, I write this blog since 2014, you can find me on 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.