• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog
What is Joiner Transformation?

Joiner transformation is an active and connected transformation that provides you the option to create joins in Informatica. The joins created using joiner transformation are similar to the joins in databases. The advantage of joiner transformation is that joins can be created for heterogeneous systems (different databases).

In joiner transformation, there are two sources which we are going to use it for joins. These two sources are called

  • Master Source
  • Detail Source

In the properties of joiner transformation, you can select which data source can be Master and which source can be detail source.

During execution, the master source is cached into the memory for joining purpose. So it is recommended to select the source with less number of records as the master source.

The following joins can be created using joiner transformation

  1. Master outer join

    In Master outer join, all records from the Detail source are returned by the join and only matching rows from the master source are returned.

  2. Detail outer join

    In detail outer join only matching rows are returned from the detail source, and all rows from the master source are returned.

  3. Full outer join

    In full outer join, all records from both the sources are returned. Master outer and Detail outer joins are equivalent to left outer joins in SQL.

  4. Normal join

    In normal join only matching rows are returned from both the sources.

In this example, we will join emp and dept tables using joiner transformation

Step 1 – Create a new target table EMP_DEPTNAME in the database using the below script and import the table in Informatica targets.

Download the above emp_deptname.sql File

Step 2 - Create a new mapping and import source tables "EMP" and "DEPT" and target table which we created in the previous step

Joiner Transformation in Informatica with EXAMPLE

Step 3 – From the transformation menu, select create option.

  1. Select joiner transformation
  2. Enter transformation name "jnr_emp_dept"
  3. Select create option

Joiner Transformation in Informatica with EXAMPLE

Step 4 – Drag and drop all the columns from both the source qualifiers to the joiner transformation

Joiner Transformation in Informatica with EXAMPLE

Step 5 - Double click on the joiner transformation, then in the edit transformation window

  1. Select condition tab
  2. Click on add new condition icon
  3. Select deptno in master and detail columns list

Joiner Transformation in Informatica with EXAMPLE

Step 6 - Then in the same window

  1. Select properties tab
  2. Select normal Join as join type
  3. Select OK Button

Joiner Transformation in Informatica with EXAMPLE

For performance optimization, we assign the master source to the source table pipeline which is having less no of records. To perform this task –

Step 7 –Double click on the joiner transformation to open edit properties window, and then

  1. Select ports tab
  2. Select any column of a particular source which you want to make a master
  3. Select OK

Joiner Transformation in Informatica with EXAMPLE

Step 8 – Link the relevant columns from joiner transformation to target table

Joiner Transformation in Informatica with EXAMPLE

Now save the mapping and execute it after creating session and workflow for it. The join will be created using Informatica joiner, and relevant details will be fetched from both the tables.

 

YOU MIGHT LIKE: