Hierarchies in CDS views
Why do we need Hierarchies?
The main purpose of a hierarchy in analytics is to aggregate facts and provide a high-level overview with the option to drill into the lower hierarchy branches and nodes. Facts are represented as CDS analytical views, which are identified by the analytical data category CUBE provided as CDS view annotation.
A hierarchy is usually not defined on the facts themselves but on some master data column that categorizes the facts. This master data entity, or any business entity that should be arranged in a hierarchy, is given by a CDS view of analytical data category DIMENSION.
In general, a separate hierarchy view is needed, identified by data category HIERARCHY. Its rows represent the nodes of the hierarchy. The hierarchical relation of these nodes can be specified in two ways:
By two sets of columns, one identifying a node as child, often the key columns of the node, and the other one identifying the related parent node
By a self-association of the view with cardinality [1 : 0..1] pointing to the parent node of the node
Root nodes are those nodes which have no parent.
Usually, a business entity instance is assigned to just one node in a hierarchy. Depending on the capabilities of the analytical engine, it could be assigned to multiple hierarchy nodes, and the engine would consider this multiple assignment when aggregating facts via the hierarchy. The analytical engine in ABAP, for example, can handle multiple assignments to leaf nodes.
Note that a hierarchy node relates to just one business entity instance. This is different than in current set hierarchies, where a node can relate to many business entity instances, but allows for a unified treatment of all nodes of a hierarchy.
Often there is not just one possible hierarchy for a business entity but several alternatives. These should be represented as instances in a hierarchy directory from which a user can select the hierarchy he wants to see. The hierarchy directory is represented by a CDS view of data category DIMENSION. An entry of the hierarchy directory is used as grouping of hierarchy nodes. At runtime, only the nodes of a single hierarchy directory entry are considered. This reduces the number of processed nodes and improves performance. The connection between the hierarchy node view and the hierarchy directory is modeled with an association between these two views, where the on-condition must include all key elements of the hierarchy view.
Assume that the user has chosen a master data dimension for (hierarchical) drill-down and some measures from the fact view. Then he has to select one hierarchy from the hierarchy directory view (or the hierarchy might be pre-selected by the analytical query). Then the engine reads hierarchy nodes and structure from the hierarchy node view for the selected hierarchy (or uses cached information). Afterwards, the measures are aggregated via the fact view to those master data instances that are assigned to hierarchy nodes. Measures for unassigned master data instances can also be aggregated and shown at a virtual hierarchy node for unassigned values.
The analytical engine recursively aggregates these values along the hierarchy structure to higher level nodes
The hierarchically aggregated measures are displayed at all visible nodes.
The following check-list helps to express your hierarchy in CDS.
All hierarchy nodes must be represented as the rows of a CDS view, the node view.
- Hierarchy nodes are all nodes belonging to a hierarchy, including root nodes, inner nodes, and leaf nodes. For those readers familiar with set hierarchies: also each set element, e.g. each cost center, is considered as a separate hierarchy node.
- For each node, a single parent node is specified in the node view. Nodes without a parent are considered root nodes. A node cannot have more than one parent. Therefore, all hierarchies are strict. Each chain of parents must be acyclic, and therefore end at a root node.
- The complete set of nodes can be divided into several individual hierarchies. The list of hierarchies is given by a CDS view, the hierarchy directory for this node view. Each node belongs to exactly one hierarchy, identified by one or multiple key fields of the node view. A hierarchy can have multiple root nodes. Within a single hierarchy, multiple sub-hierarchies are available, given by a node and its descendents.
The below is your analytical view, which has a costs center Hierarchy.
Define a foreign key association to Hierarchy Directory and also Hierarchy Node Text View as below.
The Cost Center Master View should have the association to the Cost Center Hierarchy Node view. Also, Cost Center Field is annotated with @ObjectModel.Hierarchy.assocation indicating that a hierarchy is available for this master data entity.
Below is the Hierarchy Directory grouping all the available hierarchies.
Text view for Hierarchy Directory.
Text view for Hierarchy Node View.
Analysis for office
For system setup, Analysis for office setup please refer to the below blog post.