This blog post is continuation to Beginners guide for CDS Hierarchies – Part 1
Part 2- In this blog post, I will be going to provide more information on types of hierarchies available with ABAP CDS views.
Source table for multiple parent child hierarchy
In a multiple parent child hierarchy, a child can have multiple parents. employee E1 has multiple parents E3 and E4. Employee E2 has multiple parents E5 and E6. If no start where condition is specified, then hierarchy table consists of only child node E12. In case of absent start where condition, the child node without parent is considered as root node. All other nodes which are not linked in hierarchy to root node are ignored as orphans (default condition orphans ignore).
The optional addition ORPHANS define the way orphan nodes are handled. The following categories of orphan nodes exist:Orphans
The additions work as follows:
Orphan nodes are inserted in the hierarchy as follows:
Effect
The addition CYCLES define how node cycles are defined. The additions work as follows:
This is the default setting (an exception is raised when a node cycle is detected).
The traversal of descendant nodes is broken at the child node where the node cycle occurs and the hierarchy attribute HIERARCHY_IS_CYCLE is set to the value 1.
If the addition BREAKUP is specified, MULTIPLE PARENTS ALLOWED must also be specified.
Employees E1 to E 10 belong to one department F1 and employees E11 to E13 belong to another department F2
The optional addition DIRECTORY in a CDS hierarchy defines a filter condition for the rows of the source of the hierarchy specified after BASE_CDS_VIEW. The hierarchy is generated only from those rows in the source that meet the filter condition. Comparisons for equality can be specified for conditional expressions using =:
The same rules apply to the comparable types as to CDS views.
If start where = 'E11' is not included, then the information of directory with company F2 and country US is not shown. The first root node in hierarchy employee E1 is considered as default root. This results in child nodes found for root node which fit the cds view parameters.
Note:
Graph(not well-formed tree) Spanning tree
When the start (E12) and result nodes (E11) in a tree are not connected through shortest path and the tree is not well-formed, it is recommended to generate a spanning tree. Spantree generates a hierarchy between parent-child using only the shortest paths between start and result nodes.
Considerations for spantree generation
Spantree generation in CDS hierarchy
Hierarchy attributes
Next Blog Post :
Part 3 - ABAP SQL in CDS hierarchies, ADT tools for hierarchies, Temporal hierachies and tips
Images: self made
Part 2- In this blog post, I will be going to provide more information on types of hierarchies available with ABAP CDS views.
- Multiple parent-child hierarchy
- CDS hierarchies with parameters
- Generate spantree with CDS hierarchy
- Load Clause in transversal configurations
| CLIENT | EMPLOYEE | SUPERVISOR | DEP_EMPLOYEE | DEP_SUPERVISOR | LOETYPE | ORD | SALARY | COST_CENTER |
| 0 | E1 | E3 | 0 | 1 | 1 | 150 | 99 | |
| 0 | E1 | E4 | 1 | 1 | Y | 1 | 120 | 99 |
| 0 | E2 | E5 | 1 | 1 | Z | 2 | 90 | 99 |
| 0 | E2 | E6 | 1 | 1 | X | 1 | 40 | 99 |
| 0 | E7 | E9 | 1 | 1 | Y | 2 | 60 | 99 |
| 0 | E8 | E9 | 1 | 1 | Z | 3 | 75 | 99 |
| 0 | E10 | E1 | 1 | 1 | x | 1 | 40 | 99 |
| 0 | E11 | E2 | 1 | 1 | y | 1 | 30 | 99 |
| 0 | E12 | 1 | 1 | z | 1 | 90 | 99 | |
| E9 | E13 |
Source table for multiple parent child hierarchy
In a multiple parent child hierarchy, a child can have multiple parents. employee E1 has multiple parents E3 and E4. Employee E2 has multiple parents E5 and E6. If no start where condition is specified, then hierarchy table consists of only child node E12. In case of absent start where condition, the child node without parent is considered as root node. All other nodes which are not linked in hierarchy to root node are ignored as orphans (default condition orphans ignore).
Multiple parents hierarchy
The optional addition ORPHANS define the way orphan nodes are handled. The following categories of orphan nodes exist:Orphans
- Hierarchy nodes that could have parent nodes (as specified by the parent-child relationship) but the parent nodes are not in the hierarchy (known as true orphans).
- Hierarchy nodes that cannot be reached from the root node set using hierarchy edges.
- Hierarchy nodes that are part of a node cycle and cannot be reached from the root node set using hierarchy nodes (known as island orphans).
The additions work as follows:
- IGNORE
This is the default setting (any orphan nodes are not inserted in the hierarchy). - ERROR
Any orphan nodes detected raise an exception. - ROOT
Orphan nodes are inserted in the hierarchy as follows:
- True orphans are included in the root node set as root nodes and flagged as orphan nodes in the hierarchy attribute HIERARCHY_IS_ORPHAN.
- Descendant nodes of true orphans are handled like descendant nodes of parent nodes from the root node set but are also flagged as orphan nodes in the hierarchy attribute HIERARCHY_IS_ORPHAN.
- For the hierarchy nodes of island orphans, a parent node in the root node set is generated for the child node where the cycle occurs. In the generated root node, all columns of the source hierarchy source contain the null value. In the hierarchy attributes, the additional root node is flagged as an orphan node and PARENT_ID also contains the null value.
- Cycles
Effect
The addition CYCLES define how node cycles are defined. The additions work as follows:
- ERROR
This is the default setting (an exception is raised when a node cycle is detected).
- BREAKUP
The traversal of descendant nodes is broken at the child node where the node cycle occurs and the hierarchy attribute HIERARCHY_IS_CYCLE is set to the value 1.
If the addition BREAKUP is specified, MULTIPLE PARENTS ALLOWED must also be specified.
CDS Hierarchies with parameters
- This feature is used when multiple hierarchies are present in a same source table/view
- Directory works as an additional filter (where clause) based on the fields of an association mentioned in the directory clause
| CLIENT | EMPLOYEE | DEP_EMPLOYEE | DIR_KEY_1 | DIR_KEY_2 | SUPERVISOR | DEP_SUPERVISOR | LOETYPE | ORD | SALARY | COST_CENTER |
| 0 | E1 | 1 | F1 | DE | 0 | X | 1 | 150 | 99 | |
| 0 | E2 | 1 | F1 | DE | E1 | 1 | Y | 1 | 120 | 99 |
| 0 | E3 | 1 | F1 | DE | E1 | 1 | Z | 2 | 90 | 99 |
| 0 | E4 | 1 | F1 | DE | E2 | 1 | Y | 2 | 60 | 99 |
| 0 | E5 | 1 | F1 | DE | E2 | 1 | Y | 2 | 40 | 99 |
| 0 | E6 | 1 | F1 | DE | E3 | 1 | Y | 3 | 75 | 99 |
| 0 | E7 | 1 | F1 | DE | E3 | 1 | Y | 4 | 30 | 99 |
| 0 | E8 | 1 | F1 | DE | E6 | 1 | Y | 1 | 30 | 99 |
| 0 | E9 | 1 | F1 | DE | E6 | 1 | Z | 2 | 25 | 99 |
| 0 | E10 | 1 | F1 | DE | E7 | 1 | X | 3 | 30 | 99 |
| 0 | E11 | 2 | F2 | US | 2 | Y | 2 | 80 | 100 | |
| 0 | E12 | 2 | F2 | US | E11 | 2 | Z | 2 | 45 | 100 |
| 0 | E13 | 2 | F2 | US | E11 | 2 | X | 5 | 30 | 100 |
- Multiple directories/departments in same table view
Employees E1 to E 10 belong to one department F1 and employees E11 to E13 belong to another department F2
Hierarchies with directories
The optional addition DIRECTORY in a CDS hierarchy defines a filter condition for the rows of the source of the hierarchy specified after BASE_CDS_VIEW. The hierarchy is generated only from those rows in the source that meet the filter condition. Comparisons for equality can be specified for conditional expressions using =:
- The operator on the left side of a comparison must be an element of the current hierarchy (dir_key1, dir_key2) whose name occurs as an operand in the BASE_CDS_VIEW on the left side of an ON condition of an association _DirAssoc.
- The operator on the right side of the comparison can be a simple literal or a type-friendly parameter from the parameter list of the hierarchy.
The same rules apply to the comparable types as to CDS views.
If start where = 'E11' is not included, then the information of directory with company F2 and country US is not shown. The first root node in hierarchy employee E1 is considered as default root. This results in child nodes found for root node which fit the cds view parameters.
Note:
- A filter condition removes all hierarchy nodes and their descendant nodes from the results set that do not match the condition specified in association.
- The content of the target data source of the association _directory_assoc is ignored when the filter condition is evaluated.
- The restriction of the operands on the left side of a comparison to the operands of an association of the source is supported by certain programming models. Any frameworks based on these models read data from the target data source of this association and pass it to input parameters of the hierarchy that evaluates them in the filter condition.
Generate spantree with CDS hierarchy
- Generates a hierarchy on recursive parent-child source containing only shortest paths between start and result nodes.
- If a graph that is not a well-formed tree, a spanning tree/forest will be computed, starting from the root nodes and traversing the source graph in breadth-first order.
- The value of the HIERARCHY_LEVEL attribute is equal to the distance from the root node +1, it corresponds to the shortest path in the source graph from a root to the node.
Graph(not well-formed tree) Spanning tree
When the start (E12) and result nodes (E11) in a tree are not connected through shortest path and the tree is not well-formed, it is recommended to generate a spanning tree. Spantree generates a hierarchy between parent-child using only the shortest paths between start and result nodes.
Considerations for spantree generation
- multiparent", "cycles" and "orphan" specifications are not supported with SPANTREE function
- Technical fields returned by this function is the same as standard hierarchy function
Spantree generation in CDS hierarchy
| employee | supervisor | loetype | ord | salary | cost_center | my_node_id | my_hierarchy_rank | my_hierarchy_parent_rank | my_hierarchy_level | Leaf |
| E1 | x | 1 | 300 | 99 | E1 | 1 | 0 | 1 | 11 | |
| E2 | E1 | x | 1 | 300 | 99 | E2 | 2 | 1 | 2 | 3 |
| E5 | E2 | x | 1 | 300 | 99 | E5 | 3 | 2 | 3 | 1 |
| E6 | E2 | x | 1 | 300 | 99 | E6 | 4 | 2 | 3 | 1 |
| E3 | E1 | x | 1 | 300 | 99 | E3 | 5 | 1 | 2 | 6 |
| E7 | E3 | x | 1 | 300 | 99 | E7 | 6 | 5 | 3 | 3 |
| E10 | E7 | x | 1 | 300 | 99 | E10 | 7 | 6 | 4 | 1 |
| E9 | E7 | x | 1 | 300 | 99 | E9 | 8 | 6 | 4 | 1 |
| E8 | E3 | x | 1 | 300 | 99 | E8 | 9 | 5 | 3 | 2 |
| E11 | E8 | x | 1 | 300 | 99 | E11 | 10 | 9 | 4 | 1 |
| E4 | E1 | x | 1 | 300 | 99 | E4 | 11 | 1 | 2 | 1 |
Hierarchy attributes
Next Blog Post :
Part 3 - ABAP SQL in CDS hierarchies, ADT tools for hierarchies, Temporal hierachies and tips
Images: self made
- SAP Managed Tags:
8 Comments
