Joiner Transformation in Informatica , is a
connected and
active transformation which let you join data from two heterogeneous source (same source system or different source system). The Joiner transformation joins sources with at least one matching column.
- Joiner Transformation in Informatica
As the name predict itself , the Joiner transformation is use to join data from two heterogeneous sources or data from the same source. The Joiner transformation uses a condition that matches one or more pairs of columns between the two sources. Out of two input , one is consider as master pipeline ( it is loaded first in the data cache) , then the other one is Detail pipeline which is joined with the Master data.
Joiner Transformation is an active transformation as no of output rows changes based on join condition and join type
Joiner Transformation is a Connected Transformation.
Components of Joiner Transformation in Informatica
- Joiner data cache : Joiner always caches the MASTER table. We cannot disable caching. It builds Index cache and Data Cache based on MASTER table.
- Join Type in informatica : With the help of Join Type , we can control the output of the join condition. We will discuss it in more detail.
- Master/Detail source port : While adding source ports into joiner , we need to defined master and detail source by clicking on the Master check box. To make one source as master , we can check any port of the source on the "M" check box, it will automatically mark the check for remaining port as well
- Sorted input in joiner transformation : Select this option to improve session performance. To use sorted input, you must pass data to the Joiner transformation sorted by joining port, in ascending or descending order.
You can configure the Joiner transformation components and options on the Properties and Ports tab.
Normal Join in joiner transformationA normal join will allow only those records which satisfy the joiner condition for both sources. So remaining records, who don't match the condition get discarded
Master Outer Join in joiner transformationA master outer join will keeps all rows of data from the detail source and the matching rows from the master source. If any of master records don't satisfy the condition , those rows get discarded.
Detail Outer Join in joiner transformationJust opposite to Master Outer join , it keep all rows of data from Master Source and the matching rows from Details Source. If any of detail records don't satisfy the condition , those rows get discarded.
Use the following procedure to create an Joiner transformation in Informatica mapping.
Steps to create an Joiner transformation:
- In the Mapping Designer, open a Informatica Mapping.
- Click Transformation > Create. Select Joiner transformation.
You can also select Transformation by clicking function button on Informatica Designer
- Enter a name and click Done.
- Drag all the input/output ports from the first source into the Joiner transformation. The Designer creates input/output ports for the source fields in the Joiner transformation as detail fields by default. You can edit this property later .
- Select and drag all the input/output ports from the second source into the Joiner
transformation.The Designer configures the second set of source fields and master fields by default.
- Double-click the title bar of the Joiner transformation to open the transformation
- Click any box in the M column to switch the master/detail relationship for the sources.
- Tip: To improve performance for an unsorted Joiner transformation, use the source with fewer rows as the master source. To improve performance for a sorted Joiner transformation, use the source with fewer duplicate key values as the master.
- Click the Add button to add a condition. You can add multiple conditions. The master and detail ports must have matching datatypes.
Joiner transformation only supports equivalent (=) joins.
- Click the Properties tab and configure properties for the transformation (as Mentioned above)
- Connect the output ports to a downstream transformation or target.
Example of Joiner Transformation:
