Skip to Content
Technical Articles
Author's profile photo Uladzislau Pralat

Add Value to Your S/4 HANA Embedded Analytics Content with Material Hierarchy

Standard S/4 HANA Embedded Analytics content does not provide material hierarchy. The reason for that is that material hierarchy is configured as per business requirements and is unique for each client. Still you can benefit from material hierarchy in S/4 HANA Embedded Analytics, for example, drill-down along the hierarchy or selecting data using hierarchy. In my blog I will explain how to fill this gap and create material hierarchy.

In my example, Product Hierarchy is configured to have up to 4 levels: Product Category, Product Class, Product SubClass and Product Family. Key values for each hierarchy level are respectively 2, 3, 3 and 5 characters long.

 

Here is a list of changes that are required to implement material hierarchy:

 

Create Z_T179 Basic CDS View

This view helps to identify parent child relationships between Product Category, Product Class, Product SubClass and Product Family

@AbapCatalog.sqlViewName: 'ZT179'
@VDM.viewType: #BASIC
@EndUserText.label: 'Product Hierarchy'
define view z_t179 as select from t179 {
  key prodh, 
  left(prodh,2) as prodh1,  
  left(prodh,5) as prodh2,
  left(prodh,8) as prodh3,
  left(prodh,13) as prodh4,
  stufe
}

Note: in this view Product Category, Product Class, Product SubClass and Product Family are respectively 2, 5, 8 and 13 characters long.

 

Create ZI_ProductHierarchyNodeHier Hierarchy CDS View

This view organizes Category, Product Class, Product SubClass and Product Family into hierarchy

@AbapCatalog.sqlViewName: 'ZIPRODHNDH'
@Analytics.dataCategory: #HIERARCHY
@ObjectModel.representativeKey: 'ProductHierarchyChild'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Product Hierarchy Node'
@Hierarchy.parentChild.rootNode.visibility:#ADD_ROOT_NODE
@Hierarchy.parentChild.name: 'PROD_HIER'
@Hierarchy.parentChild.label: 'Product Hierarchy'
@Hierarchy.parentChild: { recurse:          {
                          parent: 'ProductHierarchyNodeParent',   child: 'ProductHierarchyNodeChild'   } }
define view ZI_ProductHierarchyNodeHier as select distinct from z_t179 as t179_child inner join z_t179 as t179_parent
                                                                                             on t179_child.prodh3 = t179_parent.prodh3
                                                                                            and t179_child.stufe = '4'
                                                                                            and t179_parent.stufe = '3'   
association [0..1] to I_ProductHierarchyNode as _ProductHierarchyNode on $projection.ProductHierarchyNodeChild = _ProductHierarchyNode.ProductHierarchyNode
{
  @ObjectModel.foreignKey.association: '_ProductHierarchyNode'
  key t179_child.prodh as ProductHierarchyNodeChild,
  t179_parent.prodh as ProductHierarchyNodeParent,
  t179_child.stufe,
  _ProductHierarchyNode
}

union

select distinct from z_t179 as t179_child inner join z_t179 as t179_parent
                                                  on t179_child.prodh2 = t179_parent.prodh2
                                                 and t179_child.stufe = '3'
                                                 and t179_parent.stufe = '2'
association [0..1] to I_ProductHierarchyNode as _ProductHierarchyNode on $projection.ProductHierarchyNodeChild = _ProductHierarchyNode.ProductHierarchyNode                                                                                        
{
  @ObjectModel.foreignKey.association: '_ProductHierarchyNode'
  key t179_child.prodh as ProductHierarchyNodeChild,
  t179_parent.prodh as ProductHierarchyNodeParent,
  t179_child.stufe,
  _ProductHierarchyNode
}                                                                                        

union

select distinct from z_t179 as t179_child inner join z_t179 as t179_parent
                                                  on t179_child.prodh1 = t179_parent.prodh1
                                                 and t179_child.stufe = '2'
                                                 and t179_parent.stufe = '1'
association [0..1] to I_ProductHierarchyNode as _ProductHierarchyNode on $projection.ProductHierarchyNodeChild = _ProductHierarchyNode.ProductHierarchyNode                                                                                        
{
  @ObjectModel.foreignKey.association: '_ProductHierarchyNode'
  key t179_child.prodh as ProductHierarchyNodeChild,
  t179_parent.prodh as ProductHierarchyNodeParent,
  t179_child.stufe,
  _ProductHierarchyNode
}

union

select distinct from z_t179 as t179
association [0..1] to I_ProductHierarchyNode as _ProductHierarchyNode on $projection.ProductHierarchyNodeChild = _ProductHierarchyNode.ProductHierarchyNode                                                                                        
{
  @ObjectModel.foreignKey.association: '_ProductHierarchyNode'
  key t179.prodh as ProductHierarchyNodeChild,
  cast('' as prodh_d) as ProductHierarchyNodeParent,
  t179.stufe,
  _ProductHierarchyNode
} where stufe = '1'

 

Change I_ProductHierarchyNode Dimension CDS View (Optional)

