In our previous blog, we covered the basics of creating a Hierarchy with Directory. Now, we're taking a step further in this blog by enhancing our model with an extra node type, language-dependent texts, and time-dependency, both for the hierarchies and node assignments. These advanced features are often seen in SAP S/4HANA or SAP BW hierarchies but are also applicable to non-SAP hierarchies. We continue to use a simplified data model with local data for clarity, making it easier to grasp these concepts before applying them to more complex, real-world data sources. The additions to the data model are pointed out in Figure 1, with the numbering corresponding to the section numbers in this blog post. The figure shows all views, and each view has a table with data underneath.
This blog is part of a series in which we introduce the Hierarchy with Directory:
Figure 1: In yellow all extensions to the views of the basic model
Until now, our node types included Product Category (an inner node type) and Product ID (the leaf node type). To demonstrate the use of multiple inner node types within a single hierarchy, we extend the model with a Department node type. The idea is that the company that sells these products, has different departments, each responsible for one or more product categories.
To incorporate this new node type, we'll make the following updates:
Figure 2: Extending the hierarchy table with a Department ID column and new hierarchy entries
Figure 3: Adding a new record to the Product Hierarchy Directory table
Figure 4: Adding node type definition for Department in the Hierarchy view
A new data preview shows then the new grouping into departments.
Figure 5: OATMILK_1 now rolling up into Product Categories, and then into the new node type Department
Previously, our inner nodes Product Category ID and Department ID were only defined as just text-based columns. However, these are perfect candidates to be defined as Dimensions. Once associated to the Hierarchy, you can utilize dimension features like text labels, language-dependent texts, and/or time-dependent texts, which are typical in SAP S/4HANA and SAP BW hierarchies.
To illustrate this, we created a Product Category dimension with language-dependent texts. The screenshot below illustrates the required new objects and their association with the Product Hierarchy.
Figure 6: New objects for Product Category dimension with language-dependent texts
The following steps describe these changes in more detail:
Figure 7: Sample data for Product Category text table
Figure 8: Semantics definition for the Product Category text view
Figure 9: The Product Category dimension view
When refreshing the data preview of the Analytic Model, you now have the option to display either the ID, the Descriptions, or both (Figure 10), after which the more readable labels show up for the product Category nodes (Figure 11).
Figure 10: Choosing ID, Description or both as the presentation mode
Figure 11: Product Category texts are now displayed
Because we created a dimension with language-dependent texts, we can now switch the data language in the SAP Datasphere setting menu. In below screenshots, you can see that we change the language to Dutch, after which the Product Category nodes show the Dutch translation.
Figure 12: Changing the Data Access Language
Figure 13: Product Category texts are now displayed in Dutch
We repeated the same exercise for Department by creating a new dimension with language-dependent texts and extended the Product dimension by adding language-dependent texts. The result is shown in below screenshot. Please note that to display the texts for leaf nodes, like our Product node, you don’t need to create an association from the hierarchy to the dimension. The existing association from the product dimension to the hierarchy is adequate. For inner nodes this is different, and you will have to create associations from the hierarchy to the dimension.
Figure 14: All node types are now associated to dimensions with texts
Please note that at time of writing, when texts have been added to a dimension, it is not possible anymore to display the technical name of the dimension. When selecting ID as presentation, the Child ID will show up instead. In our data model the Child ID is represented by the field Node ID. How this looks like is illustrated in the screenshot below.
Figure 15: Presentation mode ID or ID and Description will show the Child field as ID and not the technical name of the dimension
Time-dependency is a common requirement in SAP S/4HANA and SAP BW hierarchies, manifesting in several ways:
We’ll run through setting up time-dependency for the hierarchy and node assignments.
A hierarchy can be considered active or not, based on an optional time-validity interval. This requires an extension to the previously modeled Product Hierarchy Directory, by adding a Valid From and Valid To field to the Hierarchy Directory table and adjusting the semantics in the Hierarchy Directory view to mark those fields as Business Date From and Business Date To. The below screenshots show the changes we made for the Product Hierarchy Directory table and view.
Figure 16: Adding time-interval fields to the Product Hierarchy Directory
Figure 17: Configuring the semantics for the time-intervals in the Hierarchy Directory View
After making these changes, we could simply reopen the Analytic Model to use today’s date as the reference date to select active hierarchies. Instead, we add a Reference Date Variable to the Analytic Model, so that we can choose a reference date ourselves. In the below screenshots you can see how we add this date inside the main design screen of the Analytic Model. Then, we open the data preview again, choose January 1, 2024 as the reference date, and see the two hierarchies active at that date when prompted for a hierarchy selection.
Figure 18: Adding a reference date variable to the Analytic Model
Figure 19: Choosing the reference date when previewing data in the Analytic Model
Figure 20: The hierarchy selection after providing a 2024 reference date, shows only the two active hierarchies at that point in time
The validity of node assignments works in a similar fashion as for hierarchies themselves. As depicted in the figure below, we’ve extended the Product Hierarchy table and view with a validity interval and added specific dates to the data in the table. We configured the semantics that define the Valid From and Valid To fields as Business Date From and Business Date To, similarly as we did for the directory in Figure 17.
Figure 21: Time-interval added to the node assignments in the Product Hierarchy Directory view
The data illustrates a notable change: the Sports department, initially aligned with the Non-food department until the end of 2023, shifts its association to the Food department starting in 2024. This transition is visible in the data preview for the year 2024, as shown in Figure 22.
Figure 22: Data preview with selection date January 1, 2024, where the sports departments changed assignment from the Non-food to Food department
As mentioned before, time-dependency of dimension texts is supported as well for hierarchies and is defined in the Text view using date intervals, just like we outlined already for the time-dependency of hierarchies and hierarchy nodes. Applying the semantics works in the same fashion, by choosing a field for Business Date From and Business Date To. From the previous instructions, you should be able to build this yourself. A sample of the data, with both time-dependent as language-dependent texts, is displayed in the figure below.
Figure 23: Data sample of both language- and time-dependent texts
In this blog we extended the basic hierarchy with another inner node type, language-dependent texts, and time-dependency for hierarchies and node assignments. With these features, we presented most of the features of the Hierarchies with Directory. Together, these features are aligned with well-known hierarchies from SAP S/4HANA and SAP BW, which makes it now a lot easier to consume such hierarchies in SAP Datasphere.
Figure 24: Final hierarchy with different node types and texts
Up next: Create an SAP S/4HANA GL Account Hierarchy within SAP Datasphere through Community Content
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
30 | |
19 | |
10 | |
9 | |
8 | |
8 | |
7 | |
7 | |
7 | |
6 |