CDS : How to define Hierarchy View

LinaRaut · ‎2024 Jul 17

This blog is about how to define Hierarchy View in CDS.

There are two types of hierarchies – 'Leveled Hierarchy' and 'Parent-Child Hierarchy' In Analytics, only a Parent-Child hierarchy is supported.

In CDS, Hierarchy can be defined in two ways:

Inline Hierarchy: where the hierarchy definition is modelled in the dimension view.

External Hierarchy: where there is a separate CDS view to model the definition of the hierarchy.

In the past, External hierarchy was defined using the annotation '@ObjectModel.dataCategory: #HIERARCHY'. Now, in CDS it is allowed to define new type of CDS view called as Hierarchy CDS, which is defined using syntax define hierarchy, which create a hierarchical view.

A CDS hierarchy represents a HANA hierarchy, created when the CDS hierarchy is activated.

Normally the hierarchy contains headers and nodes. The header data of the hierarchy is coming from the directory view and the nodes data is coming from the hierarchy view itself.

Hierarchy Directory

The directory of the hierarchy provides the header information of the hierarchy. A hierarchy view might contain more than one hierarchy. Information about each hierarchy (e.g. text) is provided by a hierarchy directory. More information can be found in blog.

Typically, the directory view is defined as follows:

@EndUserText.label: 'Connection Hierarchy Directory View'
@Analytics : { dataCategory: #DIMENSION, dataExtraction.enabled: true }
@ObjectModel.representativeKey: 'hieid'

define view entity ZLR_CONNECTION_HIERARCHY_DIR
  as select from zoq_connid_hd
{
      @ObjectModel.text.element: [ 'hiertxt' ]
  key hieid,
      @Semantics.text: true
      hiertxt,
      @Semantics.systemDate.lastChangedAt: true
      last_changed,

}

Output

Hierarchy View using define hierarchy <NAME>

This is the new way of defining hierarchy and it is available from 2023 on Prem and 2308 Cloud. For defining hierarchy, prerequisite is having a source view, which is mentioned in the definition as parent child hierarchy source.

Source View

In view definition parent child association is define along with that following associations:

Association to hierarchy view itself- It defines parent child relationship
Association to hierarchy directory - It contains all the header information- hierarchy ids.
Association to different dimension/text views – It is a relation from the nodes and leaves to dimension and text views. Via this association the text of a node/leaf is derived. In the following example '_node' is first level node. '_airline' acts as second level node and '_connection' is leaf and pointing to dimension.
Extra conditions like 'carrid = "" ' are not necessary and the order of the associations is not relevant. Instead in this case a separate field is needed, which returns the fieldname of the records, which should be used. In the example it is field 'nodetype'

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Base for : New Way of defining Hierarchy'

define view entity ZLR_CONN_HIERARCHY_BASE
  as select from zoq_connid_h


  //association to itself.
  association [0..1] to ZLR_CONN_HIERARCHY_BASE      as _parent     on  $projection.hieid    = _parent.hieid
                                                                    and $projection.parentid = _parent.nodeid

  // association with hierarchy directory
  association [1]    to ZLR_CONNECTION_HIERARCHY_DIR as _dir        on  $projection.hieid = _dir.hieid


  // associations to the different node types
  // evalulated at runtime for each record in the order given here
  // first hit of ON-condition is followed
  // Differnce to old way of Hierarchy ZLR_CONNECTION_HIERARCHY - here no $projection.carrid   = ''  and $projection.connid   = '0000'
  association [1]    to ZOQ_CONNECTION_NODENAME      as _node       on  $projection.hieid    = _node.hieid
                                                                    and $projection.nodename = _node.nodeName
 

  // "nodatetype" Airline
  // Differnce to old way of Hierarchy ZLR_CONNECTION_HIERARCHY - here no  and $projection.connid = '0000'
  association [1]    to ZOQ_AIRLINE                  as _airline    on  $projection.carrid = _airline.carrid
 

  // association to dimension view
  association [1]    to ZLR_CONN                     as _connection on  $projection.carrid = _connection.carrid
                                                                    and $projection.connid = _connection.connid

{

      @Consumption.filter: { mandatory : true, selectionType : #SINGLE, multipleSelections : false }
      @ObjectModel.foreignKey.association: '_dir'
  key hieid,
  key nodeid,
      parentid,

      seqno,

      // three different node types possible in this example
      // here CONNID is leaf (can not be derived from this view)
      
      @ObjectModel.foreignKey.association: '_airline'
      carrid,
      @ObjectModel.foreignKey.association: '_connection'
      connid,
      @ObjectModel.foreignKey.association: '_node'
      nodename,

      cast(
      case when carrid is initial and connid is initial then 'NODENAME'
           when connid is initial                       then 'CARRID'
           else 'CONNID' end as fieldname ) as nodetype,
  


      // associations defined above have to be exposed here
      // otherwise they will not be available
      _parent,
      _dir,
      _node,
      _airline,
      _connection
}

Hierarchy View

Hierarchy view can be defined using the following syntax.

Syntax:

...
[DEFINE] HIERARCHY hierarchy
         [parameter_list]
         AS PARENT CHILD HIERARCHY(
           SOURCE cds_view
           CHILD TO PARENT ASSOCIATION _hierarchy_assoc
           [PERIOD FROM field1 TO field2 VALID FROM from TO to]
           [DIRECTORY _directory_assoc FILTER BY cds_cond]
           [START WHERE cds_cond]
           SIBLINGS ORDER BY field1 [ASCENDING|DESCENDING][,
                             field2 [ASCENDING|DESCENDING], ...]
           [DEPTH depth]
           [NODETYPE node_type]
           [LOAD BULK|INCREMENTAL|load_option]
           [MULTIPLE PARENTS {NOT ALLOWED}|LEAVES|ALLOWED]
           [ORPHANS IGNORE|ERROR|ROOT]
           [CYCLES ERROR|BREAKUP]
           [GENERATE SPANTREE]
           [CACHE ON|OFF|FORCE])
     { element_list }

In Syntax

SOURCE – Name of Source view. The mandatory addition SOURCE specifies a CDS view entity. This source must expose the hierarchy association specified after CHILD TO PARENT ASSOCIATION in its SELECT list.

source

ZLR_CONN_HIERARCHY_BASE

CHILD TO PARENT ASSOCIATION: The mandatory addition CHILD TO PARENT ASSOCIATION specifies the hierarchy association who’s ON condition selects the descendant nodes of the root node set.

child to parent association

_parent

PERIOD FROM field1 TO field2 VALID FROM from TO to: The optional addition PERIOD defines the hierarchy as a temporal SQL hierarchy in which the hierarchy nodes are limited by an adjustment of time intervals. In case of parameter, they can be different but value must be same when used in a query. period from datefrom to dateto valid from $parameters.p_date to $parameters.p_date

OR

period from datefrom to dateto valid from '20230425' to '20230425'

DIRECTORY: Hierarchy directory name. The optional addition DIRECTORY defines a filter condition cds_cond for the rows of the source of the hierarchy specified after SOURCE. It defines association to the header/ directory view. Filter can be defined on hierarchy directory, based on parameter. In case of time dependent hierarchy date parameters can be used to filter out the hierarchy.

_dir filter by hienm = $parameters.p_hienm1

and keydate = $parameters.p_keydate

START WHERE: The optional addition START WHERE specifies the start condition for creating the hierarchy. START WHERE can be followed by a logical expression cds_cond that selects rows from the source cds_view.

start where

parentid is initial

SIBLINGS ORDER BY field1 [ASCENDING|DESCENDING]: The mandatory addition SIBLINGS ORDER BY sorts sibling nodes in the hierarchy. Default is Ascending.

siblings order by

seqno

NODETYPE node_type: The NODETYPE identifies an element of the CDS hierarchy that can be used to determine the type of the hierarchy node. The values of this element must be element names, which must also be contained in the element list of the CDS hierarchy.

In context of Analytics, it is mandatory to define the nodetype field in the source view of the hierarchy and also in the hierarchy view using key word 'nodetype'

For example, in the Source view

cast(

case when carrid is initial and connid is initial then 'NODENAME'

when connid is initial then 'CARRID'

else 'CONNID' end as fieldname ) as nodetype,

In the Hierarchy view

nodetype

nodetype

More detailed information can be found here ABAP Keyword Documentation (sap.com).

Example of Hierarchy View

define hierarchy ZLR_CONN_HIERARCHY
  with parameters
    @Consumption.defaultValue: 'CNTRY'
    p_hieid : zoq_conhieid
  as parent child hierarchy(
    source ZLR_CONN_HIERARCHY_BASE
    child to parent association _parent
    directory _dir filter by
      hieid = $parameters.p_hieid
    start where
      parentid is initial
    siblings order by
      seqno
    nodetype nodetype
  )
{

      @ObjectModel.foreignKey.association: '_dir'
  key hieid,
  key nodeid,
      parentid,
      seqno,

      @ObjectModel.foreignKey.association: '_airline'
      carrid,
      @ObjectModel.foreignKey.association: '_connection'
      connid,
      @ObjectModel.foreignKey.association: '_node'
      nodename,

      nodetype, 
     
      // associations defined above have to be exposed here
      // otherwise they will not be available
      _parent,
      _dir,
      _node,
      _airline,
      _connection



}

Output

Red: association _node

Green: association _airline

Blue: association _connection, the blue marked records are leaves

Example of Query using Hierarchy

@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'ZLR_CONN - query'
@Metadata.ignorePropagatedAnnotations: true

define transient view entity ZLR_CONN_QUERY
provider contract analytical_query
as projection on ZLR_CONN
{
@AnalyticsDetails.query:{
axis:#ROWS,
displayHierarchy: #ON ,
hierarchyBinding: [{ type: #CONSTANT, value: 'CNTRY'  }]

}
  connid
    
}

Output

Hierarchy View Using @ObjectModel.dataCategory: #HIERARCHY Annotation (OLD way)

In View definition the annotation @Hierarchy.parentChild provides the information about the parent child hierarchy. It has hierarchy name, multiple parents, recursion by, ordering of siblings, root node visibility, orphaned node handling, etc. along with that, following associations need to be defined.

Association to hierarchy view itself - It defines parent child relationship.
Association to hierarchy directory view - It contains all the header information, hierarchy Ids and the descriptions.
Association to different dimension/text views – It is a relation from the nodes and leaves to dimension and text views. Via this association the text of a node/leaf is derived. In the following example '_node' is first level node, '_airline' acts as second level node and '_connection' is leaf and pointing to dimension.

Important is the order of the associations listed in the element-list and the 'extra-condition' in the ON-clause. For each record of the hierarchy the ON condition of the associations is checked - the first association which meets the ON condition is used to get text and attributes for the hierarchy node/leaf.

One association is mandatory - its target must be the dimension view which has a ObjectModel.hierarchy.association to the hierarchy view. These objects in the hierarchy represent the leaves.

Example

@EndUserText.label: 'Connection Hierarchy View'
@ObjectModel.dataCategory: #HIERARCHY
@Hierarchy.parentChild: [{  
name : 'ConnectionHierarchy',
  label : 'Connection',
  siblingsOrder: [{ by: 'seqno' }] ,
  recurseBy : '_parent',
  directory: '_dir'
  }] 
  
define view entity ZLR_CONNECTION_HIERARCHY
  as select from zoq_connid_h

  // basic parent child relation inside one hierarchy
  // hierarchy uniquely selected (see @Filter for field HIEID
  association [0..1] to ZLR_CONNECTION_HIERARCHY     as _parent     on  $projection.parentid = _parent.nodeid

  // necessary if view represents several hierarchies
  // association to the hierarchy directory
  association [1]    to ZLR_CONNECTION_HIERARCHY_DIR as _dir        on  $projection.hieid = _dir.hieid


  // associations to the different node types
  // evalulated at runtime for each record in the order given here
  // first hit of ON-condition is followed
  association [1]    to ZOQ_CONNECTION_NODENAME as _node       on       $projection.hieid   = _node.hieid
                                                                    and $projection.nodename = _node.nodeName
                                                                    and $projection.carrid   = ''
                                                                    and $projection.connid   = '0000'

  // "nodatetype" Airline
  association [1]    to ZOQ_AIRLINE                  as _airline    on  $projection.carrid = _airline.carrid
                                                                    and $projection.connid = '0000'

  // For which dimension this hierarchy belongs
  association [1]    to ZLR_CONNECTION               as _connection on  $projection.carrid = _connection.carrid
                                                                 and $projection.connid = _connection.connid

{
      @Consumption.filter: { mandatory : true, selectionType : #SINGLE, multipleSelections : false }
      @ObjectModel.foreignKey.association: '_dir'
  key hieid,
  key nodeid,
      parentid,

      seqno,

      // three different node types possible in this example
      // here CONNID is leaf (can not be derived from this view)
      
      @ObjectModel.foreignKey.association: '_airline'
      carrid,
      @ObjectModel.foreignKey.association: '_connection'
      connid,
      @ObjectModel.foreignKey.association: '_node'
      nodename,
      
      // associations defined above have to be exposed here
      // otherwise they will not be available
      _parent,
      _dir,
      _node,
      _airline,
      _connection

}

Output

Red: association _node

Green: association _airline

Blue: association _connection, the blue marked records are leaves

Reference

More information on @Semantics.signReversalIndicator can be found in the blog.

CDS : How to define Hierarchy View

Get Your SAP HANA Idea Incubator Badge Today!

SCN Mission - SAP HANA Quiz Challenge is now retired

Share your #HANAStory and Win