In the previous blog, we provided an overview of the new features introduced with the Hierarchy with Directory. In this blog, we'll guide you through creating a basic Hierarchy with Directory, starting from scratch and progressing to the data preview in an Analytic Model. Our focus is on simplicity, ensuring you grasp the fundamental concepts with minimal complexity. To keep it simple, we use data from local tables, before moving on to data from SAP S/4HANA or SAP BW in future posts. However, the model is complete and comes with a few lines of transaction and master data. In our upcoming blog, we'll enhance the model by introducing advanced elements such as language-dependent texts, additional node types, and time-dependency features.
This blog is part of a series in which we introduce the Hierarchy with Directory:
Figure 1: One of the hierarchies created in this blog post
Our model showcases product sales within a product hierarchy. For this, we need to create at least four different views, as illustrated in Figure 2. We need views of the following Semantic Usage:
The figure below shows the associations between these four views, and the minimal column set that we need for the hierarchy to function. Each of the views need a data set underneath, which we provide using local tables and in which we entered manually a few records. Missing in the figure is the Analytic Model. We will need it when previewing the data, but for now we just want to focus on the core elements needed.
Figure 2: Minimum set of objects to showcase a Hierarchy with Directory
Each view reads from a local table with a few sample records. Figure 3 lists our sales transactions. Yes, we know, nine records are nothing close to reality, but it serves its purpose.
Figure 3: Sample transaction data of product sales
Figure 4 lists the unique Product IDs. Usually, a product dimension would have all kinds of master data, but again, we keep it as simple as possible and therefore this table has just one column.
Figure 4: Minimal data set for Product dimension
In this blog we will work with two data-driven hierarchies, which means that we add two entries to the Product Hierarchy Directory as displayed in Figure 5. If S/4HANA would be your source, you might have many more of these entries, with additional semantics such as date validity intervals. We come back on those additional semantics in the next post.
Figure 5: Minimal form of a Hierarchy Directory with two entries
Figure 6 illustrates the hierarchy's sample data, detailing the parent-child node relationships for our two hierarchies. The data here is already organized according to how the Hierarchy with Directory will consume it, as we will see later.
It is important to understand that there are two keys at play here:
Study the below sample data, and take note of how a single product, like OATMILK_1, is categorized differently across two hierarchies, marked respectively green and purple. In the green hierarchy, called DRINKS_OR_NOT, the leaf node is of Node Type PRODUCT. Therefore, the Product ID determines the real key, OATMILK_1. In this hierarchy table its Node ID, the child, is 4. Its parent is Node ID 0, and as you can see, for that node the Node Type is PRODUCT_CATEGORY, and the real key therefore is Product Category ID.
Figure 6: The hierarchy node relationships for two different hierarchies
Finally, it is time to put the semantics in place by creating the views. The Transactions view is of type Fact, and the Product view is of type Dimension. We’re assuming no further explanation is needed on those views, so we continue here with the final two views.
The hierarchy directory is, at its most basic, just a list of the different hierarchies with a label, modeled as a dimension. The dimension requires a key field and a label field. In our case this is Hierarchy Name and Hierarchy Label. You can choose the names of the field yourselves, but this dimension will always at least a key and a label field. Make sure the label field is configured as Text and assigned to the Hierarchy Name field, as you can see in Figure 7. This label will show up in our reporting tool when we want to select the active hierarchy.
We create this directory dimension first, because we will need to refer to it when we build the hierarchy view.
Figure 7: Column semantics for Product Hierarchy Directory
And then it is time for the hierarchy itself, for which we follow these steps:
Figure 8: Selecting the settings for the Product Hierarchy
In the settings dialog we do the following, according to the numbering in Figure 9:
Figure 9: The Hierarchy with Directory settings
9. Add another Node Type Value by clicking the “+” sign.
10. Enter the Node Type Value
11. Choose Product Category ID as the Node Type Value
Figure 10: Adding the Product Category node type value
As you can see, it is also possible to add multiple columns to define the target fields for a node type. This allows you to use compound keys, which you will need for certain S/4 or BW hierarchies.
That’s it for configuring the Product Hierarchy view. After deploying the Product Hierarchy, go to the Product dimension, and create an association of type Hierarchy with Directory to the Product Hierarchy view.
To preview the transactions as part of the hierarchy, we need an Analytic Model to consume the created objects. This is straightforward:
This is all you need to do before previewing the data. Even when you’re just playing around, it’s helpful to save and deploy the Analytic Model. In general, whenever you make changes to any of the objects and you want to make sure all changes are properly propagated, just redeploy the Analytic Model. This will redeploy all related objects and ensure that the Analytic Model is aware of your change.
To view your data in the Analytic Model, we simply follow these steps:
Figure 11: Selection of a hierarchy in data preview of an Analytic Model
Figure 12: Hierarchy selection
Figure 13: Change the hierarchy display
Figure 14 and Figure 15 show the same transactions, aggregated in the two different hierarchies. Again, you could have a look at Product ID OATMILK_1 and its assignment according to the data in Figure 6.
Figure 14: The Drinks or not hierarchy
Figure 15: The More categories hierarchy
In this blog, we've guided you through the creation of a basic yet functional set of tables and views, demonstrating a Hierarchy with Directory in SAP Datasphere. You now have a solid understanding of the foundational aspects. Hierarchies sourced from SAP S/4HANA or SAP BW often necessitate additional features. Therefore, we extend our model in the next blog: Modeling an advanced Hierarchy with Directory.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
28 | |
27 | |
14 | |
13 | |
12 | |
11 | |
10 | |
7 | |
7 | |
6 |