Modeling an advanced Hierarchy with Directory in SAP Datasphere

[Sefan_Linders]

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:

1. An Introduction to Hierarchy with Directory
2. Modeling a basic Hierarchy with Directory
3. Modeling an advanced Hierarchy with Directory (this blog)
4. Create SAP S/4HANA GL Account Hierarchy within SAP Datasphere through Community Content
5. Walkthrough of different Enterprise Scenarios via Community Content package – GL Account External Hi...
6. Order siblings in a Hierarchy with Directory

Figure 1: In yellow all extensions to the views of the basic model

1. Adding another node type

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:

1. Expand the hierarchy table by adding a Department ID
2. Insert new entries into the hierarchy table to create a new hierarchy that includes department nodes. We highlight this in Figure 2 with an example, where you can traverse the hierarchy from leaf to root node, starting from the leaf node with Product ID OATMILK_1. The other hierarchies we leave untouched.
3. Add a new entry to the directory table to denote the new Department hierarchy (see Figure 3).
4. Update the hierarchy view to include the new node type (see Figure 4).
5. Add a few Product IDs and transactions to the data, to make the data preview richer.

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

2. Adding dimensions with language-dependent texts to node types

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:

1. Create a Product Category text table with columns for Language and Text.
2. Add sample data to the table. We fill the table for language English (EN) and Dutch (NL) (see Figure 7).
3. Create a Product Category Text view with semantics configured for language and label (see Figure 8).
4. Create Product Category table with key entries.
5. Create a Product Category dimension view, consuming the table, with an association to the text view (see Figure 9).
6. Create an association from the hierarchy view to the new dimension.

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

Adding time-dependency to hierarchies, nodes, and texts

Time-dependency is a common requirement in SAP S/4HANA and SAP BW hierarchies, manifesting in several ways:

1. Time-dependency of a hierarchy itself. Determined within the Hierarchy Directory, this aspect decides if and when a hierarchy is available.
2. Time-dependency of node assignments. Defined in the Hierarchy with Directory view, this determines which node is assigned to a parent node within specific time intervals.
3. Time-dependency of dimension texts. Defined in the Text view using date intervals. While this feature was already accessible, it's now also applicable to a Hierarchy with Directory.

We’ll run through setting up time-dependency for the hierarchy and node assignments.

3. Time-dependency of hierarchies

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

4. Time-dependency of node assignments

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

5. Time-dependency of dimension texts

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

Conclusion

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