Hierarchies: How to Assign a Hierarchy to a Dimension and How to Consume a Hierarchy in an Analytical Query

CDS offers the possibility to structure hierarchical data. For instance, if you have employee data and would like to organize this data based on their manager, you can structure the data hierarchically. You are then able to query data of employees under each manager (sales, for instance). The general structure of a CDS hierarchy is depicted below:

Example structure of CDS hierarchies

Associations are labeled and are denoted by dotted lines, while selects are represented by the solid lines between the entities.

HierarchyTable: the hierarchically structured data is stored on this table. If, for instance, you are structuring employee data, this database table would contain employee IDs as well as their parent (or child) ID.

HierarchySource: This CDS view entity is the data source for the CDS hierarchy and contains a self-association for the parent-child relationship. This view also contains an association to the hierarchy. This again selects from an underlying data source in which the hierarchy node IDs and their parent node IDs are defined.

HierarchyDirectory: this view is optional. It contains directory information that is used to filter the result set of the hierarchy.

Hierarchy: the result of this entity are hierarchy nodes that are selected from the underlying source together, filtered with the directory.

If you have data that can be structured hierarchically and would like to know how you can add a hierarchy to your data model, this blog post will explain how you go about doing so.

There are several different scenarios that you may want to implement hierarchies within. I will explain how to add a hierarchy to a custom dimension in your analytical data model, but if you are adding to your own data model, which hasn’t been released by SAP, you can follow this guide as well. In that case, however, instead of adding extend views, you would just add the data to your model directly within the views and database tables.

If you want to add a hierarchy to a dimension of your cube, you can do so with an extension. First, make sure that the model is extensible. This means that your database table or structure, your analytical cube, and your CDS projection view (query) should all be enabled for developer extensibility.

Once you are sure that your data model is extensible, you can begin to add your hierarchically structured data to your data model.

There are a few different components that are required for a hierarchy to function properly. In the following steps, we will go through the steps to create a custom hierarchy to be added to the custom dimension.

1. First, create a custom database table which contains the hierarchical relationships of your dimension instances. A few structural requirements are necessary for this:

• The first key field should uniquely identify the custom hierarchy directory, as multiple hierarchical structures can be supported simultaneously.
• The second key field should identify the custom dimension instance uniquely in the custom hierarchy dimension. This does not need to be the key of your dimension.
• Subsequent fields should model the relationships to parent instance(s), as well as foreign keys for the custom dimension, as well as further relevant dimensions.

2. Create a custom directory CDS View, which returns relevant directories and filters out others.

3. Create a hierarchy node view entity, which defines the single nodes of the custom hierarchy. The node view entity should include the following:

• Association to custom directory CDS view entity
• Self-association to corresponding parent instance(s)
• Exposure of all relevant foreign keys

4. Creation of the custom CDS hierarchy, which should include:

• Use of the directory association, which links the hierarchy to the hierarchy directory
• Use of the parent association, which links the nodes of the hierarchy to their parents, and is part of the structure of the hierarchy
• Definition of a start condition
• Siblings order-by clause

The following steps describe how to do so:

1. First, create a custom database table containing the data to be used in the custom dimension. The data included in this database table will be propagated to the query.
2. Next, create a custom analytical dimension CDS view entity that exposes the data of the custom database table. Your dimension entity should include an association to the custom hierarchy, and this association should be exposed with the annotation ‘@ObjectModel.association.toHierarchy: true’.
3. You will then need to create a DDIC append on the extensible database table/structure, which stores your dimension foreign key per instance.
4. Create a CDS entity extension on the CDS entity extension include view, which exposes the additional fields directly from the database table.
5. Create a CDS entity extension on the analytical cube CDS view entity. This entity extension should include the following functionality:
• expose the required fields from the extension include view association
• define an association for the custom analytical dimension CDS view
• expose further required dimension fields relevant for query processing via the custom association
6. Create a CDS entity extension on the analytical CDS projection view (query), which exposes the required fields from the analytical cube view entity. Your query should include the annotation ‘@Consumption.filter: { hierarchyAssociation: ‘[hierarchyName]’ selectionType: #HIERARCHY_NODE }, where ‘hierarchyName’ is the name of your hierarchy.
• The included annotation binds the hierarchy to the query

Once these steps have been completed, you have a custom dimension, to which you can add your custom hierarchy. The next part will describe how to add your custom hierarchy to this newly created dimension.