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;
• Create ZI_ProductHierarchyNodeHier Hierarchy CDS View;
• Change I_ProductHierarchyNode Dimension CDS View (optional);
• Create ZI_Material Basic CDS View;
• Create ZI_MaterialHier Hierarchy CDS View;
• Create ZPRODH2, ZPRODH3 and ZPRODH4 Data Elements (optional);
• Create ZX_Material Extension for I_MATERIAL Dimension CDS View (optional);
• Change I_Material Dimension CDS View.

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:

• Z_T179
• ZI_ProductHierarchyNodeHier
• I_ProductHierarchyNode
• ZI_Material
• ZI_MaterialHier
• ZX_Material
• I_Material