Skip to Content
Technical Articles
Author's profile photo Manish Gupta

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

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.

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Aaron Benner
      Aaron Benner

      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?

      Author's profile photo Manish Gupta
      Manish Gupta
      Blog Post Author

      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

      Author's profile photo Aaron Benner
      Aaron Benner

      Can you provide a syntax for the extension suggestion:?

      Author's profile photo Aaron Benner
      Aaron Benner

      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
      }