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.

Simple parent-child hierarchy

 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.

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
 
}

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.

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

1. SAP HANA SQL reference with hierarchy functions
2. SAP HANA Parent Child Hierarchies

Images & code snippets are self-made