Union Transformation in Informatica

Data Merging:

Data Merging is a process of combining 2 or more sources data into a single output. Generally, we will use only 2 Transformations in Informatica to perform the process of data merging.

1. Union Transformation
2. Joiner Transformation

Union Informatica Transformation:

• It is active and Connected Transformation
• We say this is an active because while joining the data from various sources there will be change in the total number of records and also changes the order of the rows.
• It is used to combine multiple sources which are having same structure.
• It can receive 'n' number of inputs and provides single output.

Union Transformation in Informatica

Prerequisites for Union Transformation:

All the inputs which is coming from different sources should have the same structure.

• Number of columns in both sources should be same
• Data types of the corresponding columns should be same.
• Order of the columns must be same

Union Transformation is created with the following 2 types of groups.
Input Group: It receives the data from the sources or from other T/Rs
Output Group: It sends the data either to the target or to the other T/Rs

Note:

• By default, Union Transformation is created with a single input group and single output group.
• Number of sources in this is equal to the number of input groups in 'Union Transformation'.
• For every source or input there should be a corresponding input group in "Union Transformation".
• Union Transformation works like 'union all' in Oracle as this will allow duplicate records because we can not eliminate duplicate with this. Don't worry as we have another option to achieve this as mentioned below.
• After this Transformation use Sorter Transformation to eliminate the duplicate records.
• Union Transformation supports both homogeneous sources as well as heterogeneous sources.

Union Transformation in Informatica Scenarios:

1. Design a mapping to combine Cust_Hyd and Cust_Chennai which are having the same structure.

Procedure to combine homogeneous sources:

• Connect to the Scott Schema and create 2 tables with the same structure
• Import the meta data of both the sources using source analyzer and save the meta data by using control+s. This is just a keyboard short cut to save the meta data in informatica
• Create a target with any name by following the naming convention
• Create a mapping with the name related to that mapping.
• From repository drag both the sources and target to design the mapping.
• Create a Transformation of type union with the name union_customer
• Go to the header of union and select groups tab.
• Add the input ports and select the groups tab and add the name of the ports.
• Connect the ports from both the sources to the corresponding groups in the union.
• Create a Transformation of type sorter with the name sort distinct
• Connect the ports from the output group of union to sorter
• Select Distinct property in Sorter Transformation.

• Connect the ports from sorter to the corresponding ports in the target and save the mapping and the remaining process is common. I did not mention remaining process because it is very easy as you are familiar to run the workflows.

If you follow all these steps exactly, you will get the expected result without any failure in the session or workflow and it also takes very less time as it have very less number of records in the sources and targets.

Limitations of union transformation in Informatica:

There are very less limitations while using the union. We can perform union on Relational databases and on flat files also. So it is very useful while performing union in heterogeneous sources also. The one and only limitation is we can use this if we have same structure. It will not work if the source structure is different. If they are different we have to use another Transformation to achieve the result.