Wednesday, February 24, 2016

Expression Transformation In Informatica

Expression Transformation 

Ø  Use the Expression transformation to perform calculations at a single row level
Ø  to test conditional statements before you pass the results to a target or other transformations.
Ø  Port type. A port in expression transformation can be input, output, input/output, or variable.
·         The input ports receive data and output ports pass data to downstream transformations.
·         The input/output ports pass data unchanged.
·         Variable ports store data temporarily and can store values across the rows.
Ø  Expression. Use the Expression Editor to enter expressions. Expressions use the transformation language, which includes SQL-like functions, to perform calculations.
Ø  Using variable ports in expression transformation we can define an expressions to perform a repated calculation in variable and we can refer the result of the variable from multiple output ports of expression transfroamtion. This aviods calculating the same result in multiple output ports. And this way we can divide compuation of complex expression to simple expressions
Ø  A variable port can also be used to store values of a port across different records (i.e. between previous record and current record)
Ø  Expression transformation can also be used with Non relational database sources
Identifying duplicate records using Expression transformation:
Ø  Expression transformation is one of the best ways to eliminate duplicate records coming from the source. The only limitation is that data has to arrive from source in sorted order. If there is a key coming in from source we can store previous key value in one variable and we can compare with the each new incoming row key if there is a match between previous row key and current row key then it’s a duplicate row else its not a duplicate row.
      Input Port: Current_Record_Kay
Variable Port 1: IIF(Prev_Record_Key=Current_Record_Key,'Duplicate','Non Duplicate')
Variable Port 2 : Prev_Record_key=Curr_Record_key
Ø  Since expression is a passive transformation it wont occupy any cache memory so the processing will be faster ,it is the best and optimized way of identifying duplicate records.

Ø  The screenshot of configuring expression transformation to identify duplicate records is shown below:
Expression Transformation In Informaica

No comments:

Post a Comment