The Union transformation is an active and linked transformation within Informatica Cloud (IICS). Its purpose is to retrieve data from multiple pipelines and combine it into a single pipeline. It functions akin to the UNION ALL operation in SQL statements.
Union Transformation with two Input groups
2. Properties Of Union Transformation.
When setting up a Union transformation, you specify Union properties within the tabs of the Properties panel as follows:
Incoming Fields tab: Set up Input Groups according to the number of data streams you intend to merge.
Output Fields tab: Set up the fields expected as output from the Union transformation.
Field Mapping tab: Map the fields from each Input group with the defined output fields.
3. Input Groups in Union transformation Informatica IICS
The Union transformation typically includes two input groups by default. If you wish to merge data from more than two sources, you can create a new input group within the “Incoming Fields” tab of the Union transformation.
Connect data from various upstream transformations to the Input Groups established within the Union transformation.
You can rename input groups within the Union transformation. Additionally, newly added input groups can be deleted, provided there are at least two remaining input groups.
4. Output Fields - Union transformation
The fields specified under the Output Fields tab represent the fields that are transmitted as output from the Union transformation to subsequent transformations. It’s necessary to manually configure these output fields within the Union transformation.
Output fields in the Union transformation can be configured in two distinct ways.
selecting fields from the Input groups:
- Navigate to the top right corner of the Output Fields tab and locate the three dots next to the delete button.
- Click on the three dots and choose the “Select from Input Group” option from the dropdown menu.
- A pop-up window will appear where you can select the desired input group and view the field names associated with it.
- Choose the field name you want from any input group, add it to the right-hand side, and click “OK” to confirm your selection.
Manually generating new fields:
- Navigate to the top right corner of the Output Fields tab and click on the three dots next to the delete button.
- Choose the “New Field” option from the dropdown menu.
- Enter the desired field name, along with its datatype, precision, and scale. Click “OK” to save the field.
In the Output Fields tab of the Union transformation, you have the flexibility to create fields by either selecting them from input groups or by manually creating new fields when necessary.
5. Field Mapping - Union Transformation IICS
In the Field Mapping tab of the Union transformation, you’re required to establish mappings between the fields from each Input group and the fields generated under the Output Fields tab.
If any fields in the Output Group remain unmapped, the mapping process assigns null values to those respective output fields.
6. What makes Union transformation an active transformation?
Union is classified as an active transformation because it merges two or more data streams into a single one. While the total number of rows entering and exiting the Union transformation remains the same, the order of the input rows may change in the output. This occurs because the Union transformation doesn’t impose any constraints on the data flow based on input groups, allowing it to continuously pass data as it’s received from any input group.
7. In Informatica Cloud, does the Union transformation eliminate duplicates?
In Informatica Cloud, the Union transformation does not eliminate duplicates. To address duplicate rows, you can utilize a Sorter transformation with the Distinct option enabled following the Union transformation.
8. FAQ's
The Union transformation in IICS is used to combine data from multiple sources or pipelines into a single data stream.
The Union transformation does not automatically remove duplicate rows. Duplicate elimination can be achieved by using a Sorter transformation with the Distinct option enabled after the Union transformation.
Yes, you can rename input groups within the Union transformation to better organize and identify your data sources.
Yes, you can manually generate new fields in the Output Fields tab if needed, allowing for custom data field creation.
If any fields in the Output Group are left unmapped, the Union transformation assigns null values to those respective output fields.
Yes, the Union transformation is considered an active transformation because it merges multiple data streams into one without restricting the flow of data based on input groups.
Yes, you can configure output fields in the Union transformation by selecting fields from input groups or by manually creating new fields, providing flexibility in data mapping.
One limitation is that the Union transformation does not support the removal of duplicates directly within the transformation itself. However, this can be addressed using subsequent transformations such as Sorter with the Distinct option enabled.
