Expression Transformation in Informatica
Expression transformation is a connected, passive transformation used to calculate values on a single row.The Expression transformation accepts the row-wise data, manipulates it, and passes it to the target. The transformation receives the data from the input port and sends the data out from output ports.Expression transformation can also be used to test conditional statements before passing the data to other transformations.
Examples of calculations are concatenating the first and last name, adjusting the employee salaries, converting strings to date etc.
Informatica Expression Transformation Uses
Use an Expression Transformation to derive the new attributes to change the inconsistent data into consistent format.Use Expression Transformations for any row-wise calculation, such as if you want to concatenate the names, get the total salary, and convert it to uppercase
An expression transformation is created with following types of ports.
- Input port (I)
- Output Port (O)
- Variable Port (V)
A transformation variable is created by creating a port and selecting the V check box. When V is checked, the I and O check boxes are grayed out. This indicates that a variable port is neither an input nor an output port.Variables Ports are not visible in Normal view, only in Edit view.
- Simplify complex expressions e.g. extract month from a date for use in several output ports
- Provide temporary storage
- Improve efficiency
- Variables are initialized (numeric to 0, string to “”) when the Mapping logic is processed
Ports are evaluated in the order
- All input ports
- Variable ports in the display order (expressions can refer to input ports and variable ports that appear earlier in the port list)
- Output ports (expressions can reference input and variable ports
Data can be modified using logical and numeric operators or built-in functions. Sample transformations handled by the expression transformer:
- data manipulation – concatenation, truncation, round (CONCAT, LTRIM, UPPER, INITCAP)
- datatype conversion (TO_DECIMAL, TO_CHAR, TO_DATE)
- data cleansing – check nulls, replace chars, test for spaces, test for number (ISNULL, REPLACESTR)
- manipulate dates – convert, add, test (GET_DATE_PART, IS_DATE, DIFF_DATES)
- scientific calculations and numerical operations – exponential, power, log, modulus (LOG, POWER, SQRT)
- ETL specific – if, lookup, decode (IIF, DECODE)
Creating an Expression Transformation
- In the mapping designer, create a new mapping or open an existing mapping.
- Go to Toolbar->click Transformation -> Create. Select the expression transformation.
- Enter a name, click on Create and then click on Done.
- You can add ports to expression transformation either by selecting and dragging ports from other transformations or by opening the expression transformation and create ports manually.
Once you created an expression transformation, you can add the expressions either in a variable port or output port. Create a variable or output port in the expression transformation. Open the Expression Editor in the expression section of the variable or output port. Enter an expression and then click on Validate to verify the expression syntax. Now Click OK.
Expression Transformation Components or Tabs
The expression transformation has the following tabs
- Transformation: You can enter the name and description of the transformation. You can also make the expression transformation reusable.
- Ports: Create new ports and configuring the ports.
- Properties: Configure the tracing level to set the amount of transaction detail to be logged in session log file.
- Metadata Extensions: You can specify extension name, data type, precision, value and can also create reusable metadata extensions.
You can configure the following components on the ports tab
- Port name: Enter a name for the port.
- Datatype: Select the data type
- Precision and scale: set the precision and scale for each port.
- Port type: A port can be input, input/output, output or variable.
- Expression: Enter the expressions in the expression editor.
Expression transformation examples
1. Create a mapping to increase the salary of an employee by 10 percent?
In the expression transformation, create a new output port (call it as adj_sal) and enter the expression as salary+salary*(10/100)
The expression can be simplified as salary*(110/100)
2. Create a mapping to concatenate the first and last names of the employee? Include space between the names
Just create a new port in the expression transformation and enter the expression as CONCAT(CONCAT(first_name,’ ‘),last_name)
The above expression can be simplified as first_name||’ ‘||last_name