Joiner Transformation in Informatica is a Active and connected transformation used to join two heterogeneous sources. Joins are of two types : Homogeneous Join and Heterogeneous Join.
Homogeneous Join : Homogeneous Join will be created using Source qualifier and Joiner Transformation. But best practice is Source Qualifier.
Example : 2 database tables in same database
Heterogeneous Join : Heterogeneous Join need to be created using Joiner Transformation.
Example : An Oracle table and a DB2 table, A flat file and database table, Two flat files.
If you can perform a join on the source database, then you can configure it in the Source Qualifier. The SQL that the source qualifier generated, default or custom, executes on the source database at run time. Example : homogeneous join : 2 database tables in same database. If you cannot perform a join on the source database, then you cannot configure in the source Qualifier.
The Joiner Transformation in Informatica supports the following types of Joins.
- Normal>>>EQUI JOIN.
- Master outer >>> EQUI JOIN + remaining records from detail table.
- Detail Outer >>>> EQUI JOIN + remaining records from master table.
- Full Outer >>>EQUI JOIN + Remaining records form Both Tables.
Normal Join : With a normal join, the Integration Service discards all rows of data from the master and detail source that do not match, based on the condition. Master Outer : A master outer join keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source. Detail Outer : A detail outer join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source. Full Outer Join : A full outer join keeps all rows of data from both the master and detail sources.
Note : A normal or master outer join performs faster than a full outer or detail outer join. The Joiner transformation does not match null values. For example, if both EMP_ID1and EMP_ID2 contain a row with a null value, the Integration Service does not consider them a match and does not join the two rows. To join rows with null values, replace null input with default values, and then join on the default values.
Creating Joiner Transformation in Informatica
In this Informatica tutorial, we are going to create Joiner Transformation by joining two tables using single source qualifier. In our first step, we must create Target table (JOINNER_EMP) in Informatica PowerCenter Designer. Follow the steps given below :
- Open Informatica PowerCenter Designer tool and click on connect button. Now enter PowerCenter username and password.
- Go to tools | Target designer | Create.
- Enter name for the target table and select database.
- Now double click on target table to add columns to the table.
- Add EmpNo, Dname, Jon, Sal, DeptNo as shown below.
- Click on Ok button
- Now enter filename and click on connect button.
- Enter database username, password to connect.
- Click on generate and execute button to run SQL script as shown below.
Create mapping for Joiner Transformation.
For every Transformation, mapping must be created to add source and target table. Go to mappings menu | click on create button and enter new Mapping name as m_SQ_HOMOGENEOUS_JOIN.
- Import Joiner_emp table as Target.
Drag and drop Source tables EMP and DEPT table and target table JOINER_EMP into the mapping window workspace. In this Joiner Transformation, both source tables are coming from same database, so we don’t need two separate source Qualifiers. Delete any one source Qualifier.
- Double click on SQ_EMP header | Properties | Click on rename button to rename the table as SQ_EMPDEPT.
- Drag and drop DeptNo, Dname, LOC from DEPT table to SQ_EMPDEPT
- Map the corresponding ports from SQ_EMPDEPT to JOINER_EMP table as shown below :
- Now, double click on SQ_EMPDEPT transformation | In that click on Properties tab | In Sql Query option click on corresponding down arrow mark.
- Now select ODBC datasource as SCOTT_ODBC_SOURCE from the drop down menu.
- Enter username : SCOTT.
- Password : Admin12345.
Click on Generate SQL button, now automatically one query will populated as shown below.
- Click on Ok button.
Create Workflow for Joiner Transformation
The Remaining process to create Session, to create Workflow and running the Workflow is same here also. After ran the workflow, go to target table and preview the data whether it is populated correctly or not.
- In Workflow manager, navigate to workflow and start workflow.
Informatica Workflow monitor tool will be opened and check the workflow status.
Output : Joiner Transformation in Informatica.
To check the output like applied rows, affected rows and rejected rows, login to your database and open target table JOINER_EMP.
