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.

Look up table column	Operation	Transformation Port
Empno	=	Emp no1

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

Port Name	Data type	Precision	IOV	Expression
New- Flag	String	10		Iff(IS NULL(temp key),’true’, ’False’)
Update – Flag	String	10		Iff(IS NOT NULL(temp key),’true’, ’False’)

Create the transformation type router

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

Informatica Concepts

Group Name	Group Filter condition
New- Record	New – flag =’True’
Update – Record	Update – flag=’True’

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

Transformation Attribute	Value
Update strategy expression	DD –insert (or) 0’

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.

If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.

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

Transformation Attribute	Value
Update strategy expression	DD- update

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

Attribute	Value
Dollar source connection value	Batch 4pm – source – reader
Dollar target connection value	Batch 4pm- target - writer

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]

Informatica Tutorials & Interview Questions

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.

Look – up table column	Operator	Transformation port
Emp no	=	Empno1

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

Port Name	Data type	Precision	Scale	IOV	Expression
New - Flag	String	10			IFF(IS NULL(Emp key) ‘True’, ‘False’)
Update - Flag	String	10			IFF(NOT IS NULL(Emp key),AND(Sal!=Sal1)’True’, ‘False’)

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

Group Name	Group filter condition
New – record	New – Flag = ‘True’
Update – record	Update – Flag = ‘True’

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

Port Name	Data type	Precession	IOV	Expression
Version	Decimal	5		0

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

Transformation Attribute	Value
Update strategy expression	0 (or) DD- Insert

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 the toolbar click on add a new port

Port Name	Data type	Precession	IOV	Expression
Version	Decimal	5		Version 3+2

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 the 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

T/R Attribute	Value
Look up SQL overwrite	Select Out. Emp Key as Emp key, Out. Ename as Ename, Out. Job as Job, Out. Sal as Sal, Out. Dept no as dept no, Out . version as version, Out . emp no as emp no From emp – type2 out Where out. Emp key =(select max(Inn .Empkey) From Emp – type2 Inn Where Inn .emp no = out.empno)

Click apply ,click ok

Repository menu save