Big Data Management User Guide > Configuring Transformations to Process Hierarchical Data > Flatten Hierarchical Data

Flatten Hierarchical Data

You can flatten elements of hierarchical data into relational data. Use the Flatten Complex Port wizard in the Developer tool to perform the conversion.

The wizard converts hierarchical data to relational data. When you have hierarchical data with nested data type, you can select specific elements or all elements of complex data type to flatten.

Based on the data type of the complex port, the wizard performs the following tasks:

struct
array: Adds a Normalizer transformation with flattened output ports. The wizard flattens the array field in the Normalizer view.; Adds a final Expression transformation that contains a dynamic port with all ports from the upstream transformation including the flattened array ports.
nested data type: Adds one or more Expression and Normalizer transformations with flattened output ports. If you select a child element of a nested complex port, the wizard flattens both the parent and child elements.; Adds a final Expression transformation that contains a dynamic port with all ports from the upstream transformation including the flattened ports.

Flattening a Complex Port

Use the Flatten Complex Port wizard to convert hierarchical data to relational data.

1. In the transformation, select a complex port that you want to flatten.

2. Right-click the selected ports, and select Hierarchical Conversions > Flatten Complex Port.

The Flatten Complex Port wizard appears with the list of elements in the complex port.

The wizard shows the elements of the complex port. The elements of a complex data type have a check box against them. The wizard contains a Flatten All button.

3. In the Select Elements column, select the check box for each element of a struct or an array data type that you want to extract.

If you select a child element of a nested complex port, the wizard automatically selects the parent element to flatten.

The image shows the parent element that is selected when you select to flatten a child element of a nested complex port.

4. Optionally, click Flatten All to flatten all elements of struct or data type in the complex port.

The wizard selects all elements of struct or data type in the complex port to flatten.

5. Click Finish.

You can see the following changes in the mapping:

•For each struct element that you selected to flatten, the mapping contains a new Expression transformation Extract_<element_of_struct_type> with the following ports:

- The complex port from which you want to extract elements.
- One or more output ports for the extracted elements.

The output ports contain expressions that use complex operators to extract elements.

- A dynamic port with ports from the upstream transformation

•For each array element that you selected to flatten, the mapping contains a new Normalizer transformation Flatten_<element_of_array_type> with an input group and an output group. The output group contains the flattened normalized fields for the array element.
•A final Expression transformation Final_Flatten_Result with a dynamic port that contains the flattened ports for the complex port and other ports in the upstream transformation.

The following image shows an example of mapping changes:

The mapping contains an Expression transformation with a struct port, two Expression transformations and a Normalizer transformation to flatten struct and array elements, and a final Expression transformation with the flattened ports.