The Etlworks 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:
Extract Dimensions: phoneNumber.
Columns to Include Together with Dimensions to Extract:
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