Flattening of Hierarchy in SAP BW/4HANA using CDS Views

[Manish]

Summary:

Hierarchies are one of the most important elements in SAP BW which provides the Business users flexibility to navigate the hierarchy structure in the Frontend Reports. At times, a need arises where we need to flatten the hierarchy structure because of the business requirement in order to display only certain levels of hierarchy in the report without displaying the whole hierarchy structure, there could be a number of reasons where we need to flatten the hierarchy.

Below is the simple Plant hierarchy structure which consists of 5 levels: Group, Region, Country, City/County, and Plant.

 

 

The following graphic gives an example of a hierarchy for the InfoObject ZZPlant displaying all the levels with entities and relation.

 

Steps to flatten the hierarchy using CDS Views are as follows.

 

We need to create multiple CDS Views in order to achieve the desired outcome.

CDS View 1:

Define a CDS View on hierarchy table and restrict it to get the Top Level so that we have only the Root in the result.

If there are multiple Hierarchy maintained for the InfoObject, restrict it to the relevant Hierarchy ID in the CDS view for which we require desired results.

 

CDS View 2:

Define the next CDS view on the same hierarchy table deriving all the levels excluding the Top Level.

 

CDS View 3:

In order to achieve the outcome, in this CDS View, we will join both CDS View 1 and CDS View 2 using nested joins between Node ID and Parent ID using the Parent Child relationship to extract all levels with flattened structure.

In this example, I have flattened up to level 5, but this can be extended even further based on the particular business requirement.

 

The following graphic displays the Flattened structure of the hierarchy.

 

In certain scenarios, the Hierarchy would not have all levels populated, for example in a Cost Centre Hierarchy a Cost Centre can be populated under Level 3 or Level 4 or other Levels. To cater this, a case statement can be written to populate the values in a new field regardless of what level the Cost Centre exist.

 

Conclusion:

Once we have the flattened hierarchy structure, it can then be either loaded into InfoProvider or could be consumed in Open ODS and then into Composite Provider (Virtual layer) which can be consumed in frontend tools. We can join the the transaction data with this Flattened structure on the basis of Plant and derive the levels accordingly in the report.

Note:

The Annotations defined in the above CDS Views are standard system generated. Based on the business requirement, other annotations can be added.

Please feel free to share your comments and feedback.

[ABner]

This is very cool.  No more programs that recursively write the contents into a table for joining!

Trying this out on a basic table like /bi0hcostcenter I got an error that the table length was > 4096.  Anything larger than the L2 gave this error.  Were you able to find a workaround for this issue?

[Manish]

Hi Aaron,

Try removing extra fields from the CDS view, include only the required fields in the CDS view. This should solve the problem. Also you can refer to SAP Note 2458652. If the fields are required, you can add those fields through extension of CDS view. I tried and it worked perfectly fine.

Thanks,

Manish

[ABner]

Can you provide a syntax for the extension suggestion:?

[ABner]

Here is my sample - anything jump out as set up wrong?

 

View 1: 2 fields

define view Z_CDS_CCH_FLAT as select from /bi0/hcostcenter {
nodeid,
nodename as LEVEL1
// tlevel,
// parentid,
// iobjnm
}
where objvers = 'A'
and nodeid = '00000001'

View 2: 4 fields

define view Z_CDS_CCH_L2 as select from /bi0/hcostcenter {
nodeid,
nodename as LEVEL1,
// tlevel,
parentid,
iobjnm
}
where objvers = 'A'
and nodeid <> '00000001'

view 3: selecting from first 2 - cannot add the L3, L4 key fields due to error.

define view Z_CDS_CCH_BOTH as select

from Z_CDS_CCH_FLAT as L1
left outer join Z_CDS_CCH_L2 as L2
on L1.nodeid = L2.parentid
left outer join Z_CDS_CCH_L2 as L3
on L2.nodeid = L3.parentid
left outer join Z_CDS_CCH_L2 as L4
on L3.nodeid = L4.parentid
left outer join Z_CDS_CCH_L2 as L5
on L4.nodeid = L5.parentid

{
key L1.LEVEL1,
L2.LEVEL1 as LEVEL2,
// L3.LEVEL1 as LEVEL3,
// L4.LEVEL1 as LEVEL4,
// L5.LEVEL1 as LEVEL5,
case
when L2.iobjnm = '0COSTCENTER' then (L2.LEVEL1)
// when L3.iobjnm = '0COSTCENTER' then (L3.LEVEL1)
else 'NA'
end as LASTLEVEL
}

[Manish]

Hi Aaron,

While creating CDS views, you can take the template 'Extend View' and enable the field which you have disabled or commented in the previous CDS view.

 

Thanks,

Manish