✍ Kashif M
Trends

Saturday, 15 February 2014


Joiner Transformation in Informatica


By on February 15, 2014




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

Kashif
mkashu: Joiner Transformation in Informatica
Review : Kashif | Kashif
Update: February 15, 2014 | Rating: 4.5

Comment for "Joiner Transformation in Informatica"

0 comments

Post a Comment

Blog Archive