Expression Transformation in informatica

Expression Transformation , is a connected  and passive transformation (number of input and output rows is the same), which let you modify individual ports of a single row , or add or suppress them. For example: calculating annual Salary, concatenation. In below article we will go through the properties of Expression Transformation.

Check here for filter transformation example in informatica

Scenarios of Expression Transformation in informatica

The Expression transformation in informatica is use to perform non-aggregate calculations for each data. Data can be modified using logical and numeric operators or built-in functions . Sample transformations handled by the expression transformer are :

• Data Manipulation : concatenation( CONCAT or || ) , Case change (UPPER,LOWER) truncation, InitCap (INITCAP)
• Datatype conversion :  (TO_DECIMAL, TO_CHAR, TO_DATE)
• Data cleansing - check nulls (ISNULL) , replace chars, test for spaces (REPLACESTR) , test for number

Why expression transformation is passive transformation ?

Expression Transformation is a Passive transformation as it only modifies the incoming port data , but it does'n effect the number of rows processed.

Expression Transformation  is Connected or Unconnected ?

Expression Transformation is a Connected Transformation.

steps to add Expression transformation in Informatica

Use the following procedure to create an Expression transformation.

Steps to create an Expression transformation:

1. In the Mapping Designer, open a Mapping.
2. Click Transformation > Create. Select Expression transformation.
3. You can also select Transformation by clicking  function  button on Informatica Designer
4. Enter a name and click Done.
5. Select and drag the ports from the source qualifier or other transformations to add to the Expression transformation. You can also open the transformation and create ports manually.
6. Double-click on the title bar and click on Ports tab. You can create output and variable ports within the transformation.
7. In the Expression section of an output or variable port, open the Expression Editor.
8. Enter an expression. Click Validate to verify the expression syntax.
9. Click OK.
10. Assign the port datatype, precision, and scale to match the expression return value.
11. To make it reusable ,check the reusable  option in the edit properties.
12. Configure the tracing level on the Properties tab.
13. Click OK.
14. Connect the output ports to a downstream transformation or target.

After you make the transformation reusable, you cannot copy ports from the source qualifier or other transformations. You can create ports manually within the transformation

Expression Transformation Components or Tabs

An Expression transformation contains the following tabs:

• Transformation:  Enter the name and description of the transformation. The naming convention for an Expression transformation is EXP_TransformationName. You can also make the transformation reusable.
• Ports: Create  port and  configure them.
• Properties: Configure the tracing level to determine the amount of transaction detail reported in the session log file.
• Metadata Extensions:  Specify the extension name, datatype, precision, and value. You can also create  reusable metadata extensions.

Configuring Ports: You can configure the following components on the ports tab

• Port name:  Add the name of the port.
• Datatype, precision, and scale:  Configure the datatype and set the precision and scale for each port.
• Port type : A port can be input, output, input/output, or variable. The input ports receive data and output ports pass data. 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.
• Default values and description:  Set default value for ports and add description

Check here example of Expression Transformation

For more details check below
  Informatica expression transformation
  Example of expression transformation in informatica
  Informatica performance tuning for expression transformation

Incoming search terms :

expression transformation
Informatica expression transformation
Expression transformation in informatica
Expression transformation in informatica with example
example of expression transformation
How to add expression transformation in informatica mapping
How to use expression transformation in informatica mapping