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.
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
}
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.
No comments:
Post a Comment