This blog is about how to handle Not Assigned(REST_H) nodes in CDS Hierarchy Directory of Analytical query, to get the result set either without Not Assigned node or filtered Not Assigned node. Let’s start with some basic information about CDS Hierarchy Directory.

Introduction to CDS Hierarchy Directory

There are two types of Hierarchy views. First where we use annotation ‘@Hierarchy’ and new one where we use ‘define hierarchy <NAME>’ (from 2023 on Prem and 2308 Cloud). In both the ways we can define association to a view which is then used as Hierarchy Directory.

Hierarchy Directory is CDS view.

• It contains the List of all hierarchies available in the hierarchy view.
• It can have Text for the hierarchies (language independent case) Or it can have an association to a text view (language dependent case)
• It can contain a field which determine whether the Not Assigned node will be displayed or not.
• It can contain a field which describes a filter which should be applied to the Not Assigned node.
• It can contain a field which describes when a hierarchy was changed last time and which has annotation as ‘@Semantics.systemDate.lastChangedAt : true (for date)’ or ‘@Semantics.systemDateTime.lastChangedAt: true (for timestamp)’

When this field exist, then analytical engine will buffer the hierarchy and will renew the buffer when hierarchy is changed. Without this field hierarchy will always be rebuild and it can be performance overhead.

Use of NOTASSIGNEDNODE Annotations in CDS Hierarchy Directory

• @HIERARCHY.NOTASSIGNEDNODE.SUPPRESSINDICATOR can be used to suppress the display of Not Assigned nodes.

It can be used for one field in a Hierarchy Directory view. The data type of the field must be Boolean (CHAR field with length 1 and with values ” or ‘X’). If the flag is set for an entry in the directory, then the analytical engine doesn’t create a ‘Not Assigned’ node. In this case the hierarchy itself acts as a filter and data which is not a leaf in the hierarchy is not part of the resultset.

• @HIERARCHY.NOTASSIGNEDNODE.FILTER can be used to restrict the display of Not Assigned nodes.

It can be used for a field in the Hierarchy Directory view. The field needs in addition, a foreign-key association to a dimension view. This dimension view has also to be target of a foreign-key association defined in the dimension view which holds the hierarchy. The field with that foreign-key association must be a non representative key field. When the analytical engine resolves the ‘Not Assigned’ node, it derives a filter from the hierarchy’s name and applies this filter to the dimension values which have to be added to the ‘Not Assigned’ node. The other dimension values, which don’t fulfil the filter criteria are ignored. In this case the hierarchy itself acts as a filter. The annotation can be used for key and for non-key fields in the Hierarchy Directory. Only constraint is that the field needs a foreign-key association.
With this feature you can improve the performance. It is only useful for hierarchies on dimensions with more than one key field and if one hierarchy should only contain leaves belonging to one value of a key field. A typical example is a cost center hierarchy. All cost centers of one hierarchy should belong to the same controlling area. Without this feature the analytical engine would also add cost centers of other controlling areas to the ‘Not-Assigned’ node. Notice that this happens independent of the filters used in a query. Therefore, in a query result there is no difference if the query contains a filter on the same controlling area. But the performance will be much better if the filter is also defined in the Hierarchy Directory.

Examples

Example 1: Without @HIERARCHY.NOTASSIGNEDNODE

Example 2: With @HIERARCHY.NOTASSIGNEDNODE.SUPPRESSINDICATOR

Example 3: With @HIERARCHY.NOTASSIGNEDNODE.FILTER