Tutorial corner

Informatica,ETL,oracle,sql/plsql

Joiner Transformation example in Informatica

Spread the love

Joiner Transformation example in Informatica :  We checked Joiner transformation in informatica , steps to create it , configuring the port and join type. We also discussed the business scenario  of Joiner Transformation in Informatica. Lets see a working Example of Joiner Transformation in Informatica mapping

Joiner Transformation example

Joiner Transformation example

.

Joiner Transformation example in Informatica

Create an informatica mapping to join the data of  EMP and DEPT tables and also shows the output for the below type of Join

  • Normal Join
  • Master Outer Join
  • Detail Outer Join
  • Full Outer Join

Source Data:

EMP Table Data:

EMPNO ENAME DEPT

4

HOOVER

5

LINCOLN

4

8

GRANT

5

9

JACKSON

4

10

FILLMORE

2

14

ROOSEVELT

1

DEPT Table Data

DEPTNO DNAME

1

ACCOUNTING

2

RESEARCH

3

SALES

4

OPERATIONS

Check here for Aggregator transformation example in informatica

Solution:

Below are the steps to create full mapping.

  • Create a new mapping m_joiner_example by Go to toolbar -> click mapping-> Create
  • Drag Source (EMP, DEPT) and Target (EMP_DEPT) to the mapping.
  • Add Joiner Transformation by “Go to Toolbar->click Transformation -> Create. Select the Joiner transformation”

    Joiner Transformation steps 2

    Joiner Transformation steps 2

  • Enter a name like jnr_EMP_DEPT, click on Create and then click on Done.
Joiner Transformation steps 3

Joiner Transformation steps 3

  • Drag all the output ports from the first source SQ_EMP  into  jnr_EMP_DEPT 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 SQ_DEPT into jnr_EMP_DEPT Joiner  transformation.
  • The designer configures the second set of source fields and master fields by default.
  • Double-click the title bar of jnr_EMP_DEPT  Joiner transformation to open the transformation
  • Click on any port from EMP source  in the M column , to mark it as Master . By doing this ports of SQ_DEPT will be treated as Detail source automatically
Joiner Transformation steps 5

Joiner Transformation steps 5

  • Now go to the condition Tab and click on   to create new condition new-in-Informatica . Select DEPT column in Master dropdown  list and DEPTNO in Detail dropdown  list. Also select the equal operator “=”  from operator dropdown list. This way we can add multiple condition to join  two source.
Joiner Transformation steps 6

Joiner Transformation steps 6

  • Click the Properties tab and configure properties for the transformation and change the join Type as per your need. If you are providing sorted data ( by Source Qualifier query or using Sorter transformation before it)  from each source (sorted on the column which are part of joining condition) then we can mark Sorted Input  option  of joiner to boost the performance of it.
Joiner Transformation steps 7

Joiner Transformation steps 7

  • Now join the output port of jnr_EMP_DEPT   with the EMP_DEPT Target.
  • Click on Mapping ( from tool bar) -> then Validate ( to validate the mapping)
  • Now save the mapping ( by clicking on Repository-> Save or you can also press Ctrl+S)
  • Generate the workflow and run it

Over all Mapping:

Joiner Transformation Full Mapping

Joiner Transformation Full Mapping

Output Data

Depending upon the Join Type in the properties tab of the jnr_EMP_DEPT   joiner Transformation , we can have below output data

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. Below will be the final output as per our given input

EMPNO ENAME DEPT DEPTNO DNAME

14

ROOSEVELT

1

1

ACCOUNTING

10

FILLMORE

2

2

RESEARCH

9

JACKSON

4

4

OPERATIONS

5

LINCOLN

4

4

OPERATIONS

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.

EMPNO ENAME DEPTNO DNAME

14

ROOSEVELT

1

ACCOUNTING

10

FILLMORE

2

RESEARCH

8

GRANT

2

RESEARCH

3

SALES

9

JACKSON

4

OPERATIONS

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.

EMPNO ENAME DEPT DEPTNO DNAME

14

ROOSEVELT

1

1

ACCOUNTING

10

FILLMORE

2

2

RESEARCH

9

JACKSON

4

4

OPERATIONS

5

LINCOLN

4

4

OPERATIONS

4

HOOVER

8

GRANT

5

Check here for filter transformation example in informatica

Full Outer Join

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

EMPNO ENAME DEPTNO DNAME

14

ROOSEVELT

1

ACCOUNTING

10

FILLMORE

2

RESEARCH

3

SALES

9

JACKSON

4

OPERATIONS

5

LINCOLN

4

OPERATIONS

4

HOOVER

8

GRANT

So, in this article we have gone through step by step Example of Joiner Transformation in Informatica mapping,  learnt the  step to add it , configure the port , joining condition and various type of join type . 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