How to build a Custom Hierarchy in ABAP CDS views

[former_member205880]

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!

[Former Member]

Great work again!

Following your blog for knowing more in depth about embedded analytics  using ABAP CDS views

[former_member192683]

Amazing stuff. Congrats!

[former_member303753]

hello maksim.alyapyshev,

I solved this problem as you face .  you need add on line for the root .

for example:

add the data ('tony', '') to the following table .

name	boss
elan	sunny
joy	eric
sunny	tony
eric	tony

 

why do that?
at class CL_RSODP_ABAP_CDS_CONTEXT->GET_PARENT_CHILD_HIERARCHY line 382,  the where condition only search the node that have parent, then build the hierarchy tree, so if you       want the root display, you need do that.

  DO 99 TIMES.

    ASSERT sy-index < 99.

    LOOP AT lt_node ASSIGNING <ls_node>

         WHERE parentid IS NOT INITIAL

           AND tlevel IS INITIAL.

      READ TABLE lt_node ASSIGNING <ls_node2>

           WITH TABLE KEY nodeid = <ls_node>-parentid.

      ASSERT sy-subrc = 0.

      CHECK <ls_node2>-tlevel IS NOT INITIAL.

      <ls_node>-tlevel = <ls_node2>-tlevel + 1.

    ENDLOOP.

    IF sy-subrc <> 0.

      EXIT.

    ENDIF.

  ENDDO.

 

best,

Elan

[Former Member]

Hi Maksim,

really useful blog! Do you know if it's mandatory to create a dimension view (or re create if dimension was already delivered in standard content) to link it to its hierarchy? I did not found any way to extend field annotations of standard abap cds (example @objectmodel.hierarchy.association  ), thus newly created could be duplicated of standard dimensions and outside of the standard model.thanks

[former_member205880]

Hi, Pierfrancesco!

I think it 's mandatory. The most simple way (and don't know any other workaround) is to create a new dimension CDS and include all you need:

• association to hierarchy CDS view


• @objectmodel.hierarchy.association above the field

About field extensions, look here. This is documentation about  extension with ANNOTATE VIEW. But it is quite limited and not support @ObjectModel annotations.

So even if you EXTEND VIEW and add new assosiation to hierarchy CDS view (like examples here), you could not add @objectmodel.hierarchy.association above the field.

BR, Maksim

 

[Former Member]

Thanks

[Former Member]

Hi Maksim,

I followed your code and created two CDS views (one for dimension and one for hierarchy).

However when I test the master data view in RSRTS_ODP_DIS or in Analysis office, the query doesn't recognize hierarchy.

I get attached error. I don't understand what I am missing here. Would you be able to suggest a solution?

@AbapCatalog.sqlViewName: 'ZAK1ITMD'

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Item Dimension Table'

@VDM.viewType: #BASIC

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

//@ObjectModel.dataCategory: #TEXT

//@ObjectModel.usageType.dataClass: #MASTER

@ObjectModel.representativeKey: 'Item' 

define view zak1_item_dimension 

 as select distinct from /rpm/item_d as item_md

    association[0..*] to Zak1_Item_Hiernode  as _itemHier on  item_md.guid    = _itemHier.Item

                                                       

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

        item_md.guid = _Text.ReferencedObjectUUID                                                                      

{

@ObjectModel.Hierarchy.association: '_itemHier'

@ObjectModel.text.element: 'ItemText' 

@EndUserText.label: 'Item'

key item_md.guid as Item, 

item_md.category as ItemCategory,



/*Assosiations*/

_itemHier,

_Text,

/*Text*/

@Semantics.text: true

_Text.ObjectName as ItemText

    

}

@VDM.viewType: #BASIC

