• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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

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.

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

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]

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 tool bar 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 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

For indepth understanding of Informatica click on

Summary
Review Date
Reviewed Item
Designing and mapping with SED implementation in Informatica
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Designing and mapping with SED implementation in Informatica"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.