This change adds ZI_ProductHierarchyNodeHier Hierarchy to I_ProductHierarchyNode Dimension. This is a change to standard CDS View since required changes (highlighted in yellow) go beyond view extension and annotation extension capabilities.

 

Create ZI_Material Basic CDS View

This view adds Material level to parent child relationships between Product Category, Product Class, Product SubClass and Product Family

@AbapCatalog.sqlViewName: 'ZMAT'
@VDM.viewType: #BASIC
@EndUserText.label: 'Material'
define view ZI_Material as select from ZI_ProductHierarchyNodeHier 
{
  key cast(ProductHierarchyNodeChild as abap.char(40)) as nodeid, 
  key cast(ProductHierarchyNodeParent as prodh_d) as parentid,
  cast(ProductHierarchyNodeChild as prodh_d) as prodh,
  cast('' as matnr) as material 
}

union

select from ZI_ProductHierarchyNodeHier as prodh join mara 
                                                  on prodh.ProductHierarchyNodeChild = mara.prdha   
{
  key cast(mara.matnr as abap.char(40) ) as nodeid, 
  key cast(prodh.ProductHierarchyNodeChild as prodh_d) as parentid,
  cast('' as prodh_d) as prodh,
  mara.matnr as material
}

 

Create ZI_MaterialHier Hierarchy CDS View

This view organizes Category, Product Class, Product SubClass, Product Family and Material into hierarchy

