Tutorial corner

Informatica,ETL,oracle,sql/plsql

Expression Transformation in Informatica with Example

Spread the love

Expression Transformation in Informatica , is a connected 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. It helps implement the complicated data transforms, applies business logic and performs checks and validations.For example: calculating annual Salary, concatenation. In below article we will go through the properties of Expression Transformation. We will also discuss the steps of adding /configuring expression transformation in Informatica Mapping.

Expression Transformation in informatica

Expression Transformation in informatica

Check here for filter transformation example in informatica

Business Purpose 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
  • 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)

Is Expression Transformation Active or Passive ?

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

Is Expression Transformation Connected or Unconnected ?

Expression Transformation is a Connected Transformation.

Properties of Expression Transformation

  • Active /Passive : Expression Transformation is a Passive transformation as it only modifies the incoming port data , but it does’n effect the number of rows processed.
  • Connected/Unconnected Transformation : Expression Transformation is a connected Transformation
  • Types of ports in Expression Transformation:

    • Input :
    • Output :
    • Variable: Used to store any temporary calculation

How to use 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

    Expression Transformation shortcut

    Expression Transformation short cut

  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

Variable Ports in Expression Transformation

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.

Ports are evaluated in the order:
1) All input ports
2) Variable ports in the display order (expressions can refer to input ports and variable ports that appear earlier in the port list)
3) Output ports (expressions can reference input and variable ports)

Example of Expression Transformation in Informatica

Check here example of Expression Transformation

Expression Transformation Performance Tuning :

  • Try to use numeric operation instead of string one
  • 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 in the display order (from top to bottom), then output ports.

So , in this article we have gone through Expression Transformation  in Informatica with example ,scenarios where to use it , steps to create it and  performance related guidelines. Hope you enjoyed the article.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme