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;
- 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.
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:
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
Hi Anup,
the same as in ABAP development, sometimes you have to make a change to SAP standard to meet user requirements
Regards, Uladzislau
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, ,
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
Hi Subhendu,
it is normal. I_Material is used in many standard views and system activates them all.
Regards, Uladzislau
How about just copy I_MATERIAL into Customer namespace and use it.
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?
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!
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
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
Hi Prahant,
please make sure that you follow provided instructions to a dot. It is working for others.
Regards, Uladzislau
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
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
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