Skip to Content
Technical Articles

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:

8 Comments
You must be Logged on to comment or reply to a post.
  • 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

    • 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

  • 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

    • 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

       

       

      • 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