Union Transformation in Informatica Cloud (IICS)

Spread the love

1. Union Transformation

Union transformation is an active and connected transformation in Informatica Cloud(IICS). It is used to read data from multiple pipelines and merge data into a single pipeline. It is similar to UNION ALL in SQL statement.

Union Transformation reading data from 2 Input groups

2. Union Transformation Properties

When you configure a Union transformation, you define Union properties on the following tabs of the Properties panel:

  • Incoming Fields tab: Configure Input Groups based on the number of data streams you wanted to merge
  • Output Fields tab: Configure the fields that are expected as output from the Union transformation
  • Field Mapping tab: Map the fields from each Input group to the output fields defined.

3. Input Groups in Union transformation

By default, Union transformation has two input groups. If you want to merge data from more than two sources, a new input group can be created under Incoming Fields tab of Union transformation.

Map data from multiple upstream transformations to the Input Groups created in the Union transformation.

Input Groups in Union Transformation

The input groups can be renamed in Union transformation. The added input groups can also be deleted as long as there are atleast two input groups.

4. Output Fields in Union transformation

The fields defined under Output Fields tab are the fields which pass as output from Union transformation to the downstream transformation. These output fields should be manually configured in Union transformation.

There are two different ways in which output fields can be configured in Union transformation.

4.1 By selecting fields from Input groups

  • Under Output Fields tab, on the right top corner, beside delete button, click the three dots.
  • From the drop down click Select from Input Group option.
  • From the pop up, you can select the Input group and see the field names from the Input group selected.
  • Select the field name from any Input group you want and add it to the right hand side and click ok.
Output Fields tab in Union Transformation

4.2 Creating new fields manually

  • Under Output Fields tab, on the right top corner, beside delete button, click the three dots.
  • From the drop down click New Field option.
  • Enter the field name, datatype, precision and scale of the field and select OK to save the field.

You can create fields in Output Fields tab of Union transformation using combination of both by selecting from Input groups and by creating a new field manually if required.

5. Field Mapping in Union Transformation

Under Field Mapping tab of Union transformation, you need to map the fields from each Input group to the fields created under Output Fields tab.

If there are some fields in Output Group which are not mapped, mapping passes null values to output fields.

6. Why Union transformation is an active transformation?

Union is an active transformation because it combines two or more data streams into one. Though the total number of rows passing into the Union transformation is same as the rows coming out, the row order of the input rows changes at the output. This is because the Union transformation does not restrict the flow of any data based on Input group and keeps on passing the data as it receives from any Input group.

7. Does Union transformation remove duplicates in Informatica Cloud?

Union transformation does not remove duplicates in Informatica Cloud. To remove the duplicate rows, use sorter transformation with Distinct option enabled after the Union transformation.

Leave a Comment

Related Posts