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
- EMP_ DIM_Type1(insert)
- 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.
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
- Keep the version number in a separate column
- Mark the current dimension record with the flag
- 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
- EMP_type2(new record to insert)
- 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
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