Sunday, March 31, 2013

Expression Transformation

The Expression Transformation is an Passive and Connected Transformation.

The Expression transformation to calculate values in a single row before it writes into Target.

For example, you might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers. You can also use the Expression transformation to test conditional statements before you pass the results to a target or other transformations.

The Expression transformation is use to perform non-aggregate calculations.

Task can be performed using Expression Transformation:
The transformation language provides the following types of functions:
  •  Aggregate - Only Row level Addition, Subtraction, Multiplication and Division
  • Character
  • Conversion
  • Data Cleansing
  • Date
  • Encoding
  • Financial
  • Numerical
  • Scientific
  • Special
  • String
  • Test
  • Variable
Generally we use Expression transformation for Test, Character and Conversion function like IF...ELSE..THEN,  DECODE, ISNULL, IS_DATE, IS_SPACES, IS_NUMBER, etc..

The Expression Transformation having 3 types of port:
  • Input: The Input port is that which received data directly from its before transformation.
  • Output: The Output port is that which return value of the expression or a Hard coded Value
  • Variable: The Variable port is that Temporary value of a current row or a Hard coded value.
The Variable port and Output port are used to perform a expression for each record passing through it. We can have multiple expressions in single expression transformation. We can have 'N' no of Variable and Output Port in single expression transformation.

Example: Create a mapping to sum salary and commission for each employee. (Row level calculation)

Sample Mapping:
1. Connect and Open the folder if not already opened.
2. Select Tools --> Mapping Designer
3. Select Mappings --> Create

It will pop-up "Mapping Name". Enter the mapping name of your choice" and Click on 'OK'. Example: m_emp_salary

4. Drag the Source and Target definitions into workspace if they are already exist. If not click here to know how to create or import Tabledefinitions.

5. Select 'Transformation' from Menu --> Create
a)    That will appear you 'Select the transformation type to create:'

 b)    Select 'Expression' from drop down and 'Enter a new name for this transformation:' as "exp_employee_salary"
c)    Click 'Create' and 'Done'

or
  
a)    Click on Expression Transformation icon marked below in below snapshot.

 b)    Click in the workspace in Mapping Designer.

 c)    Select EXPTRANS in workspace and Right Click --> Edit.

d)    In Transformation tab --> Click on 'Rename' highlighted above which will pop-up 'Rename Transformation'. Enter the Transformation Name: "exp_employee_salary"
e)    Click on 'OK'
f)    Click on 'Apply' and 'OK'.

6). Drag required ports from 'SQ_EMP' Source Qualifier to 'exp_employee_salary' Expression Transformation.

7) Select 'exp_employee_salary' Expression Transformation and Right Click --> Edit --> Ports Tab.

a) Un-Check Output Ports for SAL and COMM

Before: See the port highlighted in red.
After: See the ports highlighted in red.

b) Add the Variable port to expression transformation.
Note: Use the below 'Icon' to handle the port as commented.



·         Add a new port to the transformation and name it 'v_Salary' and check 'Variable check box' as shown above
·         Click on 'Open Browser' and Write a Expression in "Formula:" box like 'SAL+COMM' or First_Name||Last_Name.
·         Click on 'Validate' to validate function and Click on 'OK'


c) Add Output port to expression transformation.

  • Add a new port to the transformation and name it 'o_Salary' and check 'Output check box' as shown above
  • Click on 'Open Browser' and Write a Expression in "Formula:" box like 'v_Salary' which is variable value
  • Click on 'Validate' to validate function and Click on 'OK'
d) Click on 'Apply' and Click on 'OK'.

8). Drag required port from 'exp_employee_salary' expression to 'EMP_DEPT_FILE' Target as below:
 
9) Select 'Mapping' from Menu --> Validate.

10) Select 'Repository' from Menu --> Save.


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

21 comments:

  1. It is very useful
    Thank u so much

    ReplyDelete
  2. It gets easy, with such nice explanation and diagrams.

    ReplyDelete
  3. thanks a lot gautham for such a nice explanation.

    ReplyDelete
  4. THANK U SO MUCH GOWTHAM

    ReplyDelete
  5. can we select all ports and uncheck input port checkbox for all in one go???

    ReplyDelete
  6. Hi Kalyani,

    In informatica no such option available if you are familiar with key board you can do this much easier then using mouse.

    ReplyDelete
  7. Hi Gowtham, please post informatica scenarios for reference.

    ReplyDelete
  8. Hi,
    I have complete Informatica video lecture of approx 36 hour including every transformation with business scenario. Please contact me on rahul.sapetl@gmail.com for more details.

    ReplyDelete
    Replies
    1. Hi Rahul,

      plzzzzzzzzzz.. share the video .. my Mail ID shivukumar47@gmail.com

      Delete
    2. Hi Rahul,

      Please share the video .. Email Id :ms.suganyaraj@gmail.com

      Delete
    3. Hi Rahul,

      Please share the video on Email Id :suresh.baipu@gmail.com

      Delete
    4. Hi Rahul,

      Plase share video on Email Id:adityapotnis03@gmail.com

      Delete
    5. Plase share video on Email Id: mr.kundan21@gmail.com

      Delete
    6. Hi rahul Plzz share the videos at pankajparashar90@gmail.com

      Delete
    7. Hi Rahul, pls share the video. My mail is - abhishekc059@gmail.com

      Delete
    8. Please send video link to ncrao999@gmail.com

      Delete
  9. thanks a lot for such a nice explanation.

    ReplyDelete
  10. Hi, Thanks a lot for the brief explanation.

    ReplyDelete
  11. to_char(to_date(if_date(ddmmyyyy),dd/mm/yyyy).Want to keep alias on it without using variable port. Need to have shortcut for this expression.

    ReplyDelete
  12. its very usefull gowtham.thank you so much.

    ReplyDelete