Tutorial corner

Informatica,ETL,oracle,sql/plsql

Joiner Transformation in Informatica with example

Spread the love

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). Unlike normal Database joins , we can join data from file system as well. The Joiner transformation joins sources  with at least one matching column.In below article we will go through the properties of Joiner Transformation. We will also discuss the steps of adding /configuring Joiner  transformation in Informatica Mapping. We will also check the some performance tuning related guidelines for Joiner Transformation.

Joiner Transformation in Informatica

Joiner Transformation in Informatica

Business Scenarios of Joiner Transformation :

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.

Note: Joiner can join only two source system , if we need to join N sources then we need to use N-1 Joiner to join them.

Is Joiner Transformation Active or Passive ?

Joiner Transformation is an active transformation as no of output rows changes  based on join condition and join type

Is Joiner Transformation Connected or Unconnected ?

Joiner Transformation is a Connected Transformation.

Properties of Joiner Transformation in Informatica

  • Active /Passive : Joiner Transformation is an active transformation as no of output rows changes  based on join condition and join type
  • Connected/Unconnected Transformation : Joiner Transformation is a connected Transformation.
  • Types of ports in Joiner Transformation:
    • Input : For reading input Data
    • Output : For providing output Data
    • Additionally there is an Master check box which enable you to identify the  Master Pipeline and Detail Pipeline

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 :  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 :  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.

Join Type of Joiner Transformation

Normal Join
A 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
A 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
Just 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.

Full Outer Join
A full outer join keeps all rows of data from both the master and detail sources.

Configuring Joiner Transformation Ports:

You can configure the following components on the ports tab

  • Port name:  Add the name of the port.
  • Datatype, precision, and scale:  Configure the datatype and set the precision and scale for each port.
  • Port type : A port can be input, output. There is no change in the data , so both Input/Output check is marked always.
  • Master Port:  Use “M” check box to identify master source. You can make any source as master by checking any port of it.

Configuring Joiner Transformation Properties:

Modify the Transformation properties by clicking on the Properties tab.

Property Description
Case-Sensitive String Comparison If selected, the Integration Service uses case-sensitive string comparisons when performing joins on string columns.
Cache Directory Specifies the directory used to cache master or detail rows and the index to these rows. By default, the cache files are created in a directory specified by the process variable $PMCacheDir. If you override the directory, make sure the directory exists and contains enough disk space for the cache files. The directory can be a mapped or mounted drive.
Join Type Specifies the type of join: Normal, Master Outer, Detail Outer, or Full Outer.
Null Ordering in Master Not applicable for this transformation type.
Null Ordering in Detail Not applicable for this transformation type.
Tracing Level Amount of detail displayed in the session log for this transformation. The options are Terse, Normal, Verbose Data, and Verbose Initialization.
Joiner Data Cache Size Data cache size for the transformation. Default cache size is 2,000,000 bytes. If the total configured cache size is 2 GB or more, you must run the session on a 64-bit Integration Service. You can configure a numeric value, or you can configure the Integration Service to determine the cache size at runtime. If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of memory for the Integration Service to allocate to the cache.
Joiner Index Cache Size Index cache size for the transformation. Default cache size is 1,000,000 bytes. If the total configured cache size is 2 GB or more, you must run the session on a 64-bit Integration Service. You can configure a numeric   value, or you can configure the Integration Service to determine the cache size at runtime. If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of  memory for the Integration Service to allocate to the cache
 Sorted Input Specifies that data is sorted. Choose Sorted Input to join sorted data. Using sorted input can improve performance
Master Sort Order  Specifies the sort order of the master source data. Choose Ascending if the master source data is in ascending order. If you choose Ascending, also enable sorted input. Default is Auto.
Transformation Scope Specifies how the Integration Service applies the transformation logic to incoming data. You can choose Transaction, All Input, or Row.

Must Read : Learn more about Expression Transformation

How to use Joiner transformation in Informatica ?

Use the following procedure to create an Joiner transformation in Informatica mapping.

Steps to create an Joiner transformation:

  1. In the Mapping Designer, open a Informatica Mapping.
  2. Click Transformation > Create. Select Joiner transformation.
    Joiner Transformation steps 2

    Joiner Transformation steps 2

    You can also select Transformation by clicking  function  button on Informatica Designer

    Joiner Transformation Shortcut

    Joiner Transformation Shortcut

  3. Enter a name and click Done.
    Joiner Transformation steps 3

    Joiner Transformation steps 3

  4. 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 .
  5. 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.
  6. Double-click the title bar of the Joiner transformation to open the transformation
  7. Click any box in the M column to switch the master/detail relationship for the sources.
  8. 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.
    Joiner Transformation steps 5

    Joiner Transformation steps 5

  9. Click the Add button to add a condition. You can add multiple conditions. The master and detail ports must have matching datatypes.
    Joiner Transformation steps 6

    Joiner Transformation steps 6

    Joiner transformation only supports equivalent (=) joins.

  10. Click the Properties tab and configure properties for the transformation (as Mentioned above)
    Joiner Transformation steps 7

    Joiner Transformation steps 7

  11. Connect the output ports to a downstream transformation or target.

Example  of Joiner Transformation:

Check  example of Joiner Transformation in informatica

More about Joiner Transformation:

Joiner Transformation Performance Tuning :

  • Use Sorter Transformation before Joiner Transformation to improve the session performance.
  • Use required data port only in Joiner to reduce the size of  data cache which hold this data.
  • Try to use Database join directly via source qualifier as it gives better performance.
  • Source with with fewer rows and with fewer duplicate keys should be consider as the master and the other source as detail.

Limitation of Joiner Transformation:

  • We cannot use Joiner if any input  pipeline contain update strategy transformation
  • We cannot use sequence generator directly before the joiner Transformation

So , in this article we have gone through properties of Joiner Transformation ,component/Properties of Joiner Transformation , scenarios where to use it , steps to create it and  performance related guidelines. Hope you enjoyed the article. Please feel free to give your feedback.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme