Union Transformation
Union Transformation in Informatica
Union transformation is an active and connected transformation. It is multi input group transformation used to merge the data from multiple pipelines into a single pipeline. Basically it merges data from multiples sources just like the UNION ALL set operator in SQL. The union transformation does not remove any duplicate rows.
Union Transformation Components
When we configure a Union transformation, define the following components:
Transformation tab: We can rename the transformation and add a description.
Properties tab: We can specify the tracing level.
Groups tab: We can create and delete input groups. The Designer displays groups we create on the Ports tab.
Group Ports tab: We can create and delete ports for the input groups. The Designer displays ports we create on the Ports tab.
We cannot modify the Ports, Initialization Properties, Metadata Extensions, or Port Attribute Definitions tabs in a Union transformation.
Create input groups on the Groups tab, and create ports on the Group Ports tab. We can create one or more input groups on the Groups tab. The Designer creates one output group by default. We cannot edit or delete the default output group.
Example: to combine data of tables EMP_10, EMP_20 and EMP_REST
- Import tables EMP_10, EMP_20 and EMP_REST in shared folder in Sources.
- Create a target table EMP_UNION_EXAMPLE in target designer. Structure should be same EMP table.
- Create the shortcuts in your folder.
Creating Mapping:
- Open folder where we want to create the mapping.
- Click Tools -> Mapping Designer.
- Click Mapping-> Create-> Give mapping name. Ex: m_union_example
- Drag EMP_10, EMP_20 and EMP_REST from source in mapping.
- Click Transformation -> Create -> Select Union from list. Give name and click Create. Now click done.
- Pass ports from SQ_EMP_10 to Union Transformation.
- Edit Union Transformation. Go to Groups Tab
- One group will be already there as we dragged ports from SQ_DEPT_10 to Union Transformation.
- As we have 3 source tables, we 3 need 3 input groups. Click add button to add 2 more groups. See Sample Mapping
- We can also modify ports in ports tab.
- Click Apply -> Ok.
- Drag target table now.
- Connect the output ports from Union to target table.
- Click Mapping -> Validate
- Repository -> Save
- Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.
- Make sure to give connection information for all 3 source Tables.
![clip_image002[5]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRYaN7bBjtq-Ax9h2hCN3yvjPIXwG9Gc6I2uux1BtaRUefWogmexmhJhav3u87VINOQR6VP9sz3r_Bt6JYihQpjugc2BhgMEEwswVmJK50QMoU48m-Zx3Araou3GyVhp8plRAZOB53FIXf/s1600-h/clip_image002%5B5%5D%5B3%5D.jpg "clip_image002[5]")
Union Transformation Guidelines
The following rules and guidelines should be used when using a union transformation in a mapping
- Union transformation contains only one output group and can have multiple input groups.
- The input groups and output groups should have matching ports. The datatype, precision and scale must be same.
- Union transformation does not remove duplicates. To remove the duplicate rows use sorter transformation with "select distinct" option after the union transformation.
- The union transformation does not generate transactions.
- You cannot connect a sequence generator transformation to the union transformation.
- Union transformation does not generate transactions.
- we can connect heterogeneous sources to a Union transformation.
Creating union transformation
Follow the below steps to create a union transformation
- Go the mapping designer, create a new mapping or open an existing mapping
- Go to the toolbar-> click on Transformations->Create
- Select the union transformation and enter the name. Now click on Done and then click on OK.
- Go to the Groups Tab and then add a group for each source you want to merge.
- Go to the Group Ports Tab and add the ports.
Components of union transformation
Configure the following tabs of union transformation
- Transformation: You can enter name and description of the transformation
- Properties: Specify the amount of tracing level to be tracked in the session log.
- Groups Tab: You can create new input groups or delete existing input groups.
- Group Ports Tab: You can create and delete ports for the input groups.
Note: The ports tab displays the groups and ports you create. You cannot edit the port or group information in the ports tab. To do changes use the groups tab and group ports tab.
Why union transformation is active
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 is the same as the total number of rows passing out of it, and the sequence of rows from any given input stream is preserved in the output, the positions of the rows are not preserved, i.e. row number 1 from input stream 1 might not be row number 1 in the output stream. Union does not even guarantee that the output is repeatable.
Union Transformation Example
1. There are two tables in the source. The table names are employees_US and employees_UK and have the structure. Create a mapping to load the data of these two tables into single target table employees?
Share This:
Tharun Katanguru
I'm Tharun Katanguru. A full time web designer and Data Engineer. I enjoy to make modern template. I love create blogger template and write about Web Design, Data Warehousing and Data Management. Now I'm working as a Jr Etl Developer.
No Comment to " Union Transformation "