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.
Figure 1: One of the hierarchies created in this blog post
The data model in its most basic form
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:
1. Fact (Transactions), with transactions of products sold.
2. Dimension (Product), that lists the unique product instances.
3. Hierarchy with Directory (Product Hierarchy), which contains the parent-child relationships between the hierarchy nodes, and the definition of all hierarchy semantics.
4. Hierarchy Directory (Product Hierarchy Directory), which lists all the hierarchies.
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
The sample data
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:
- One key specifies the parent-child relationship. In our data model, this is field Node ID. It is the child of the parent-child relationship.
- A second key to identify the respective node’s real In our data model, this is Product ID or Product Category ID. The Node Type field determines which of the two keys should be considered. Later, we will see that dimensions can be associated to this key, making them in fact dimension keys.
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
The views
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.
Hierarchy Directory
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
The Hierarchy with Directory
And then it is time for the hierarchy itself, for which we follow these steps:
1. Create a view with “Hierarchy with Directory” as its Semantic Usage. In below figure you can see that we are using a SQL view, but you can use a graphical view as well.
2. Consume the table with the parent-child relationships, in our case LT_NODES, of which the content was shown in Figure 6.
3. Create an association to the Product Hierarchy Directory between the Hierarchy Name field from the hierarchy itself to the hierarchy directory.
4. Choose “Hierarchy with Directory Settings” as you can see in Figure 8. This opens up the settings dialog.
Figure 8: Selecting the settings for the Product Hierarchy
In the settings dialog we do the following, according to the numbering in Figure 9:
1. Choose the parent field.
2. Choose the child field.
3. Choose the Hierarchy Name column, which should be the column from which we created an association to the Product Directory View.
4. The Hierarchy Directory Entity should now be displayed automatically.
5. Choose the column that defines the Node Type.
6. Our leaf node is of node type value PRODUCT, so we manually enter that value here.
7. We choose the column that the Product ID is in. This will be the column showing up later as the node value.
8. Choose “Set as Leaf” if not already set.
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.
The Analytic Model
To preview the transactions as part of the hierarchy, we need an Analytic Model to consume the created objects. This is straightforward:
1. Create a new Analytic Model, and drag the Transaction view in.
2. Import the associations when you are prompted for it.
3. Select Amount as the required measure.
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.
Data preview
To view your data in the Analytic Model, we simply follow these steps:
1. Choose Preview to go to the preview screen.
2. Insert Product ID into the rows, creating a straightforward list of sales per product.
3. As outlined in Figure 11, hover over the Product ID entry in the Rows section, then choose Hierarchy, and then choose Select Hierarchy. A dialog pops up to choose the hierarchy (see Figure 12).
4. Change the display format to Description as shown in Figure 13, which will showcase the node text rather than the node ID.
5. Take a moment to appreciate the final display.
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
No comments:
Post a Comment