JOINER  TRANSFORMATION

      Joiner Transformation is an Active and Connected transformation. The purpose of joiner transformation is to join two heterogeneous sources like two tables from two different databases or one table and one flat file or two flat files. The Joiner transformation joints two sources based on conditions that include one or more pair of columns between the sources. In the two input source pipelines, one is considered as Master source and another one as Detail source, mostly prefer the input source pipeline which has less number of records or duplicate values as Master pipeline.


The joiner transformation supports the following four types of joins.

• Normal Join
• Master Outer Join
• Details Outer Join
• Full Outer Join

We will learn about each join type with an example. Let consider the following department and employee tables as the source pipelines.

Table Name: DEPARTMENT

DEPART_Id    DEPART_Name
-----------------------------------
1                        Maths
2                        Chemistry
3                        Physics

Table Name: EMPLOYEE

EMPLOYEE_Id    DEPART_Id
-------------------------------------
10                            1
20                            2
30                            NULL

Assume that department source is the master and employee source is the detail and we will join these sources on the department_id port.

Normal Join:

The joiner transformation outputs only the records that match the join condition and discards all the rows that do not match the join condition. The output of the normal join is

Master Outer Join:

In a master outer join, the joiner transformation keeps all the records from the detail source and only the matching rows from the master source. It discards the unmatched rows from the master source. The output of master outer join is

Detail Outer Join:

In a detail outer join, the joiner transformation keeps all the records from the master source and only the matching rows from the detail source. It discards the unmatched rows from the detail source. The output of detail outer join is

Full Outer Join:

The full outer join first brings the matching rows from both the sources and then it also keeps the non-matched records from both the master and detail sources. The output of full outer join is

Limitations of Joiner Transformation

• You cannot use joiner transformation when the input pipeline contains an update strategy transformation.
• You cannot connect a sequence generator transformation directly to the joiner transformation.

Tips to Improve Performance of Joiner Transformation

To improve the performance of a joiner transformation follow the below tips

• If possible, perform joins in a database. Performing joins in a database is faster than performing joins in a session.
• You can improve the session performance by configuring the Sorted Input option in the joiner transformation properties tab.
• Specify the source with fewer rows and with fewer duplicate keys as the master and the other source as detail.

Joiner transformation is called as blocking transformation because, The integration service blocks and unblocks the source data depending on whether the joiner transformation is configured for sorted input or not.

Sorted Joiner Transformation

Blocking logic may or may not possible in case of sorted joiner transformation. The integration service uses blocking logic if it can do so without blocking all sources in the target load order group. Otherwise, it does not use blocking logic.

Unsorted Joiner Transformation

In case of unsorted joiner transformation, the integration service first reads all the master rows before it reads the detail rows. The integration service blocks the detail source while it catches the all the master rows. Once it reads all the master rows, then it unblocks the detail source and reads the details rows.

THANK YOU FOR VISITING MY BLOG!

PREPARE MAPPING, CREATE SESSION, GENERATE WORKFLOW- DAILY, FLOWLY