Expression Transformation
Expression Transformation in Informatica
Expression transformation is a connected, passive transformation used to calculate values on a single row. Examples of calculations are concatenating the first and last name, adjusting the employee salaries, converting strings to date etc. Expression transformation can also be used to test conditional statements before passing the data to other transformations.
Use the Expression transformation to calculate values in a single row before we write to the target. For example, we might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers.
Use the Expression transformation to perform any non-aggregate calculations.
Example: Addition, Subtraction, Multiplication, Division, Concat, Uppercase conversion, lowercase conversion etc.
We can also use the Expression transformation to test conditional statements before we output the results to target tables or other transformations. Example: IF, Then, Decode
There are 3 types of ports in Expression Transformation:
- Input
- Output
- Variable: Used to store any temporary calculation.
Calculating Values :
To use the Expression transformation to calculate values for a single row, we must include the following ports:
- Input or input/output ports for each value used in the calculation: For example: To calculate Total Salary, we need salary and commission.
- Output port for the expression: We enter one expression for each output port. The return value for the output port needs to match the return value of the expression.
We can enter multiple expressions in a single Expression transformation. We can create any number of output ports in the transformation.
Example: Calculating Total Salary of an Employee
- Import the source table EMP in Shared folder. If it is already there, then don’t import.
- In shared folder, create the target table Emp_Total_SAL. Keep all ports as in EMP table except Sal and Comm in target table. Add Total_SAL port to store the calculation.
- Create the necessary shortcuts in the folder.
Creating Mapping:
- Open folder where we want to create the mapping.
- Click Tools -> Mapping Designer.
- Click Mapping -> Create -> Give mapping name. Ex: m_totalsal
- Drag EMP from source in mapping.
- Click Transformation -> Create -> Select Expression from list. Give name and click Create. Now click done.
- Link ports from SQ_EMP to Expression Transformation.
- Edit Expression Transformation. As we do not want Sal and Comm in target, remove check from output port for both columns.
- Now create a new port out_Total_SAL. Make it as output port only.
- Click the small button that appears in the Expression section of the dialog box and enter the expression in the Expression Editor.
- Enter expression SAL + COMM. You can select SAL and COMM from Ports tab in expression editor.
- Check the expression syntax by clicking Validate.
- Click OK -> Click Apply -> Click Ok.
- Now connect the ports from Expression to target table.
- Click Mapping -> Validate
- Repository -> Save
Create Session and Workflow as described earlier. Run the workflow and see the data in target table.
As COMM is null, Total_SAL will be null in most cases. Now open your mapping and expression transformation. Select COMM port, In Default Value give 0. Now apply changes. Validate Mapping and Save.
Refresh the session and validate workflow again. Run the workflow and see the result again.
Now use ERROR in Default value of COMM to skip rows where COMM is null.
Syntax: ERROR(‘Any message here’)
Similarly, we can use ABORT function to abort the session if COMM is null.
Syntax: ABORT(‘Any message here’)
Make sure to double click the session after doing any changes in mapping. It will prompt that mapping has changed. Click OK to refresh the mapping. Run workflow after validating and saving the workflow.
Creating an Expression Transformation
Just follow the below steps to create an expression transformation
Just follow the below steps to create 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.
Adding Expressions
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.
Configuring Ports:
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.
Performance tuning :
Expression transformation is used to perform simple calculations and also to do Source lookups.
- Use operators instead of functions.
- Minimize the usage of string functions.
- If we use a complex expression multiple times in the expression transformer, then Make that expression as a variable. Then we need to use only this variable for all computations.
Expression transformation examples
1. Create a mapping to increase the salary of an employee by 10 percent?
Solution:
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
Solution:
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
1. Create a mapping to increase the salary of an employee by 10 percent?
Solution:
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
Solution:
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
No Comment to " Expression Transformation "