Order siblings in a Hierarchy with Directory with ...

Sefan_Linders · ‎03-14-2024

In the diagram below you can see three departments: Sales, Marketing and Development, ordered as such. They are not ordered alphabetically, but according to an alternative sequence. SAP S/4HANA allows you to create hierarchies like that, where the nodes under the same parent have a custom order. This is called the siblings order, in analogy to a family setup where childs share the same parent.

Figure 1: A custom ordering of nodes based on a node sequence

In SAP Datasphere, you can configure a siblings order as well, using a Custom CSN Annotation. In this blog we explain how, taking the Profit Center Hierarchy as an example. Once configured, SAP Analytics Cloud will take the ordering of siblings into account.

This blog is part of a series in which we introduce the Hierarchy with Directory:

Please note a few things before you get started:

This blog adapts the Profit Center Hierarchy with Directory view, which is delivered as part of the community content for Hierarchy with Directory.
A siblings order can only be set if you have a suitable field to order on in your hierarchy data. If you extract from SAP S/4HANA Hierarchy CDS Views, be it cloud or on-prem, only Replication Flow extraction provides you with such field. More on this at the end of this blog.
The siblings order also works for views with Semantic Usage: Hierarchy, although in this post we only look at Semantic Usage: Hierarchy with Directory.

Previewing the Profit Center hierarchy before setting the Siblings Order

While testing this feature and writing this blog, we used the Profit Center Hierarchy that is delivered as part of the community content for Hierarchy with Directory. It is associated in the G/L Line Item Cube Analytic Model, as you can see in the screenshot below.

Figure 2: Analytic Model that includes Profit Center dimension

Several hierarchies for Profit Center existed already in our source data, and on purpose, we select a hierarchy for which we know that ordering based on a sequence ID will yield a different result than the default alphabetical order. You will see that later in this post when we define the ordering method.

Figure 3: Choosing a hierarchy in the data preview of an Analytic Model

In Figure 4, you can see the data preview of the G/L Line Items, in shape of a Profit Center Hierarchy. Note that 0LEAD comes before 0PARTNER, according to the default alphabetical order of the technical name. However, this was not the modeled order in SAP S/4HANA.

Figure 4: The ordering of nodes is alphabetical, based on the Node ID / Child ID

Looking up the Hierarchy with Directory view and its data

Let’s look at the Profit Center Hierarchy View. Just to explain which view we are moving to now, below screenshot shows you the lineage from the Analytic Model (all the way on the right), to the Profit Center Hierarchy. If you want to understand the full relationship between entities in a Hierarchy with Directory model, have a look at the start of this blog post series.

Figure 5: Look up the Hierarchy with Directory view to change the ordering of nodes

When we open the hierarchy view and display the data, we first filter the data on ProfitCenterHierarchy=*PC_HIER*, to show only the nodes of the hierarchy that we selected earlier in our data preview. You can see two fields that provide an order to the nodes: SequenceNumber and HierarchyNodeSequence. Both fields provide the same order, the former is only a concatenation of the HierarchyNodeSequence and the NODEID. Note that according to this data, 0PARTNER should come before 0LEAD, contrary to the alphabetical order that we saw.

Figure 6: The data already contain fields to order the nodes on

In the case of the Profit Center Hierarchy CDS View, the CDS definition in the source system already provides an annotation for the siblings order, called siblingsOrder. As you can see in below screenshot, it consists of a reference to a field, and contains a direction: here ASC for Ascending. This annotation is not consumed by SAP Datasphere automatically, but it does provide you with the right information to configure it. We will take SequenceNumber, just like the CDS View prescribes.

Figure 7: The CDS definition contains a definition of the siblings order

Setting the order using the Custom CSN Annotations editor

In SAP Datasphere, the siblings order can be configured in the view with Semantic Usage: Hierarchy with Directory. In our case, that is the Profit Center Hierarchy view that we already looked up.

There is no graphical means to choose the field to order on. Instead, you can apply the magic in the CSN Custom Annotations editor. The red arrow in the below screenshot points to where you need to click on to open this editor inside the hierarchy view.

Figure 8: Opening the Custom CSN Annotations editor

Once opened, move to the section in the code with key “@Hierarchy.parentChild”, and add the following code. The change is also reflected in Figure 9.

"siblingsOrder": [
   {
      "by": "SequenceNumber",
      "direction": {
         "#": "ASC"
      }
   }
]

Figure 9: Code added to define a siblings order

After making this change, we save the view and deploy it. Then, we go back to the Analytic Model, and deploy it as well. Without the latter deployment, the Analytic Model does not take this change to an associated entity into account.

Now, when data previewing the same hierarchy as before, we can see that the order has changed. The screenshot below shows that 0PARTNER now comes before 0LEAD. SAP Analytics Cloud will also take this change into account and apply the configured order.

Figure 10: Now the order of 0PARTNER and 0LEAD is reversed

Replication Flows versus Remote Tables

There is one thing we need to talk about though. At present, if you consume a hierarchy from SAP S/4HANA through a CDS View, the method of extraction determines which kind of hierarchy structure is delivered.

Replication Flows extract the exact CDS View definition as defined in the source, and in the case of SAP-provided CDS views for hierarchies, those CDS Views will often contain the sequence number discussed in this blog. However, if you extract that same CDS View using Remote Tables, the structure looks wildly different. There are historic reasons for that, but I’ll spare you the details.

Besides other structural differences, the Remote Table structure for SAP-provided Hierarchy CDS Views does not include a field with a sequence. Instead, it contains a field NEXTID that, for each child node, points to the node that comes after. This is illustrated in the below figure. This could be called a pointer, or a daisy chaining mechanism if you will, but not a sequence as we talked about before. And SAP Datasphere wants that sequence.

Figure 11: NextID ordering method, common for remote table access

So if you have the option to choose your extraction method, then choose Replication Flows. If you don’t have that option, but you do want to order your siblings, you are left with two options that I can think of:

Create a custom CDS view that consumes the SAP-provided Hierarchy CDS View and retains the original structure.
Write a sqlscript view in SAP Datasphere that converts the NextID into a sequence.

I haven’t tried either option yet, but feel free to leave your experiences in the comments.

Conclusion

In this blog we described what it means to order the nodes that share the same parent, in other words, ordering the siblings. The good news is that this is already supported by SAP Datasphere and SAP Analytics Cloud, and we explained how to configure this using Custom CDS Annotations.