How can complex hierarchies from SAP BW be mapped in SAP Datasphere? Since the introduction of the "Hierarchies with Directory" function, this is no longer a challenge.
The seamless integration of SAP BW hierarchies into SAP Datasphere has been a challenge up to now. This has often been an obstacle to the use of SAP's cloud-based data warehouse solution. With the introduction of the "Hierarchies with Directory" function in October last year, complex hierarchies, including those from SAP BW, are no longer a challenge for Datasphere. This enhanced synergy between Datasphere and BW makes Datasphere an increasingly attractive system that can now be easily integrated into an existing DWH landscape with BW.
- SAP Business Warehouse (SAP BW for short) is a proven SAP product that is used by many companies. SAP sees SAP Datasphere as the long-term successor to the on-premise BW system, emphasizing its ongoing investment in this cloud solution, which is still in development. Using Datasphere enables companies to expand their data strategy, benefit from innovative features, and at the same time make effective use of investments in their existing SAP infrastructure.
1. The Use Case
Imagine a fictional showcase company that sells electrical appliances and accessories worldwide through local locations. Recently, vending machines offering beverages and snacks have been introduced in the stores, expanding the product range. The introduction of the new products requires an adjustment to the reporting, including a new hierarchy that is mapped in both the BW system and the Datasphere. In addition to the overall report, a separate report for the new products is also desired.
However, due to the structure of the SAP BW system, implementing these hierarchies proves challenging. One option is to "flatten" the hierarchy and then rebuild it using multiple tables. However, this approach requires considerable effort and can lead to additional work for more complex hierarchies.
The hierarchy with directory now provides the solution to this problem and enables simple mapping for the hierarchies from SAP BW.
The following section presents a simplified model based on fictitious data from the BW system and mapped to the Datasphere.
2. What are hierarchies with directories?
The hierarchy with directory function enables complex mapping of hierarchies for a master data object. The hierarchy can have several parent objects with any number of levels.
In addition, multiple hierarchies for an object can be stored in the same table without any problems. For this reason, a directory is required. To distinguish between the hierarchies, they are defined in a directory table. This means that different hierarchies can be used for different requirements, which is very useful for reporting and analytics.
The individual objects in the hierarchy can be expanded with text dimensions. The tables from the SAP BW system are therefore ideal for modeling in Datasphere. There is no need to join tables in Datasphere or perform time-consuming post-processing. This makes the ingest process clear and easy to understand.
3. Data transfer: SAP BW to Datasphere
| No. | Name Datasphere | Name Business Warehouse |
|---|---|---|
| 1 | Hierarchy Name | RSHIEDIR |
| 2 | Product Hierarchy | /BIC/HCIT_MPROD |
| 3 | Product Dimensions | /BIC/MCIT_MPROD |
| 4 | Sales Data | /BIC/ACIT_ASALS2 |
A. Hierarchy Name – Semantic Use: Dimension
The table "RSHIEDIR" contains all hierarchies in the BW system. To use the hierarchies relating to our "Product" object, you must filter according to the corresponding info object "CIT_MPROD."
HIEID is the hierarchy key created in BW. HIENM is the name of the hierarchy.
B. Product Hierarchy – Semantic usage: Hierarchy with directory
Here, /BIC/HCIT_MPROD is limited to the relevant columns for the overview.
The column "IOBJNM" specifies the object name and "NODENAME" specifies the corresponding key of the object. In order to use the table with the semantic usage "hierarchy with directory," a calculated column must be created for each of the three different objects in the "IOBJNM" column. The key is now written to the respective column depending on the object. The other columns remain zero.
For the info object "CIT_MPROD," the calculated column "PRODUCT" was created using the following logic.
The logic is adjusted accordingly for the other columns with "CIT_MPRGR" for "PRODUCTCATEGORY" and "CIT_MDPMT" for "DEPARTMENT." This then results in the finished hierarchy table.
In many hierarchies, only two objects can be found in the IOBJNM column: the info object itself and '0HIER_NODE'. In such cases, two separate columns should be created for the two objects.
C. Product Dimension – Semantic Use: Dimension
The product dimension can simply be transferred from the source table. The columns "OBJVERS" and "CHANGED" are irrelevant in this case and can be hidden.
D. Sales Data – Semantic Use: Fact
The sales data can also be easily transferred from the source table.
4. Modeling in Datasphere
A. Setting up the hierarchy with directory
In order to set up the hierarchy, an association with the hierarchy name table must already exist. This must be created.
Now the hierarchy settings can be made.
All info objects, in this case "CIT_MPROD," "CIT_MPRGR," and "CIT_MDPMT," must be specified with correct names as node type values. The "column" must correspond to the calculated column created in each case.
B. Further associations
Now the other two relationships need to be implemented: the relationship between Sales Data and Product Dimension, and between Product Dimension and Product Hierarchy. In both cases, the join is to Product.
The basic modeling is now complete. You can expand the model by adding text tables. For more information, see this blog post from SAP.
An initial analytical model can already be created using the current structure.
5. Analytic Model & Preview of Hierarchies
The hierarchies are now visible in the preview of the analytical model. There, you can now choose between the two integrated hierarchies.
The "CLEVERIT HIERARCHY" forms the basis for the overall report and contains all products in the product range with 5 levels for 3 objects.
All – Food – Drinks – Juice – Orange Juice
The "CLEVERIT HIERARCHY ONLYFOOD" contains all new products and thus provides a selected overview of the results of the new launch.
In this case, the text tables of the objects have already been successfully integrated into the model, which means that the corresponding text information can already be displayed.
Would you like to learn more about SAP Datasphere?
Customer Exit Variables Explained in Datasphere
With SAP Datasphere, SAP consistently advances its data platform towards a cloud-centric future. For many BW users…
SAP Datasphere: Health Monitoring
WHY STANDARD MONITORING FOR SAP DATASPHERE IS INSUFFICIENT AND CUSTOMIZED SOLUTIONS BECOME A NECESSITY. …
SAP Business Data Cloud – Revolution or Just Another Upgrade?
With the announcement of the SAP Business Data Cloud (BDC), SAP establishes a new milestone in…
Mapping Hierarchies from SAP BW to Datasphere: Here's How
How can complex hierarchies from SAP BW be represented in SAP Datasphere? Since its introduction…
How-To: Facilitating Data Transfer between SAP Datasphere and Python via hdbcli
The Python package hdbcli enables access to data within SAP Datasphere, thereby facilitating…
SAP Datasphere – The End of SAP Business Warehouse?
Does SAP Datasphere signify the end of SAP Business Warehouse? In August 2023, SAP...
6. Summary
The introduction of hierarchies with directories undoubtedly marks a significant step forward in mapping hierarchies from SAP BW and S4/HANA to the Datasphere. This not only facilitates the structuring and organization of data within the Datasphere, but also enables the seamless integration and management of hierarchical information from various SAP source systems.
This makes implementing Datasphere into corporate strategy or switching to Datasphere significantly more attractive for companies. Integrating hierarchies with directories offers valuable flexibility and efficiency to companies looking to improve their data infrastructure.
In addition, SAP Datasphere's compatibility with other SAP Business Technology Platform (BTP) cloud solutions, such as SAP Analytics Cloud, and the ability to connect non-SAP systems offer a wide range of integration options. This enables companies to use and analyze their data in a variety of ways, regardless of whether it is stored in the cloud or on-premises.
Would you like to learn more? Then our Discovery Workshop is precisely what you need.
Author: Daniel Kern
Christopher Kampmann
Head of Business Unit
Data & Analytics
christopher.kampmann@isr.de
+49 (0) 151 422 05 448
