Step 1: Get an EMP source table:
This table available in the scott user:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
Step 2 get a target table for the same(note this has a surrogate key newly created:)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SK
SQL>: Create table empscd1 as select * from scott.emp;( you might have to grant create permission for the scott user incase not working).
SQL>: alter table t_emp add sk number(15) primary key;
Now, we have the source and the target tables created.
Lets start off with the mapping.
Step 3: In the informatica designer get the source and the target tables that we just created above.
Step 4: Now, get a look up and select the target table to look up.
Step 5: Drag and drop all the source tables to the look up transformation.(now, we have the target tables rows at the top of the look up and
the source rows the lower side in the look up transformation)
Step6: Get an expression transformation and connect all the rows in the look up transformation to the expression transformation. Now, you
need to add two more columns here
1. Insert_flg of integer(15) type: Give the expression for this as: IIF(ISNULL(SK) OR ISNULL(EMPNO),1,0)
2. Update_flg of integer(15) type: Give the expression for this as: IIF(NOT ISNULL(SK) and
(
( ENAME != ENAME1 ) OR
( JOB != JOB1 ) OR
( MGR != MGR1 ) OR
( HIREDATE != HIREDATE1 ) OR
( SAL != SAL1 ) OR
( COMM != COMM1 ) OR
( DEPTNO != DEPTNO1 )
) ,1,0 )
Step 7: Get a router transformation and connect the source rows(the rows that have "1" postfixed) to it. Makesure the insert_flg and update
flg columns that you created also is connected for the same and give group names for this to create two groups as follows
Group 1. Insert_rows: Group filter condition for this: Insert_flg
Group 2. Update_rows: Group filter condition for this: Update_flg
Step 8: Now connect the insert_rows groups of the router to the target. Note: here you should not connect the SK column from the source
/router transformation, but you need to use a sequence generator tranformation and connect the nextval column to the SK of the target as this
sequence needs to be updated to the next value whenever a new row gets added.
Step 9: Connect the update_rows of the router tranformation to an update strategy tranformation with a transformation value of DD_UPDATE and
then connect to the Target instance 2( Note : target instance2 is nothing but a copy paste of the target table and is not generated at the
target DB).
STep 10: Now create a workflow and the task for the same and run the transformation.
Make sure you commit the changes made on the source side :-)
The logic goes very simple:
1. First the lookup will look up in the cache table for a given row for existence.
2. If the SK does not exist, then it will go ahead and update the row in the target/Dimension table.
3. After this the sequence generator will be updated to the next value WRT the target/Dimension table.
4. If the SK exist then the condition will point to the update_flg and will do a DD_UPDATE of the corresponding row in the target table.
5. Now, the same process will continue with the next row onwards.
6. Note the SK in the target table should be a primary key with out fail.
