✍ Kashif M
Trends

Sunday, 8 September 2013


EXPRESSION TRANSFORMATION IN INFORMATICA


By on September 08, 2013


Passive Transformation

It is used to build row-by-row calculations.

We can derive new information (new data) in expressions.

Ports:
Input

Output

Variable

Expression

(All the ports are enabled except expression)

Define Source (EMP).

Define the Target Table (drag the Source definition from the Sources folder and rename it,edit it)

Rename the table to DIM_EXP.

Edit its properties.  Add new columns AN_SAL, HRA,TAX,NET_SAL,NET_SAL_COMM.Set the datatypes, precision  and scale.




Place the table in DWH_MEEN schema or the target Schema physically.



SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DIM_EMP                        TABLE
DIM_EMP1                       TABLE
DIM_RANK                       TABLE
DIM_EXP                        TABLE



Define the Mapping M_EXP. Drag the Source and Target.

Project the required ports from Source Qualifier to the Expression Transformation.



Edit the Transformation.

In the Ports tab add the following ports: AN_SAL, TAX, HRA, NET_SAL,AN_SAL_COMM.
Specify the data types and precision.

Uncheck the input ports for the newly added ports (since they are not coming as input) AN_SAL, TAX, HRA,NET_SAL,AN_SAL_COMM. Similarly HRA and TAX are not required in the target, so we check them as variable.


These ports now appear under the Expression list (highlighted). Click on Apply and then OK.




Apply all the formulas in the Expression drop down.



AN_SAL_COMM =SAL * 12 + IIF(ISNULL(COMM),0,COMM)



Then create a session,workflow,Repository->save,Start Workflow,
Preview the data



Kashif
mkashu: EXPRESSION TRANSFORMATION IN INFORMATICA
Review : Kashif | Kashif
Update: September 08, 2013 | Rating: 4.5

Comment for "EXPRESSION TRANSFORMATION IN INFORMATICA"

1 comments:

Blog Archive