All about Union Transformation.

The Union transformation is an active transformation in informatica Powercenter used to merge data from multiple pipelines into a single pipeline as output. A common data integration pattern is to combine two or more than two data sources into a single stream that contains a join of all rows. Because data sources often do not have the same structure, data streams cannot be freely combined. The Union transformation in Informatica Powercenter allows you to combine data sources into a single destination by adjusting stream metadata. The Union transformation merges data from multiple sources, similar to the UNION ALL SQL statement. For example, you can use the Union transformation to merge employee information from ADP with data from a Workday employee object. You can add, change, or remove specific fields when combining data sources with the Union transformation. At runtime, the mapping task processes input groups in parallel. At the same time, it reads the source associated with the union transformation and moves the data block to the input group of the transformation. When the mapping is executed, the data is merged into a single output group based on the field mapping.

Rules and Guidelines associated with Union Transformations

Use the following rules and guidelines when using Union transformations:

You can create multiple input groups, but only one output group.

All input and output groups must have matching ports. Precision, data type, and scaling must be the same for all groups.

Union transformation does not remove duplicate rows. To remove duplicate rows, you need to add another transformation like a Router or filter conversion.

You cannot use the Sequence Generator or Update Strategy transformations before the Union transformation.

Union transformations do not generate transactions.

Components of Union Transformation

Transformation tab:  You can rename the transformation and add a description.

Properties tab: Tracing level can be specified.

Groups tab: Input groups can be created and deleted. Ports tab can be created in Designer displays groups.

Group Ports Tab: Create and delete ports for the input groups can be created. Ports tab can be created in Designer displays groups.

Working with Input Group in Union Transformation.

By default, the Union transform has two input groups. If you want to merge data from more than two sources, add an input group for each additional source. Each group can have different field rules for each upstream transformation in informatica Powercenter.

Input groups have the following characteristics:

It initializes the output fields based on the fields of the first source that connects the input group.

Each input group can use different field mapping modes or parameters.

You can parameterize field mappings or define field mappings for each input group.

To add an input group, in the Mapping Designer, connect the upstream transform to the new group in the union transform. You can also add input groups on the Incoming Fields tab of the Union transform.

Working with Output Group in Union Transformation.

Connecting an upstream transform to a union transform initializes the output fields. The first output field is an exact copy of the input field in the Input1 group.

When defining output fields, keep the following in mind:

After you initialize the output field, you can no longer connect or disconnect input groups to change the output field.

You can add output fields manually by adding them before connecting any Union Transform input groups.

When you add an output field, define the field name, data type, precision, scale, and optional description. The description can contain up to 4000 characters.

If you connect a Union transformation to an upstream transformation that does not pass fields, the output fields are not initialized.

At runtime, the map passes null values ​​to output fields that are not in the field map.

Field mappings in Union Transformation:

The Union transformation can merge data from multiple source pipelines. Sources can use the same set of fields, some matching fields, or parameterized field mapping.

When working with field mappings in Union transformations, keep the following in mind:

You must use input groups whose fields have the same name, type, precision, and scale.

Some output fields can be manually edited, deleted, or added.

As part of field mapping, select an input group and specify parameters from the input group.

You can use parameters for fields in all input groups. Field mapping for each input group can be parameterized or mapped by field name. At runtime, the task adds exact copies of the input group's fields as output fields.

Advanced properties of UNION transformation:

User can configure advanced properties for the Union transformation. Advanced properties control settings such as the tracing level for session log messages and whether conversion is optional or required.

Tracing Level: The level of detail for error and status messages that Data Integration writes to the session log. You can choose between sparse, normal, redundant initialization, and redundant data. Default is normal.

Optional: Determines whether transformation is optional or not. If the transformation is optional and there are no incoming fields, you can perform a mapping task and the data can pass through another branch in the data flow. If a conversion is required and the incoming field is missing, the task will fail. For example, configure source connection parameters. In a branch of the data flow, add a transformation using a field rule so that only date/time data is included in the transformation, and specify that the transformation is optional. When configuring a mapping task, select a source that does not contain date/time data. The mapping task ignores the branch containing the optional transformation, and data flow continues through another branch of the mapping.