Designing and mapping with SED implementation in Informatica

Design a mapping with SED type 1 implementation:

       A Type one dimension keeps only current  data in the target. It doesn’t any history.

Procedure:

Create a source definition with name EMP

Create a target definition with the name  EMP –DIM – Type1(Emp key, Emp no, tname, job,sal)

Note:

Emp key should be primary key

Creating a mapping with the name M_ EMPLOYEE _ DIM_Type1 drop the source definition Emp

Drop the target definition as a 2 instances

1. EMP_ DIM_Type1(insert)
2. EMP_ DIM_Type1(update)

From transformation menu select create

Select the transformation type look up

Enter the name LKP_ TRG click on create

From target select the table EMP _DIM_Type1 click ok

Click done

From source qualifier (SQ- EMP) copy the port empno to the look transformation.

Double click on the look up transformation select condition tab from tool brace click on add a new condition.

Click apply ,click ok

Create the transformation type expression transformation (Exe _src_lookup)

From SQ_ EMP copy the following ports to expression transformation empno, ename, job, sal

From look up transformation copy the port emp key to the expression transformation.

Double click on expression transformation select the ports tab

Create the transformation type router

From expression transformation copy the port to the router transformation ,couble click on router transformation select group tab

Click apply and click ok

Defining new records data flow:

Create the transformation type expression , update strategy and sequence generator

From router transformation , from new record output group copy the ports to the expression transformation (emp no, Ename, job,sal) from expression transformation copy the ports to update strategy transformation.

Double click on update strategy transformation select properties tab

Click apply and click ok

From updating transformation connect the ports to the target , from sequence generator transformation connect the net work port to the EMP key of target table.

Interested in mastering Informatica Training? Enroll now for FREE demo on Informatica Training Online.

Defining update record data flow:

Create the transformation type expression and update strategy from router transformation , from update record output group copy the following ports to the expression transformation (Emp key, emp no, ename, job,sal)

From expression transformation copy the ports to the update strategy transformation.

Double click on update strategy transformation select properties tab

From update strategy transformation connect the ports to the target from repository menu click on save.

Create a session with the name S_M _EMPLOYEE_DIM_Type1, double click the session select the properties tab

Select the mapping tab set the reader and writer connections, click apply and click ok

SQL> update emp set sal =sal +11000 where empno=10;

SQL> commit;

Design a mapping with SED type2 implementation:

A type 2 dimension stores complete history in the target for each update at insert a new record in the target.

There are 3 different  methods to maintain the history

1. Keep the version number in a separate column
2. Mark the current dimension record with the flag
3. Use start date and end date [date range]

Procedure:

Create a source definition with the name EMP

Create a target definition with the name EMP- type2 (emp key, emp no, enmae, sal, job, deptno, version)

Create a mapping with the name M_EMP_DIM_TYPE2

Drop the source definition

Drop the target definition as a 2 instances

1. EMP_type2(new record to insert)
2. EMP _type2 (update record as insert)

Transformation menu select create select the transformation type look up enter name LKP – TRG click on create

From target select table  EMP- type2 click ok click on done

From SQ- EMP copy the port EMPNO to the look up transformation

Double click on the lookup transformation select the condition tab tool bar click on add a new condition.

Click apply and click ok

Create the transformation type expression from source qualifier, copy the following ports to the expression transformation

From look up transformation copy the following ports to expression transformation(EMP key,sal,version)

Double click on expression T/R select ports tab

Click apply and click ok

Create the transformation type router from expression T/R, copy the following ports to the router transformation(EMPno, Ename,job,sal,deptno,version,new-flag,update-flag)

Double click the router T/R select the groups tab

Click apply and click ok

Defining new record dataflow:

Create the transformation type expression, update strategy and sequence generator transformation

From router T/R , from new record output group copy the following ports to the expression transformation emp no, ename, job,sal,deptno

Double click on expression t/R select ports tab

Click apply ,click ok

From expression T/R copy the ports to update strategy T/R

Double click on update strategy T/R select properties

Click apply , click ok

From update strategy T/R connect the port to target

From sequence generator T/R connect the textual port to the EMP key of the target table

Defining the update records as insert:

Creating the transformation type expression and update strategy from router T/R , for update record output group copy the following ports to expression T/R

(EMPno, Ename,job,sal,deptno,version)

Double click on expression T/R select the ports tab

Uncheck the output port for a port name version

From tool bar click on add a new port

Click apply and click ok

From expression T/R copy the ports to update strategies T/R and develop the following strategy expression DD- insert

From update strategy T/R connect the ports to target

From sequence generator T/R connect the to the EMP key of target table.

Look up SQL overwrite:

Double click on the look up T/R select properties tab

Click apply ,click ok

Repository menu save

For indepth understanding of Informatica click on

• Informatica Tutorials & Interview Questions
• Introduction to Informatica
• Informatica Components
• Flat Files in Informatica
• Working with Tasks in Informatica