EXPRESSION TRANSFORMATION IN INFORMATICA

• 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