How to build a Custom Hierarchy in ABAP CDS views

I was interested in standard analytics capabilities of HCM in S/4HANA (1511) and Orgunit Hierarchy realization in ABAP CDS views.

I. Find a standard HR hierarchy

I started with Query Browser Fiori App and wanted to find standard analytical queries by HCM components like HR-PA, HR-PD, HR-PT, HR-PY etc. But there was not one unfortunately.

After this I went to ABAP dictionary to find where main HR tables, like HRP1000, HRP1001, PA0001 are used. It is possible to find DDL Sources.
At the picture you could see a result for HRP1001, there is a private CDS view for table of relationships between HR Objects (PIQCDSHRP1001)

After this I found a packages with ABAP CDS definitions of HCM Area:

There are not so many ABAP CDS views. You could see private, interface and consumption prefics of names:

But I didn’t find Orgunit Hierarchy in some of there views. That’s why we have to build a new one.

Important: I am sure that at some future releases of S/4HANA there will be (or already exist in 1610) a standard approach, but as this is my case, I am using this like an opportunity of building custom hierarchy ABAP CDS View.

II. Create custom hierarchy CDS view

1. Create a hierarchy view Zi_Orgunit_H

Notes:

• I’m using some standard private CDS views on HRP1000 and HRP1001 tables.
• CDS view Zi_Orgunit_Dim will be showed later. It’s our dimention CDS view. I will open it in Analysis for Excel to demonstrate a result.
• This is a simple example on test data and I am not care about time-dependency of hierarchy structure, but in real case it of cause shouldbe done. You could use P_TODAYDATE or create a variable for key date different that today.
• Take care about correct definition of hirarchy semation. If you don’t use hierarchy directory @Hierarchy.ParentChild.name is mandatory.

@VDM.viewType: #BASIC

@ObjectModel: { dataCategory: #HIERARCHY }

@AbapCatalog.sqlViewName: 'ZIORGUNITH'

@Hierarchy.parentChild.name: 'ORGEH_01'

@Hierarchy.parentChild.label: 'Orgunit hierarchy 01'

@Hierarchy.parentChild: 

{ recurse:          {   parent: 'ParentNode',   child:  'HierarchyNode'   },

  siblingsOrder:    {   by: 'HierarchyNode',    direction: 'ASC'   },

  orphanedNode.handling: #ROOT_NODES,

 rootNode.visibility: #DO_NOT_ADD_ROOT_NODE

  }

@AccessControl.authorizationCheck: #NOT_ALLOWED  

define view Zi_Orgunit_H       

     

 as select from P_PDOBJECT as object 

 inner join P_PDRELATION as relation  on

   relation.PlanVersion       =  object.PlanVersion  and   

   relation.ObjectType        =  object.ObjectType   and

   relation.ObjectID          =  object.ObjectID     and

   relation.RelationStatus    =  object.ObjectStatus  

 association[0..*] to Zi_Orgunit_Dim as _Zi_Orgunit_Dim on $projection.OrgUnit = _Zi_Orgunit_Dim.Orgunit

{       

    key object.ObjectID as HierarchyNode,

    relation.EndDate as RelationEndDate,

    relation.StartDate as RelationStartDate,

    relation.RelatedObjectID  as ParentNode,

    @ObjectModel.foreignKey.association: '_Zi_Orgunit_Dim'

    relation.ObjectID as OrgUnit,

    

    _Zi_Orgunit_Dim

} where object.PlanVersion = '01' and

        relation.ObjectType             = 'O' and

        relation.RelationShipDirection  = 'A' and

        relation.ReleationShip          = '002' and

        relation.RelatedObjectType      = 'O'

Data preview in HANA Studio:

2. Create a dimension view Zi_Orgunit_Dim

Notes:

• In this CDS view we create an association to hierarchy CDS, in hierarchy CDS an association to dimention CDS is also needed.
• For texts to be showen in Analysis we could use a standard CDS view I_OrgUnitText, but in this view dataCategory is missing (#TEXT needed to be defined), that’s why @ObjectModel.text.assosiation is not possible. But we could always use @ObjectModel.text.element annotation instead.

@AbapCatalog.sqlViewName: 'ZIORGUNITD'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'ZI_ORGUNIT_DIM'

@VDM.viewType: #BASIC

@Analytics: { dataCategory: #DIMENSION, dataExtraction.enabled: true }

@ObjectModel.representativeKey: 'Orgunit'

define view Zi_Orgunit_Dim

 as select distinct from P_PDOBJECT 

    association[0..*] to Zi_Orgunit_H  as _OrgUnit_Hier on  $projection.Orgunit    = _OrgUnit_Hier.OrgUnit

                                                       

    association[0..*] to I_OrgUnitText as _Text on

        $projection.Orgunit = _Text.OrganizationalUnit                                                                      

{

@ObjectModel.Hierarchy.association: '_OrgUnit_Hier'

@ObjectModel.text.element:  [ 'OrgunitName' ] 

@EndUserText.label: 'Orgunit'

key P_PDOBJECT.ObjectID as Orgunit, 

@Semantics.businessDate.to: true

P_PDOBJECT.EndDate,

@Semantics.businessDate.from: true

P_PDOBJECT.StartDate,

/*Assosiations*/

_OrgUnit_Hier,

/*Text*/

@Semantics.text: true

_Text.OrganizationalUnitName as OrgunitName

    

} where P_PDOBJECT.ObjectType = 'O';

Data preview in HANA Studio (As you can see no hierarchy are show. This is normal of course):

3. Open dimension CDS view Zi_Orgunit_Dim in Analysis for Excel.

Notes:

• Technical name is 2CZIORGUNITD.
• Technical name and description of hierarchy are the same as we defined earlier.

4. Compare with PPOSE:

Almost the same except a root Company organisational unit.

Problem:

Our hierarchy has ROOT node Company 50000050, but unfortunately this is not showed correctly in Analysis for Excel and BEx Analyzer also.

I supposed that these 2 annotation at the hierarchy CDS view definition should control this:

• @Hierarchy.parentChild.orphanedNode.handling: #ROOT_NODES

Defines how nodes with a parent that does not occur as a child are processed. It should create a root for Company 50000050, but It is not happen.

• @Hierarchy.parentChild.rootNode.visibility: #DO_NOT_ADD_ROOT_NODE

The system will not add an additional artificial single root node to the hierarchy.

Compromise Solution:

We could filter in reports Not assigned Nodes of hierarchy handle this case some how.