Hierarchical structures help users survey and process large amounts of data. In analytical applications, measures can be aggregated at hierarchy nodes and selectively expanded to greater detail.
Hierarchies in CDS
CDS offers two methods for modeling and processing of hierarchies.
Annotation-Based Hierarchies
Annotation-based hierarchies can be defined with a standard CDS view or entity and have been available since the introduction of CDS technology. However, because they need an analytic processor (analytic engine), they are mainly used in analytical applications.
CDS Hierarchies
CDS hierarchies were first introduced in SAP S/4HANA release 1809 as a special new type of CDS entity, exclusively for that purpose. They are defined by a specific hierarchy syntax, which is aligned with native SAP HANA hierarchies. CDS hierarchies are integrated with ABAP SQL, which offers several new hierarchy functions that are executed, like the hierarchy selects, by the SAP HANA hierarchy engine.
Hierarchy Categories and Basics
In leveled hierarchies, every hierarchy level needs a separate data field. A simple example is the two-level hierarchy of countries and regions, which is represented by view I_Region with key fields Country and Region for the levels. As explicit fields are available for every hierarchy level, standard SQL requests are sufficient for executing typical hierarchy requests, such as aggregating measures, that are defined on the lowest level, to higher levels of the hierarchy. You can, for example, determine all regions of a country via a simple SQL request and aggregate their values. Special hierarchy functions aren’t necessary. Therefore, we’ll focus on a different type of hierarchy, the parent-child hierarchy.
A parent-child hierarchy consists of a set of hierarchy nodes and a set of parent relations from a child node to a parent node. This figure shows an example.
The top node in the hierarchy example is a root node; it doesn’t have its own parent. There can be more than one root node in a hierarchy. Nodes without their own child nodes are called leaf nodes of the hierarchy. The descendants of a node are its children, its grandchildren and so on, excluding the node itself.
Parent-child hierarchies can have two exceptional properties:
- Nodes with more than one parent
- Nodes that form a cycle
In a cycle, you can start from a node, go to its parent, repeatedly continue to the next parent, and reach the starting node after some steps.
Hierarchies without these exceptional properties are called strict hierarchies. Strict hierarchies are simpler to process and can be displayed easier in user interfaces (UIs).
In a strict hierarchy, every node has exactly one root node. This can be reached by traversing along the parent relation. The number of steps to the root node is called distance from root. You get the level of the hierarchy node by adding 1 to the number of steps. A root node’s distance from root is 0, and its level is 1.
The relational data model for a parent-child hierarchy is based on an entity for the nodes and a parent relation from the entity to itself. Every data row has fields to uniquely identify a child node and to uniquely identify a parent node. In CDS, a hierarchy node entity can be modeled by a CDS view or a CDS entity, and the parent relation can be modeled by a CDS association.
In business reality, the important entities, such as employees or cost centers, can be found in many different hierarchical arrangements, often combined with other entities of the business world. Therefore, it’s common practice to not include business attributes in the hierarchy node entities but have a relation (association) to a separate entity that represents the employee or the cost center. Entities with different business semantics can be combined in a hierarchy that way.
This separation also enables the definition of multiple alternative hierarchical structures for the same entities, for example, planning variants or historical versions. The same cost center can be associated from multiple hierarchy variants or versions. Usually, a separate entity, a hierarchy directory, is used to maintain these alternative hierarchical structures.
Parent-child hierarchies have an important advantage over leveled hierarchies: the number of hierarchy levels is independent from the number of available fields to represent a level and, theoretically, unlimited. This capability, however, requires programming loops or recursive processing of the hierarchy. Unfortunately, this isn’t possible with standard requests in SQL. For using parent-child hierarchies, additional support by a hierarchy engine is necessary. For analytic applications, the hierarchy engine of the analytic engine has been available since CDS was introduced. It’s used for annotation-based hierarchies.
Editor’s note: This post has been adapted from a section of the book Core Data Services for ABAP by Renzo Colle, Ralf Dentzer, and Jan Hrastnik.
Comments