This constructs a complete hierarchy from parent/child relationships, and produces a description of the hierarchy in vertically or horizontally flattened format.
Rows describing individual parent-child relationships.
Each row must contain two columns that function as the keys of the parent and child in the relationship.
The input can also include columns containing attributes describing the parent and/or child.
An example of an input data set is an Employee Master hierarchy description in which each row represents the relationship between an employee (child node) and the employee’s manager (parent node).
In this example:
- Employ_ID is the child node identifier and the primary key
- Mgr_ID is the parent node identifier
The input data set cannot include rows with operation codes other than NORMAL.
The input data set can contain hierarchical data.
Identifies the column in the source data that contains the parent identifier in each parent-child relationship
You can drag this column from the source schema into the Parent column box.
You cannot specify columns including nested schemas as the parent.
Identifies the column in the source data that contains the child identifier in each parent-child relationship
You can drag this column from the source schema into the Child column box.
You cannot specify columns including nested schemas as the child.
Indicates how the hierarchical relationships are described in the output. Choose from two options:
- Horizontal: – Each row of the output describes a single node in the hierarchy and the path to that node from the root. This mode requires that you specify the maximum path length through the tree as the maximum depth.
- Vertical: – Each row of the output describes a single relationship between ancestor and descendant and the number of nodes the relationship includes. There is a row in the output for each node and all of the descendants of that node. Each node is considered its own descendant and therefore is listed one time as both ancestor and descendant.
The Hierarchy Flattening editor includes:
- Schema In pane which contains the source schema
- Schema Out pane which contains the target schema
- Hierarchy Flattening transform options
You can drag column names from the source schema to fill in values for the Parent column, Child column, Parent attribute list, and Child attribute list options.
Data Services generates the target schema in response to the values you choose in the transform options. To refresh the target schema after you make a change to the options, choose View > Refresh or press F5.
The following picture shows the editor with values for vertical flattening:
The following picture shows the editor with values for horizontal flattening:
Sample Output will look like the below image-