@AbapCatalog.sqlViewName: 'ZIMATHIER'
@EndUserText.label: 'Material'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #BASIC
@ObjectModel.dataCategory: #HIERARCHY
@Hierarchy.parentChild: [{
rootNode.visibility:#ADD_ROOT_NODE,
name : 'MAT_HIER',
label: 'Material Hierarchy',
siblingsOrder: [{ by: 'material', direction: #ASC }],
recurseBy: '_parent'  
 }]
define view ZI_MaterialHier as select from ZI_Material
association[0..1] to ZI_MaterialHier as _parent on $projection.parentid = _parent.nodeid
association[0..1] to I_ProductHierarchyNode as _ProductHierarchyNode on $projection.prodh = _ProductHierarchyNode.ProductHierarchyNode
                                           and $projection.material = ''
association[0..1] to I_Material as _Material on $projection.material = _Material.Material
                                            and $projection.prodh = ''                    
{
  key nodeid, 
  parentid,
  @ObjectModel.foreignKey.association: '_ProductHierarchyNode'  
  prodh,
  @ObjectModel.foreignKey.association: '_Material'
  material,
  _parent,
  _ProductHierarchyNode,
  _Material 
}

See wiki page to learn more how the above hierarchy is defined

 

Create ZPRODH2, ZPRODH3 and ZPRODH4 Data Elements (Optional)

These data elements are used to cast product hierarchy levels and such a way properly name Material Dimension properties instead of hard-coding names with @EndUserText.label annotations;

 

Create ZX_Material Extension for I_MATERIAL Dimension CDS View (Optional)

This view extension adds Product Category, Product Class, Product SubClass and Product Family product hierarchy level attributes

@VDM.viewExtension: true
@AbapCatalog.sqlViewAppendName: 'ZXMATERIAL'
@EndUserText.label: 'Material'
extend view I_Material with ZX_Material
association [0..1] to I_ProductHierarchyNode as _ProductHierarchyNode1 on $projection.ProductCategory = _ProductHierarchyNode1.ProductHierarchyNode  
association [0..1] to I_ProductHierarchyNode as _ProductHierarchyNode2 on $projection.ProductClass = _ProductHierarchyNode2.ProductHierarchyNode  
association [0..1] to I_ProductHierarchyNode as _ProductHierarchyNode3 on $projection.ProductSubClass = _ProductHierarchyNode3.ProductHierarchyNode  
association [0..1] to I_ProductHierarchyNode as _ProductHierarchyNode4 on $projection.ProductFamily = _ProductHierarchyNode4.ProductHierarchyNode  
 {
  bismt,
  volum,
  @ObjectModel.foreignKey.association: '_ProductHierarchyNode1'           
  cast(left(prdha,2) as prodh1) as ProductCategory,                               
  @ObjectModel.foreignKey.association: '_ProductHierarchyNode2'           
  cast(left(prdha,5) as zprodh2) as ProductClass,                              
  @ObjectModel.foreignKey.association: '_ProductHierarchyNode3'            
  cast(left(prdha,8) as zprodh3) as ProductSubClass,                                 
  @ObjectModel.foreignKey.association: '_ProductHierarchyNode4'           
  cast(left(prdha,13) as zprodh4) as ProductFamily,  
  _ProductHierarchyNode1,                                                  
  _ProductHierarchyNode2,                                                 
  _ProductHierarchyNode3,                                                                              
  _ProductHierarchyNode4                                                    
}

 

Change I_Material Dimension CDS View

This change adds ZI_MaterialHier Hierarchy to I_Material Dimension. This is a change to standard CDS View since required changes (highlighted in yellow) go beyond view extension and annotation extension capabilities.

 

Source codes can also be downloaded from here:

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Anup Kulkami
      Anup Kulkami

      Hi Uladzislau,

      This is one point SAP needs to address on priority i.e..

      This is a change to standard CDS View since required changes (highlighted in yellow) go beyond view extension and annotation extension capabilities.

      In the BW world, it is quite normal to modify delivered BI content to suit customer needs. But in ABAP world, modification to standard SAP code is not common.

       

      Even if the underlying technology changes, the fundamental requirement of ability to implement such enhancement doesn't.

      Does this imply that we should accept "change standard SAP code"  as new normal for ABAP-CDS? Or is SAP considering to provide better alternatives to changing standard SAP code?

      Thank you,

      Anup

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Anup,

      the same as in ABAP development, sometimes you have to make a change to SAP standard to meet user requirements

      Regards, Uladzislau

      Author's profile photo Dmitry Kuznetsov
      Dmitry Kuznetsov

      Anup,

      There are just situations where existing sensibility can not help and Uladzislau provided one such example. Then it is up for discussion whether a requirement needs to be met with a consequence of adjusting the standard code or you guard the code and say "no" to a requirement.

      Documenting it well for future upgrades turns very important then..

      Dmitry Kuznetsov

      BI Consulting

      Author's profile photo Subhendu Ghanty
      Subhendu Ghanty

      Hi Hi Uladzislau, ,

      While activating thisc extended view -> ZX_Material it's taking more than two hour, still Activation process is in progress. Do you also encounter same scenario?

      Thanks,

      Subhendu

       

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Subhendu,

      it is normal. I_Material is used in many standard views and system activates them all.

      Regards, Uladzislau

       

      Author's profile photo Selvakumar Mohan
      Selvakumar Mohan

      How about just copy I_MATERIAL into Customer namespace and use it.

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Do you really want to copy all standard CDS views where I_Material is used as well in order to make use of Material Hierarchy?

      Author's profile photo Daniel Klink
      Daniel Klink

      I think it is a valid approach. Of course there are advantages and disadvantages, but we also decided to go this way as changing I_Material was not possible in our environment. We are carefully selecting use cases and copying other standard views in scenarios where material hierarchy is required. So far, it is working fine and we are convinced it was a good decision.

       

      ---

      I found this blogpost last year when I was struggling with implementation of material hierarchy for reporting, and liked it. but i did not take the time to reply. Thanks Hi Uladzislau,, I learned a lot from this and always appreciate the great content you are sharing! Keep up the good work for the community!

      Author's profile photo Virinchi Punyamanthula
      Virinchi Punyamanthula

      Hi Hi Uladzislau, ,

      Nice blog!!!!

      I followed your blog and I was able to achieve the product hierarchy which I was struggling to do in CDS. But issue is m not getting text for the intermediate nodes. Only for first node and last node it is coming. Could you please help in fixing this?

       

      I have attached the screenshot. Please help!!

       

      Thanks & Regards,

      Virinchi

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Virinchi,

      please check I_ProductHierarchyNode dimension if following association path you get a text for your hierarchy levels, for example, 4001. Maybe hierarchy level not defined correctly in Z_T179.

      Regards, Uladzislau

       

       

      Author's profile photo Virinchi Punyamanthula
      Virinchi Punyamanthula

      Hello Uladzislau,

       

      Thanks for your valuable help and it's working. But we are not getting the node description for level 5 and level 6. We have some material which has been associated to level 4 it is working fine. But the material which are linked to level 6 hierarchy the above nodes description not working.

       

      The material which are associated to 4th level is coming fine..

       

      Could you please help on this. I have attached the relevant screenshot for reference.

       

      Thanks

      Virinchi

      Author's profile photo Prashant Shah
      Prashant Shah

      Hi Uladzislau,

      I have followed your blog and tried to implement the solution . we tried to check the output through RSRTS_ODP_DIS . but unable to get the required result. Can you please guide what is the right way to execute this after creating all above CDS views.

       

      Thanks & Regards,

      Puneet

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Prahant,

      please make sure that you follow provided instructions to a dot. It is working for others.

      Regards, Uladzislau

      Author's profile photo Prashant Shah
      Prashant Shah

      Hi Uladzislau,

       

      Thanks, we followed the blog and got the hierarchy.

      We have created analytics query view on top of cube view , which has material hierarchy  association. but when we are trying to see analytical query in RSRT the hierarchy is not showing up in the properties of material characteristic. Therefore we are unable to activate the hierarchy for the analytical query.but the hierarchy is available at dimension view of I_material . Could you please provide some inputs on this .just in case we are missing any annotation or association which  would allow the Hierarchy to flow in analytical query.

       

      Regards

      Prashant

       

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Prashant,

      please check your I_Material CDS view in RSRTS_ODP_DIS transaction. If hierarchy is available there, then it will also should be available in Query CDS view as well

      Regards, Uladzislau

      Author's profile photo Laurent Caliari
      Laurent Caliari

      Hi Prashant

      Did you manage to make it work? I'm struggling with the same issue.

      All seems ok. Hierarchy is available in RSRTS_ODP_DIS as well but not when running a query.

      Thanks in advance

      Laurent