In this post I want to describe how you can use hierarchies in SAP Datasphere just like in good old SAP BW. 

To be able to use a hierarchy in SAP Datasphere like in SAP Business Warehouse, you need an object with the semantic type "Hierarchy with Directory". 

There are several blog posts on this topic that explain it in detail:

• https://blogs.sap.com/2024/01/08/creating-a-hierarchy-with-directory-in-sap-datasphere/
• https://blogs.sap.com/2024/01/15/walkthrough-of-different-enterprise-scenarios-via-community-content-package-gl-account-external-hierarchy-with-replication-flow./
• https://blogs.sap.com/2024/01/15/an-introduction-to-hierarchy-with-directory-in-sap-datasphere/
• https://blogs.sap.com/2024/01/15/modeling-a-basic-hierarchy-with-directory-in-sap-datasphere/
• https://blogs.sap.com/2024/01/15/modeling-an-advanced-hierarchy-with-directory-in-sap-datasphere/
• https://blogs.sap.com/2024/01/15/guidecreate-sap-s-4hana-external-gl-account-hierarchy-within-sap-datasphere-through-community-content-packages/

The entity relationship model for hierarchies with directories looks like this:

For the hierarchy with directories, we need different tables directly from SAP BW.

• RSHIERDIRT - for the hierarchy directory texts
• RSHIERDIR - for the hierarchy master data
• RSTHIERNODE - for the texts of the text nodes
• /BIC/HZCDWC001 - for the hierarchy table of the Product Group InfoObject
• /BIC/MZCDWC001 - for the product group master data
• ZDWC007 - for the transaction data of the store

The transaction data is contained in the Facts table for the store. The transaction data has an association to the Product Category dimension. The Product Group dimension has a hierarchy with directory assignment. 

However, the main logic takes place in the view with the semantic type Hierarchy with Directory. In the Entity-Relationship Model, it is recognizable as "Product Group with Hierarchy Directory".

The directory has a text association with the hierarchy texts (table RSTHIERNODE), a dimension association with the hierarchy master data (table RSHIERDIR), which in turn has a text association with the hierarchy directory texts table (table RSHIEDIRT). So much for the structure of the Entity-Relationship model. 

We start with the table Hierarchy Directory Tables (RSHIEDIRT). This is a remote table in the SAP BW system with the semantic usage Text.

In the attributes of the table, the language key, the text, and the column name are defined.

Next, we look at the Hierarchy Master Data table (RSHIEDIR). This table lists all the hierarchies that exist in the SAP BW system.

To get only the data we need, we filter the hierarchy IDs we need. Using projection, we remove the unneeded columns and then calculate the date from to obtain a correct validity. The semantic use of views is dimensioning.

The RSTHIERNODE table is used to obtain the texts associated with the hierarchy nodes. Filter the hierarchy IDs you require and hide the columns you do not need. 

As semantic usage text and the columns with the necessary semantic types (language, text, description) are defined.

Now the hierarchy is created with directories. The basis is the hierarchy table of the Product Group dimension (/BIC/HZCDWC001).

First, various unneeded columns are hidden (e.g., object version, level of a hierarchy node). Then, the Hierarchy Node Name (NODENAME) column must be split into two columns to get a correct assignment for the hierarchy. One for the folder (0HIER_NODE) and one for the original InfoObject (ZCDWC001). So we have two new columns in this view: Leaf Node (ZCDWC001) and Node ID (0HIER_NODE).

This view now forms the basis for the hierarchy with directories.

The calculation of the new columns had to be moved to a separate view, otherwise it would lead to an error. As a semantic usage for the following view, we use Hierarchy with Directory and then define our settings. If necessary, the dimension assignment to the hierarchy's master data must be done beforehand.

In the details panel, we now make the settings for the hierarchy with directory.

We define the Parent Column and the Child Column. We also need to select the column containing the name or ID of the hierarchy. The association with the Hierarchy Directory Entity (Hierarchy Master Data) should be done automatically by the previous association.

Next, we define the Nodetype column. The last step is the definition of the node value. Once for the folder (0HIER_NODE) and the corresponding column (NODE_ID) and then also for the individual leaves of the hierarchy. Here is the InfoObject ZCDWC001 with the Leaf Node column. 

By clicking OK, the hierarchy can be implemented and published. Afterward, the association with the dimension Product Group can be performed. In the analytical model, the hierarchy can now be selected for the dimension

This is one way to model hierarchies in SAP Datasphere. In this post, I get all the information directly from the old BW and reuse it to build a data model in Datasphere. If you have any thoughts or ideas, please post them in the comments.

• 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