Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
Showing results for 
Search instead for 
Did you mean: 

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.

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(
child to parent association _Parent
start where
SOURCE_CDS_VIEW.cost_center = 100
siblings order by
multiple parents not allowed
key employee,
key dep_employee,


  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.


  • 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] )


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.

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.


  • 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:


This is the default setting (a child node can have precisely on parent node only).


Only leaf nodes can have multiple parent nodes.


All hierarchy nodes can have multiple parent nodes.


In next blog post, we will learn about various types of CDS hierarchies

Part 2Various 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