Expression Transformation

Expression Transformation is a connected passive transformation (i.e.) Number of input and output Rows is the same), you can modify individual ports of a single row, or add or suppress them. It helps implement the complicated data transforms, applies business logic and performs checks and validations. You can use various function according your business logic.

Built-in Functions:

Data Manipulation: concatenation (CONCAT or ||), Case change (UPPER, LOWER) truncation, InitCap (INITCAP)

Data type conversion: (TO_DECIMAL, TO_CHAR, TO_DATE, TO_FLOAT, and TO_NUMBER)                 

Data cleansing: GREATEST, IN, INSTR, IS_DATE, IS_NUMBER, IS_SPACES, ISNULL, LEAST, LTRIM, RTRIM

Manipulate dates: GET_DATE_PART, IS_DATE, and DIFF_DATES

Scientific calculations and numerical operations: exponential, power, log, modulus (LOG, POWER, SQRT)

Informatica Function: IIF, DECODE

Properties of Expression Transformation:

Active /Passive: Expression Transformation is a Passive transformation as it only modifies the incoming port data, but it won’t affect the number of rows processed.

Connected/Unconnected Transformation: Expression Transformation is a connected   Transformation

Types of ports in Expression Transformation: Input, Output, Variable port.

Expression transformation in Informatica:

1. Steps to create Expression transformation: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 (ex: EXP_DIM_BUN_EMEA).
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 data type, 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.

Expression transformation Tabs:

• Transformation:  Enter the name and description of the transformation. The naming convention              for an Expression transformation is EXP_meaning_full_name. 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, data type, 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.
• Data type, precision, and scale:  Configure the data type 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.

 Purpose of Expression Transformation: The Expression transformation is use to perform non-aggregate calculations for each data. Data can be modified using logical and numeric operators or built-in functions.

Performance Tuning on Expression Transformation:

• Try to use numeric operation instead of string operations.
• Use of operators are faster than functions (i.e. || vs. CONCAT).
• Use transformation variables to break down complex transformation logic into smaller parts.        You can define variable port by clicking on V check box while defining the port. In the expression editor of it, it can all input port and other variable port for calculation.
• It is highly recommended to define a naming convention for the input and output ports for expressions. For example, all input ports have an in_ prefix for Input port, out_ for output   ports and var_ for variables port.
• Ports are evaluated in the following order: input ports first, then variable ports and then output ports.