Design a mapping with SED type 1 implementation:
A Type one dimension keeps only current data in the target. It doesn’t have any history.
Procedure:
- Create a source definition with the 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
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
- Keep the version number in a separate column
- Mark the current dimension record with the flag
- 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
- 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