Joiner Transformation in Informatica

·         Active Transformation

·         Joiner relates two or more tables.

·         It allows homogeneous or heterogeneous sources.

·         Join Types

NORMAL (Equijoin – default)

            OUTER

ü  Master Outer

ü  Detail Outer

ü  Full Outer

·         Does not allow non-equi joins.

Join Types

 

Equi-Join

Example

EMP (DETAIL )

Empno ename sal deptno

1 x 2000 10

2 y 3000 20

3 z 1000 10

4 A 3000

DEPT (MASTER)

DEPTNO   DEPTNAME

10              HR

20              FI

30             SALES

Equi-Join  (3 Records)

1 x 2000 10 HR

2 y 3000 20 FI

3 z 1000 10 HR

MASTER OUTER

Equi-Join + Remaining Records of Detail (4 records)

1 x 2000 10 HR

2 y 3000 20 FI

3 z 1000 10 HR

4 A 3000

Detail Outer

Equi-Join + Remaining Records of MASTER (4 records)

1 x 2000 10 HR

2 y 3000 20 FI

3 z 1000 10 HR

                30 SALES

Full Outer

Equi-Join + Remaining Records of MASTER + Remaining Records of Detail (5 records)

1 x 2000 10 HR

2 y 3000 20 FI

3 z 1000 10 HR

                30 SALES

4 A 3000

Interview Question for JOINER

Does JOINER allow non-equi-join?

Ans: NO It does not allow non-equi-joiN.

Create a table DIM_JOIN in the DWH_MEEN schema.

CREATE TABLE DIM_JOIN AS

SELECT * FROM EMP

WHERE 1=2;

Table structure only gets copied.

Alter the table and add a new column DNAME.

SQL> ALTER TABLE DIM_JOIN

         ADD DNAME VARCHAR2(30);

Table altered.

SQL> DESC DIM_JOIN;

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ---------------

 EMPNO                                                          NUMBER(4)

 ENAME                                                          VARCHAR2(10)

 JOB                                                            VARCHAR2(9)

 MGR                                                            NUMBER(4)

 HIREDATE                                                       DATE

 SAL                                                            NUMBER(7,2)

 COMM                                                           NUMBER(7,2)

 DEPTNO                                                         NUMBER(2)

 DNAME                                                          VARCHAR2(30)

SQL> COMMIT;

Import that table in the Warehouse Designer.

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

      9999 MONA       ANALYST         7566 06-AUG-09       2000

14 rows selected.

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        50 SUPPORT        BANGALORE

        60 DISTRIBUTION   DELHI

        70 EDUCATION      CHENNAI

        80 XYZ            ABC

8 rows selected.

STEPS:

1.      Define Sources (EMP,DEPT)

2.      Define Target (DIM_JOIN)

3.      Define Mapping  (M_JOIN)– Drag two sources and one target in Mapping Designer Workspace.

4.      From the Transformation Menu select JOINER and create. T_JOIN

Drag the required ports from two source qualifiers to the JOINER.

Note: The first input to JOINER becomes Master and next Detail. (??? I got the detail as detail and master as master only )

Go to JOINER,edit – go to PORTS tab and select master port for deptno,dname and loc.

Ports: Input,Output,MAster

Go to the Conditions tab and add a new condition.

Go to the Condition Tab and add a condition.

From the master table and detail table select the common column.

(Common column with common data type and size)

We can also add multiple conditions in a JOIN condition.

Properties:

·         Data Cache

·         Index Cache is going to use JOIN condition.

·         Sorted Input

·         Join Type

·         Join Condition

·         Cache Directory

·         Case sensitive string comparison

·         Join Type : NORMAL

·         NULL ordering in MASTER/DETAIL (By default it is high – NULL values will come at the bottom)

     

Project the required ports from JOINER to TARGET.

Validate the mapping.

Repository -> Save

Define session, workflow WF_JOIN.

Start workflow and preview the output.

NOTE: For JOINER and some other transformations, we can edit the properties at the Session    level also.

Edit the Session and go to Mappings tab.

Select Transformations.

Master outer

(Equijoin + Remaining records of Detail Table)

Select the Join Type as Master Outer.

Repository -> Save.

Output (13 + 1)

DETAIL OUTER JOIN

Equijoin + Remaining Records of Master Table

(13 + 5 = 18)

Repository -> Save

Output:

FULL OUTER JOIN

(Equijoin + Remaining Records of Master + Remaining Records of Detail)

(13 + 5 + 1 = 19)

Output: