How to build a Custom Hierarchy in ABAP CDS views

Hi!

In this post I’d like to consider hierarchies in CDS views.

Good starting point could be looking at Cost Center hierarchy, it was described here.

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. For more information go Hierarchy Annotations.

@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.

P.S. If you faced this problem and found some general (more beautiful) solution, please let me know in comments.

Thank you for attention!