Wednesday, 22 February 2017

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)

How to build a Custom Hierarchy in ABAP CDS views

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

How to build a Custom Hierarchy in ABAP CDS views

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

How to build a Custom Hierarchy in ABAP CDS views

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

  • 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 is mandatory.
@VDM.viewType: #BASIC
@ObjectModel: { dataCategory: #HIERARCHY }
@AbapCatalog.sqlViewName: 'ZIORGUNITH' 'ORGEH_01'
@Hierarchy.parentChild.label: 'Orgunit hierarchy 01'
{ 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,

} 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:

How to build a Custom Hierarchy in ABAP CDS views

2. Create a dimension view Zi_Orgunit_Dim

  • 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, true
@Semantics.businessDate.from: true

@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):

How to build a Custom Hierarchy in ABAP CDS views

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

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

How to build a Custom Hierarchy in ABAP CDS views

4. Compare with PPOSE:

How to build a Custom Hierarchy in ABAP CDS views

Almost the same except a root Company organisational unit.


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.

No comments:

Post a Comment