@ObjectModel: { dataCategory: #HIERARCHY }

@AbapCatalog.sqlViewName: 'ZAK1ITMH'

@Hierarchy.parentChild: [{name: 'ITMH_01'},

{label: 'Item 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 Zak1_Item_Hiernode       

     

 as select from /rpm/item_d as item_hiertab 



 association[0..*] to zak1_item_dimension as _itemDimension on item_hiertab.guid = _itemDimension.Item

 

{         

    key     item_hiertab.guid as Item,

    _itemDimension.Item as HierarchyNode,

    item_hiertab.parent_guid  as ParentNode,   

    _itemDimension

 

}

 

[former_member205880]

Hi, try to add in Hierarchy CDS assosiation to dimention:

define view Zak1_Item_Hiernode as select from /rpm/item_d as item_hiertab

association[0..*] to zak1_item_dimension as _itemDimension on item_hiertab.guid = _itemDimension.Item

{ 

@ObjectModel.foreignKey.association: '_itemDimension' --!try to add line like this

key item_hiertab.guid as Item,

_itemDimension.Item as HierarchyNode,

item_hiertab.parent_guid as ParentNode,

_itemDimension }

 

Br, Maksim

[Former Member]

Thanks Maksim,

Works perfectly now!

[shivamshukla12]

Thanks for Sharing this wonderful stuff for learning.

 

Shivam

[former_member109557]

Hi,

We tried to display hierarchy using Hierarchy CDS views and it is fine in "Analysis Office" but the expansion of nodes are not working if the same is consumed in Fiori Elements List report.

The issue is detailed in (https://answers.sap.com/questions/581579/node-expand-not-working-in-hierarchy-cds-view-cons.html). Please can you let us know your comments.

Regards,

Vivek

 

[virenp_devi]

Hello Maksim,

Super Blog!! It worked in one of my case perfectly.

I tried same exercise for one more scenario, everything works but Text. CDS view does show text correctly however in Analysis for office it shows Blank  against Parents/childs when Text format is chosen. Interestingly it shows new option " Node Description" where all texts appear together like another field. Kindly assist. Thank you.

 



 

 

 

 

 

[Subhendu]

Hi maksim.alyapyshev

While activating 1st CDS view - Zi_Orgunit_H, due dependency on  2nd CDS view - Zi_Orgunit_Dim ,it's not letting me to activate and Vice -versa (i.e while activating 2nd CDS view , Zi_Orgunit_Dim due to dependency on Zi_Orgunit_H it's throwing activation error )

What is the solution for this problem?
Any help appreciated.

Thanks,

Subhendu

[former_member205880]

HI, could you try to activate them together through Activate of inactive ABAP objects. Or you could temporarily comment dependencies active and remove comments chars.

 

BR, Maksim

[Subhendu]

Thanks Maksim for your answer . It worked 🙂

[former_member329454]

Hi Maksim,

This is an excellent blog! However, when I’m selecting hierarchy node after selecting hierarchy in my Bex query (Hierarchy CDS was built on h table of BPC Info object /ERP/ENTITY), I get an error message stating that particular “node value for variable is invalid”. But I can see this node is available in rsh1. Any thoughts?

Regards,

Kiran

[former_member509411]

Hi Maksim,

you already mentioned the time-dependency yourself (crucial requirement for most organziation hierachies). Also, a good thought to introduce an Input Parameter for this, so we are able to set dynamic dates for the hierachy.

However, I ran into the issue that the hierachy is attached via association to e.g. a cube which acts as a Transient Provider. An association again doesnt accept Input Parameters the same way joins do. How would you go forward with pushing the Input Parameter to the hierachy in a proper way? Would we have to expose the start/enddates up to the association so they could be part of the filter condition?

[sankar_roy]

Hi Maksim,

 

My requirement is to display IM Projects Hierarchy in Analysis for Office.

I am able to display the hierarchy,

Each child node in the hierarchy can have multiple sub projects stored in a custom table.

I am trying to achieve this by association to a view which selects data from the custom table. But since each node(projects) in the hierarchy can have multiple sub projects in the custom table so i am getting an error "Child-node 'XXXX' is assigned to Parent_Node 'YYYY' multiple times.

How do we get rid of this? I understand that the node is the key to the hierarchy. But in my requirement a node can have multiple sub projects.

Thanks in advance!

 

Best Regards,

Sankar

[former_member680888]

Hello

I have replicated this hierarchy for Material dimension. And I can see proper hierarchy in Analysis for office.

Now How I can use this with my transaction data. Do I need to have an association of this Hierarchy dimension CDS (which one?) with transaction data CDS?

 

Thanks

Hari

[former_member680888]

Hello

I have replicated this hierarchy for Material dimension. And I can see proper hierarchy in Analysis for office.

Now How I can use this with my transaction data. Do I need to have an association of this Hierarchy dimension CDS (which one?) with transaction data CDS?

 

Thanks,

Hari

[former_member509411]

Any update on this? The requirement to pass a date parameter to this type of CDS-hierachy to allow reports being run on e.g. previous organization hierachies would be important in many settings. Help appreciated!

[Alexia]

Hi Jan,

Did you manage to solve this? I'm also interested in the solution. So far I couldn't find a way to filter the hierarchy properly.

[kuara]

Hi,

I have replicated this hierarchy for Orgunit dimension. And I can see proper hierarchy in Analysis for office.

Now How I can use this with my consumption cds view . Do I need to have an association of this Hierarchy dimension CDS (which one?) with transaction data CDS?

Regards,

Arvind kumar

[kuara]

Hi Hari,

I have also same question, if you have already know answer to this issue, then ot would be nice if you can share.

Thanks in Advance !!!

Regards,

Arvind kumar

[inaki_zunzarren]

Hi,

any idea how create a level hierarchy with CDS View?

All examples are parent-child hierarchies, for instance Product, GL Account, CostCenter, so on

However, there is not any example for level hierarchies, for instance Location (country, state, region).

Is it possible?

Thanks and best regards,

Iñaki

[dhiraj_more]

Hi Maksim,

That's a great blog even I have a analytical requirement where I have to show transaction data against Cost Center Hierarchy. I went through your blog and at least have some idea how will I be able to form the hierarchy.

Thanks,

Dhiraj M