Joiner Transformation in Informatica

  • Updated
  • Posted in Programming
  • 7 mins read


The joiner transformation is an lively and related transformation used to hitch two heterogeneous sources. The joiner transformation joins sources primarily based on a situation that matches a number of pairs of columns between the 2 sources. The two enter pipelines embrace a grasp and a element pipeline or department. To be part of greater than two sources, that you must be part of the output of the joiner transformation with one other supply. To be part of n variety of sources in a mapping, you want n-1 joiner transformations.

Creating Joiner Transformation

Follow the beneath steps to create a joiner transformation in informatica

  • Go to the mapping designer, click on on the Transformation->Create.
  • Select the joiner transformation, enter a reputation and click on on OK.
  • Drag the ports from the primary supply into the joiner transformation. By default the designer creates the enter/output ports for the supply fields in the joiner transformation as element fields.
  • Now drag the ports from the second supply into the joiner transformation. By default the designer configures the second supply ports as grasp fields.
  • Edit the joiner transformation, go the ports tab and examine on any field in the M column to change the grasp/element relationship for the sources.
  • Go to the situation tab, click on on the Add button so as to add a situation. You can add a number of circumstances.
  • Go to the properties tab and configure the properties of the joiner transformation.

Configuring Joiner Transformation

Configure the next properties of joiner transformation:

  • Case-Sensitive String Comparison: When performing joins on string columns, the combination service makes use of this feature. By default the case delicate string comparability possibility is checked.
  • Cache Directory: Directory used to cache the grasp or element rows. The default listing path is $PMCacheDir. You can override this worth.
  • Join Type: The kind of be part of to be carried out. Normal Join, Master Outer Join, Detail Outer Join or Full Outer Join.
  • Tracing Level: Level of tracing to be tracked in the session log file.
  • Joiner Data Cache Size: Size of the info cache. The default worth is Auto.
  • Joiner Index Cache Size: Size of the index cache. The default worth is Auto.
  • Sorted Input: If the enter knowledge is in sorted order, then examine this feature for higher efficiency.
  • Master Sort Order: Sort order of the grasp supply knowledge. Choose Ascending if the grasp supply knowledge is sorted in ascending order. You need to allow Sorted Input possibility in the event you select Ascending. The default worth for this feature is Auto.
  • Transformation Scope: You can select the transformation scope as All Input or Row.

Join Condition

The integration service joins each the enter sources primarily based on the be part of situation. The be part of situation accommodates ports from each the enter sources that should match. You can specify solely the equal (=) operator between the be part of columns. Other operators should not allowed in the be part of situation. As an instance, if you wish to be part of the workers and departments desk then it’s a must to specify the be part of situation as department_id1= department_id. Here department_id1 is the port of departments supply and department_id is the port of workers supply.

Join Type

The joiner transformation helps the next 4 sorts of joins.

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

We will study every be part of kind with an instance. Let say i’ve the next college students and topics tables because the supply.

Table Name: Subjects
Subject_Id subject_Name
-----------------------
1 Maths
2 Chemistry
3 Physics

Table Name: Students
Student_Id Subject_Id
---------------------
10 1
20 2
30 NULL

Assume that topics supply is the grasp and college students supply is the element and we are going to be part of these sources on the subject_id port.

Normal Join:

The joiner transformation outputs solely the information that match the be part of situation and discards all of the rows that don’t match the be part of situation. The output of the traditional be part of is

Master Ports       |   Detail Ports
---------------------------------------------
Subject_Id Subject_Name Student_Id Subject_Id
---------------------------------------------
1 Maths 10 1
2 Chemistry 20 2

Master Outer Join:

In a grasp outer be part of, the joiner transformation retains all of the information from the element supply and solely the matching rows from the grasp supply. It discards the unequalled rows from the grasp supply. The output of grasp outer be part of is

Master Ports       |   Detail Ports
---------------------------------------------
Subject_Id Subject_Name Student_Id Subject_Id
---------------------------------------------
1 Maths 10 1
2 Chemistry 20 2
NULL NULL 30 NULL

Detail Outer Join:

In a element outer be part of, the joiner transformation retains all of the information from the grasp supply and solely the matching rows from the element supply. It discards the unequalled rows from the element supply. The output of element outer be part of is

Master Ports       |   Detail Ports
---------------------------------------------
Subject_Id Subject_Name Student_Id Subject_Id
---------------------------------------------
1 Maths 10 1
2 Chemistry 20 2
3 Physics NULL NULL

Full Outer Join:

The full outer be part of first brings the matching rows from each the sources after which it additionally retains the non-matched information from each the grasp and element sources. The output of full outer be part of is

Master Ports       |   Detail Ports
---------------------------------------------
Subject_Id Subject_Name Student_Id Subject_Id
---------------------------------------------
1 Maths 10 1
2 Chemistry 20 2
3 Physics NULL NULL
NULL NULL 30 NULL

Sorted Input

Use the sorted enter possibility in the joiner properties tab when each the grasp and element are sorted on the ports specified in the be part of situation. You can enhance the efficiency by utilizing the sorted enter possibility as the combination service performs the be part of by minimizing the variety of disk IOs. you may see good efficiency when labored with giant knowledge units.

Steps to comply with for configuring the sorted enter possibility

  • Sort the grasp and element supply both by utilizing the supply qualifier transformation or sorter transformation.
  • Sort each the supply on the ports for use in be part of situation both in ascending or descending order.
  • Specify the Sorted Input possibility in the joiner transformation properties tab.

Why joiner transformation is named as blocking transformation

The integration service blocks and unblocks the supply knowledge relying on whether or not the joiner transformation is configured for sorted enter or not.

Unsorted Joiner Transformation

In case of unsorted joiner transformation, the combination service first reads all of the grasp rows earlier than it reads the element rows. The integration service blocks the element supply whereas it caches the all of the grasp rows. Once it reads all of the grasp rows, then it unblocks the element supply and reads the main points rows.

Sorted Joiner Transformation

Blocking logic could or could not doable in case of sorted joiner transformation. The integration service makes use of blocking logic if it will possibly accomplish that with out blocking all sources in the goal load order group. Otherwise, it doesn’t use blocking logic.

Joiner Transformation Performance Improve Tips

To enhance the efficiency of a joiner transformation comply with the beneath ideas

  • If doable, carry out joins in a database. Performing joins in a database is quicker than performing joins in a session.
  • You can enhance the session efficiency by configuring the Sorted Input possibility in the joiner transformation properties tab.
  • Specify the supply with fewer rows and with fewer duplicate keys because the grasp and the opposite supply as element.

Limitations of Joiner Transformation

The limitations of joiner transformation are

  • You can not use joiner transformation when the enter pipeline accommodates an replace technique transformation.
  • You can not join a sequence generator transformation on to the joiner transformation.

Recommended Reading:


SQL Transformation
Informatica Scenario Based Questions
Rank Transformation in Informatica
Aggregator Transformation in Informatica
Source Qualifier Transformation in Informatica

If you want this text, then please share it or click on on the google +1 button.

Leave a Reply