Skip to Content
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

A simple parent child hierarchy represents relation between a single parent and multiple children. Source table shown below depicts the relation between employees and supervisors in a department. In this case, a single supervisor(E1) can be responsible for multiple employees(E2, E3). A hierarchial table represents the relationship between employee and supervisor in a tabular form with parent-child association.
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 structure

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

define view CDS_SOURCE_VIEW
  as select from hierarchy_tab
  association [1..*] to CDS_SOURCE_VIEW as _Parent
//child to parent association (Mandatory)
  on  $projection.supervisor = _Parent.employee
  and $projection.dep_supervisor = _Parent.dep_employee
 
{
   key employee,
   key dep_employee,
      
 _Parent // Make association public
 
 
 
}
define hierarchy CDS_HIERARCHY
  as parent child hierarchy(
    source SOURCE_CDS_VIEW
   child to parent association _Parent
    start where
      SOURCE_CDS_VIEW.cost_center = 100
    siblings order by
      SOURCE_CDS_VIEW.employee
    multiple parents not allowed
  )
{
   key employee,
   key dep_employee,
  _Parent
 
}
  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.

CDS hierarchy <CDS_HIERARCHY>  is defined as parent child hierarchy for base view <CDS_SOURCE_VIEW>. The additions in parentheses after AS PARENT CHILD HIERARCHY definethe hierarchy creation. The self-association _Parent is exposed as child to parent association in hierarchy definition.

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
  1. SAP HANA SQL reference with hierarchy functions
  2. SAP HANA Parent Child Hierarchies

Images & code snippets are self-made

/
4 Comments
You must be Logged on to comment or reply to a post.
  • Hierarchies help businesses to analyze their data in a tree structure through different layers with drilldown capability. … ABAP Core Data Services – Part 1 (ABAP CDS Entities). … ABAP CDS Development Guide … Hi Tushar, It’s great you are trying to do a consolidated blog series for beginners on this …
    • 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