Union Transformation is Active and Connected Transformation in Informatica Cloud IICS – IDMC which is used to combine the data from different sources.
Usage:
• Used to merge data from multiple pipelines into a single pipeline.
• When we have multiple sources (homogeneous/heterogeneous) with the same structure, to combine all the records from source, then we will use union tr.
- Union transformation is an active tr which is used to combine (add the records) from all the sources (homogeneous/ heterogenous) which are having same structure.
- Union transformation is having multiple input groups and single output group. Union transformation cannot have more than one output group.
- Union transformation is exactly opposite of router transformation.
- Union transformation in informatica will work as a union all set operator in oracle. Union transformation will not delete the duplicates.
- Union transformation + sorter transformation with distinct , will acts a union in oracle.
Why union is active transformation?
when a transformation having more than one input or more than output group, we will call that as active transformation.
Because, union transformation is having multiple input groups, that’s why union is active.
Input Groups: By Default, Union Transformation has two input groups like below.
• If you want have more than two sources, then you can create More input groups.
• To add an input group, in the Mapping canvas, connect an upstream transformation to the “New Group” group of the Union transformation.
• You can also add input groups on the Incoming Fields tab of the Union transformation.
Incoming Fields: You can rename input groups. You can rename and delete input groups on the Incoming Fields tab of the Union transformation.
Field mapping: When you work with field mappings in a Union transformation, note the following:
• You must use input groups where the fields have the identical name, type, precision, and scale.
• You must do Field Mapping for each Input Group in the Union Transformation by selecting each input group drop down like below.
Difference between Union and Joiner transformation:
| Requirements | Union Transformation | Joiner Transformation |
|---|---|---|
| Remove duplicate rows | No, you can use a Router or Filter transformation downstream from the union transformation to remove remove duplicates | Yes |
| Combine records based on a join condition | No, the union transportation is equivalent to a UNION ALL statement in SQL, which combines data vertically from multiple sources | Yes, the joiner transformation supports normal, Right Outer, Left Outer and Full Outer JOINs |
| Include multiple input groups | Yes, you can define multiple input groups and one output group | Yes, you can define two input groups, Master and Detail |
| Include heterogeneous sources | Yes | Yes |
| Marge different data types | All of the source columns must have similar data types. The number of columns in each source must be the same | At least one column in the sources to be joined, must have the same data type |
| Generate transactions | No | Yes |
Example of union transformation: An Example of Union transformation shown below. The below mapping, combines all the sources by using Union transformation, also Sorter will delete the duplicates and load the data to target.
Difference between Union and Router transformation:
| Router Transformation | Union Transformation |
|---|---|
| Having one input, one default and mulitple output groups | Having Muliple input groups and one output group |
| Router will split the source data multiple targets | Union will combine multiple sources and send the data single target |
| To join “n” source tables, only one source transformation is required | To join “n” sources, “n-1” joiner transformations required |
| Router is having default group | No default group |
| We should give condtion for each group | No conditions |
Leave a comment