Skip to Content
Author's profile photo Former Member

Using the Hierarchy Flattening Transform

The hierarchy flattening transform can analyze a parent-child relationship and provide a description of the hierarchy in a flattened format (vertically flattened/ horizontally flattened). Each row in the output will contain 1 parent-child relationship.

For example consider the following reporting structure in an organization:


The input data is in a table EMP_MGR. The data is corresponding to the hierarchy structure in the above diagram.The below figure shows the data in the table EMP_MGR.


This data is taken as the input to the Hierarchy Flattening Transform. In this example we will see the horizontal flattening by Hierarchy Flattening Transform. This will mean that each root to node relationship will be “flattened” and presented as a row, eg: MGR_1 : EMP_2, MGR_1 : MGR_2, MGR_1 : MGR_2:EMP_4 etc. The figure below shows the Dataflow containing the hierarchy transform.


We will configure the Hierarchy Flattening Transform such that the MGR_ID as the parent column and EMP_ID as the Child column. Since this example deals with only 2 columns (representing Parent and Child) we will not be populating the Parent attribute List and the Child Attribute List. If we had additional columns in the source table , that are associated with the Parent column, we could have added them to the Parent attribute List. Similarly , if we had additional columns in the source table , that are associated with the Child column, we could have added them to the Child attribute List. Configure the Input schema columns only. After that , if you click on “Validate” button, the right hand columns are created automatically. The number of columns (…upto LEVEL 3 in this snapshot) depend on the maximum depth that is selected.The figure below shows the configuration of the hierarchy flattening transform.


Maximum depth is taken as 3 in the initial run.  The root node (MGR_1) has zero level depth, MGR_2 has depth 1 , EMP_4 has depth 2 and so on. So having a maximum depth =3 will mean that we cannot fully represent EMP_6 which has a depth =4. This is illustrated when we run the Job and look at the output.


We see that the last row is incomplete.

If we reset Maximum Depth= 4 and run the job again then we get the output as shown below . Here the last row shows the root to node relationship completely for EMP_6.


Note :

If the input data is erroneous, that is there exists cyclic relationship, then a run-time error is produced.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks for posting to the Hierarchy Flattering transformation. I have One more question priya. Can you please help me..

      how to load the  Oracle exception errors  automatically to the target table(Oracle)?

      My Source is Oracle ,

      My target is Oracle.  please let me know  It is very Urgent...

      Author's profile photo Former Member
      Former Member

      Thanks for posting this. It is simple after you do it once ;-).

      I tried it and got the below:


      Author's profile photo Former Member
      Former Member

      thanks for posting this Debapriya Mandal.

      i would be grateful if you could illustrate an example where we have 3 or more columns.

      and may be you could explain more on parent attributes and child attributes.

      Author's profile photo Prabhakar Teegavarapu
      Prabhakar Teegavarapu

      Thanks for the blog Debapriya. that was very usefull.

      I am trying to find a solution incase of cyclic hierarchy issue.

      based on your example. If Manager1 reports back EMP6 and I have split such data and report in a different file

      Can you suggest something on this?



      Author's profile photo Swetha N
      Swetha N

      Hi Prabhakar, did you find solution to the cyclic issue.

      Please post here if any luck.

      Author's profile photo Former Member
      Former Member


      Can anyone help me in retrieving the employees who are under the manager.


      Thanks in advance,