Joiner Transformation(1)

来源：互联网发布：软件压力测试工具编辑：程序博客网时间：2024/06/06 03:15

Joiner Transformation

The joiner transformation is an active and connected transformation used to join two heterogeneous sources. The joiner transformation joins sources based on a condition that matches one or more pairs of columns between the two sources. The two input pipelines include a master and a detail pipeline or branch. To join more than two sources, you need to join the output of the joiner transformation with another source. To join n number of sources in a mapping, you need n-1 joiner transformations.

Creating Joiner Transformation

Follow the below steps to create a joiner transformation in informatica

Go to the mapping designer, click on the Transformation->Create.
Select the joiner transformation, enter a name and click on OK.
Drag the ports from the first source into the joiner transformation. By default the designer creates the input/output ports for the source fields in the joiner transformation as detail fields.
Now drag the ports from the second source into the joiner transformation. By default the designer configures the second source ports as master fields.
Edit the joiner transformation, go the ports tab and check on any box in the M column to switch the master/detail relationship for the sources.
Go to the condition tab, click on the Add button to add a condition. You can add multiple conditions.
Go to the properties tab and configure the properties of the joiner transformation.

Configuring Joiner Transformation

Configure the following properties of joiner transformation:

Case-Sensitive String Comparison: When performing joins on string columns, the integration service uses this option. By default the case sensitive string comparison option is checked.
Cache Directory: Directory used to cache the master or detail rows. The default directory path is $PMCacheDir. You can override this value.
Join Type: The type of join to be performed. 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 data cache. The default value is Auto.
Joiner Index Cache Size: Size of the index cache. The default value is Auto.
Sorted Input: If the input data is in sorted order, then check this option for better performance.
Master Sort Order: Sort order of the master source data. Choose Ascending if the master source data is sorted in ascending order. You have to enable Sorted Input option if you choose Ascending. The default value for this option is Auto.
Transformation Scope: You can choose the transformation scope as All Input or Row.

Join Condition

The integration service joins both the input sources based on the join condition. The join condition contains ports from both the input sources that must match. You can specify only the equal (=) operator between the join columns. Other operators are not allowed in the join condition. As an example, if you want to join the employees and departments table then you have to specify the join condition as department_id1= department_id. Here department_id1 is the port of departments source and department_id is the port of employees source.

Join Type

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 say i have the following students and subjects tables as the source.

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 subjects source is the master and students source is the detail and we will join these sources on the subject_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

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

Limitations of Joiner Transformation

The limitations of joiner transformation are

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.

0 0