Denormalizing nested dataset (extracting dimensions)

Integrator supports a technique called "denormalization" or extracting the dimension.

Read about extract dimensions transformation. 

When the source (FROM) dataset is a nested object, the following transformation parameters are available:

• Extract Dimensions - use this field to specify dimensions to extract from the nested data object. This transformation will be executed last, after the whole nested data object is read into memory. To accomplish this, it will automatically disable data streaming.
• Columns to Include Together with Dimensions to Extract - a comma-separated list of the columns in the driving dataset to include, together with the extracted dimension. This field is used together with a "Dimensions to Extract" transformation and specifies what columns in the driving dataset should be included, together with the columns from the dimension.

So, if we keep reusing the "users" example, which extracts all the phone numbers, together with the user data, we would do the following:

Step 1. Skip the mapping altogether.

Step 2. In the Complex Transformations tab enter:

• For Extract Dimensions - phoneNumber.
• For Columns to Include Together with Dimensions to Extract - firstName,lastName,age.

As a result, we will create the following flat dataset:

firstName   lastName    age  ....  phoneNumber_type   phoneNumber
Duke        Java        18   ....  Mobile             111-111-1111
Duke        Java        18   ....  Home               222-222-2222