Technical Articles
Beginners guide for CDS Hierarchies – Part 1
Introduction to hierarchies
Hierarchical data is defined as data sets combined with each other via hierarchical relation. Hierarchical relations exist when one data item is positioned as parent of another item.
In an organisation having employees, hierarchial data is a list of employees(entities) which are related to each other. Hierarchy enables structural organisation of data with each employee linked to other employees in the hiearchical table.
Two types of hierarchy are available:
- A level-based hierarchy where the members of a dimension are organized into levels, such as Country, State, and City
- A parent-child hierarchy where the members of a dimension iare organized into a set of parent-child relationships.
Simple parent-child hierarchy
CLIENT | EMPLOYEE | SUPERVISOR | DEP_EMPLOYEE | DEP_SUPERVISOR | LOETYPE | ORD | SALARY | COST_CENTER |
0 | E3 | E1 | 1 | 1 | Z | 2 | 90 | 99 |
0 | E4 | E2 | 1 | 1 | X | 1 | 40 | 99 |
0 | E5 | E2 | 1 | 1 | Y | 2 | 60 | 99 |
0 | E6 | E3 | 1 | 1 | Z | 3 | 75 | 99 |
0 | E7 | E3 | 1 | 1 | X | 4 | 30 | 99 |
0 | E1 | 1 | 0 | X | 1 | 150 | 99 | |
0 | E2 | E1 | 1 | 1 | Y | 1 | 120 | 99 |
0 | E8 | E6 | 1 | 1 | Y | 1 | 25 | 99 |
0 | E9 | E6 | 1 | 1 | Z | 2 | 25 | 99 |
0 | E10 | E7 | 1 | 1 | Y | 3 | 30 | 99 |
Relation between employee-supervisor
Hierarchial tables are not directly generated from hierarchial data. A hierarchy should be defined and implemented to generate hierarchial structure in a tabular form. CDS hierarchies provides the means to define and implement hierarchies via CDS views.
ABAP CDS Hierarchy
defines a CDS entity as a hierarchy in the CDS DDL. A CDS hierarchy has a tabular result set whose rows construct parent-child relationships. When a CDS hierarchy is accessed as the data source of an ABAP SQL query, it is handled like a hierarchy in which additional hierarchy columns can be selected. Building hierarchies based on hierarchical functions is already available in HANA. With ABAP CDS hierarchies, it is now possible to build hierarchies in ABAP CDS views.
Considerations for defining CDS hierarchies
- are DDLS objects, such as views, abstract entities, table functions
- do not have an SQL view (SE11 view) as internal object
- support exactly one name for DDL, CDS entity and database object store cds meta data and in addition (new persistency)
- meta data contain all information required for creation of HANA DDL statement
- are only supported on HANA and require a CDS view as base object with a certain self-association
Steps to implement parent-child hierarchy from a source table
|
|
BASE view for CDS hierarchy | CDS hierarchy |
source <CDS_Source_View> ( Mandatory) on which the hierarchy is based.
The mandatory addition SOURCE specifies a CDS view as a source of the CDS hierarchy. This source must publish the hierarchy association specified after CHILD TO PARENT ASSOCIATION in its SELECT list.
Note:
- only CDS entities that can be specified as the source of a CDS hierarchy. More specifically, a CDS hierarchy cannot be the source of another CDS hierarchy.
- child to parent association <Assoc> ( Mandatory) defines the child and parent field names. The data type of left and right side of field in the association must be identical
- In CDS_SOURCE_VIEW the association _Parent defines the child (hierarchy_tab) to parent (CDS_SOURCE_VIEW) through self-association. Association characterizes the cardinality of hierarchy ( multi parent [1..*], single parent [1..1] )
Additions
siblings order by ( Mandatory)
- defines the sort order of sibling nodes ( works with HANA SP03)
- for <CDS_HIERARCHY> sort order of sibling nodes is based on entries in employee row of hierarchy result set.
CLIENT | EMPLOYEE | SUPERVISOR | DEP_EMPLOYEE | DEP_SUPERVISOR | LOETYPE | ORD | SALARY | COST_CENTER |
0 | E1 | 0 | X | 1 | 150 | 99 | ||
0 | E2 | E1 | 1 | 1 | Y | 1 | 120 | 99 |
0 | E3 | E1 | 1 | 1 | Z | 2 | 90 | 99 |
0 | E4 | E2 | 1 | 1 | X | 1 | 40 | 99 |
0 | E5 | E2 | 1 | 1 | Y | 2 | 60 | 99 |
0 | E6 | E3 | 1 | 1 | Z | 3 | 75 | 99 |
0 | E7 | E3 | 1 | 1 | X | 4 | 30 | 99 |
0 | E8 | E6 | 1 | 1 | Y | 1 | 25 | 99 |
0 | E9 | E6 | 1 | 1 | Z | 2 | 25 | 99 |
0 | E10 | E7 | 1 | 1 | Y | 3 | 30 | 99 |
Sorted result list of CDS_HIERARCHY
start where <condition> (Optional)
- specifies the start condition to identify the root nodes
The selected rows are inserted in the hierarchy as a root node set. For each root node in the root node set, the descendant nodes are selected that meet the ON condition of the hierarchy association and, if possible, inserted in the hierarchy.
Note:
- The start condition should select a feasible set of root nodes. If no rows in the results set of the source cds_view meet the condition, the hierarchy is empty. If all rows meet the condition, the descendant nodes of every row are selected and inserted
- Technical fields are accessed via prefix $node ($node.node_id, $node.parent_id, $node.hierarchy_rank).
Multiple parents
The optional addition MULTIPLE PARENTS specifies whether the hierarchy can have child nodes with multiple parent nodes:
- NOT ALLOWED
This is the default setting (a child node can have precisely on parent node only).
- LEAVES ONLY
Only leaf nodes can have multiple parent nodes.
- ALLOWED
All hierarchy nodes can have multiple parent nodes.
In next blog post, we will learn about various types of CDS hierarchies
Part 2 – Various types of CDS hierarchies
- multiple parent-child hierarchy
- CDS hierarchies with parameters
- Temporal hierarchy
Links
Images & code snippets are self-made
Hi Aliza,
I've removed a link that you had added to your comment. Please review our SAP Community rules of engagement: https://community.sap.com/resources/rules-of-engagement
Best regards
SAP Community Moderator
Hi Harish! Thanks for sharing. I wish there was a more clear explanation of the context in which hierarchies would be used. I think everyone understands what a hierarchy is but, to be honest, just from reading this blog, I didn't quite get why this is needed in CDS view context and what it's for exactly.
There is an older blog that I found in Google that I think explains this better: https://blogs.sap.com/2016/11/25/hierarchies-in-cds-views/
Hello Jelena,
Here, the idea was to inform specifically about CDS hierarchies in ABAP.
I will link the blog you sent for more information.
Thanks a lot,
Greetings,
Harish B
Hi Harish,
Thank you for the blog.
I have a requirement where I need the hierarchical CDS view by Sales org , division and distribution channel. May I know how can we build the hierarchy with different fields. I found the examples only with recursive, but not individual.
Can you please give some suggestions?
Thank you,
Kishore
Hi Harish,
Thanks for sharing the valuable info.
Can you please let me know from which release and SP onwards this CDS hierarchy feature is supported?,
Regards,
Avinash