CDS Hierarchy Directory: How to use NOTASSIGNEDNODE Annotation to suppress or to filter the Not Assigned(REST_H) nodes

[LinaRaut]

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

@EndUserText.label: 'Connection Hierarchy Directory View'

@analytics : { dataCategory: #DIMENSION, dataExtraction.enabled: true }

@ObjectModel.representativeKey: 'hieid'

define view entity ZOQ_CONNECTION_HIERARCHY_DIR as select from ZOQ_CONNID_HD

{

@ObjectModel.text.element: 'hiertxt'

key hieid,

@Semantics.text: true

hiertxt,

@Semantics.systemDate.lastChangedAt: true

last_changed

}

Resultset will display Not assigned (REST_H) nodes

 

Example 2: With @HIERARCHY.NOTASSIGNEDNODE.SUPPRESSINDICATOR

@EndUserText.label: 'Connection Hierarchy Directory View'

@analytics : { dataCategory: #DIMENSION, dataExtraction.enabled: true }

@ObjectModel.representativeKey: 'hieid'

define view entity ZLR_CONNECTION_HIERARCHY_DIR as select from zoq_connid_hd

{

@ObjectModel.text.element: 'hiertxt'

key hieid,

@Semantics.text: true

hiertxt,

@Semantics.systemDate.lastChangedAt: true

last_changed,

@Hierarchy.notAssignedNode.suppressIndicator: true

'X' as suppressNotAssigned

}

Resultset will not display Not Assigned (REST_H) nodes

 

Example 3: With @HIERARCHY.NOTASSIGNEDNODE.FILTER

@EndUserText.label: 'Connection Hierarchy Directory View'

@analytics : { dataCategory: #DIMENSION, dataExtraction.enabled: true }

@ObjectModel.representativeKey: 'hieid'

define view entity ZLR_CONNECTION_HIERARCHY_DIR as select from zoq_connid_hd

association to ZOQ_AIRLINE as _carrid on _carrid.carrid = $projection.carrid

{

@ObjectModel.text.element: 'hiertxt'

key hieid,

@Semantics.text: true

hiertxt,

@Semantics.systemDate.lastChangedAt: true

last_changed,

@ObjectModel.foreignKey.association: '_carrid'

@Hierarchy.notAssignedNode.filter: true

cast( 'LH' as s_carr_id ) as carrid,

_carrid

}

Resultset will display Not Assigned(REST_H) node but only with LH as we have provided filter on carrid LH

 

Following are some of correction which are done for this feature:

3348310 - Problem with Hierarchy.notAssignedNode.filter
3377570 - Dump: GETWA_NOT_ASSIGNED in class CL_RSR_RRK0_HIERARCHY