Beginners guide for CDS Hierarchies - Part 1

harishbokkasam1 · ‎11-23-2020

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

Links

SAP HANA SQL reference with hierarchy functions

SAP HANA Parent Child Hierarchies

Images & code snippets are self-made