0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×

Hierarchy Flattening Transformation:

Hierarchy Flattening Transformation in SAP BODSSummary:

Hierarchy Flattening is a readymade logic in the form of transform which is used to load Hierarchies in the table form.

  • Hierarchy flattening transformation used to group the Hierarchies (master data).
  • Supports only for Tree structured inputs.
  • In Business Objects Data Services, Hierarchies are loaded in two fashions.
  1. Horizontal
  2. Vertical
  • Horizontal Flattening :
    • Root to node relationship will be flattened.
    • Each row in the output describes a single node and the path to the node from the root node.
    • Number of records in the output will be equal to the number of nodes in the input tree.
    • Enables drill up and drill down. 
  • Vertical Flattening :
    • It takes a node and defines its relationship with all the other nodes.
    • Each record of output describes a single relationship between Ancestor and Descendent and the no. of nodes the relationship includes.
    • Enables global filtering – performance process. 

Prerequisite:

  • Input the Hierarchy Flattening transform must be a tree in the form of flat structure. 

NOTE:

  • In a Hierarchy flattening scenario, Horizontal flattening is mandatory where as Vertical flattening is optional. 

Example Scenario: 

Figure1 shows a sample input dataset which is a flat structure of given tree.

Hierarchy Flattening Transform in BODS
Figure 1: Sample Source Data

Figure2 shows a sample target data which is Horizontal flat structure of input dataset obtained using Hierarchy flattening transform (Horizontal fashion).

Hierarchy Flattening Transformation in SAP BODS 1
Figure 2: Sample Target Data
  • Both Horizontal and Vertical flattening outputs have a Predefined structure which is discussed in later sections.
Hierarchy Flattening Transform in SAP BODS
Figure 3: Hierarchy Flattening Transformation Object Hierarchy
Hierarchy Flattening Transform in SAP BODS 1
Figure 4: Hierarchy Flattening Transformation job structure
Hierarchy Flattening Transform in SAP BODS 2
Figure 5: Hierarchy Flattening Transformation Job Horizontal ETL flow
Hierarchy Flattening Transform in BODS 1
Figure 6: Hierarchy Flattening Transformation Job Vertical ETL flow
Hierarchy Flattening Transform in BODS 2
Figure 7: Horizontal Flattening Transform Defining rules
  • Select Horizontal Flattening type for Horizontal hierarchy flattening as shown in figure 7.
  • Output of the Horizontal Hierarchy flattening has a Predefined structure as in ‘Schema out’ of above figure.
  • No. of levels depends on the Maximum depth of input tree.
Hierarchy Flattening Transform in BODS 3
Figure 8: Vertical Flattening Transform Defining rules
  • Select Vertical flattening type for Vertical hierarchy flattening as shown in figure 8.
  • Output structure of Vertical flattening has a Predefined structure as in ‘Schema out’ of above figure.

Attachments:

Source File: 

  • Use the below file as source for working out on the transformation.

Click Here to Download Source Data.

.atl File:

  • Import the below .atl file in the Data Services Designer to find the Job for Effective Date transformation.

Click Here to Download ATL File.

That’s it.

Enjoy 🙂

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×