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
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)
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
Stay Updated
Get stories of change makers and innovators from the startup ecosystem in your inbox
