• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Expression Transformation in Informatica

Expression Transformation in Informatica

Expression transformations are used for row-wise manipulation. For any type of manipulation you wish to perform on an individual record, use an Expression transformation. The Expression transformation accepts the row-wise data, manipulates it, and passes it to the target. The transformation receives the data from the input port and sends the data out from output ports.

This is of type passive transformation which allows you to calculate the expressions for each record. An expression transformation in Informatica supports to write expression either in variable ports or only in output ports. In this Informatica Tutorial for Beginners, we will learn about Expression Transformation, it uses and examples. We will also learn about Rank Index and Rank Transformation in other posts.

Informatica Expression Transformation Uses

Use an Expression Transformation to derive the new attributes to change the inconsistent data into consistent format.

Use Expression Transformations for any row-wise calculation, such as if you want to concatenate the names, get the total salary, and convert it to uppercase

An expression transformation is created with following types of ports.

  • Input port (I)
  • Output Port (O)
  • Variable Port (V)

Variable Ports

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.

  • Simplify complex expressions e.g. extract month from a date for use in several output ports
  • Provide temporary storage
  • Improve efficiency
  • Variables are initialized (numeric to 0, string to “”) when the Mapping logic is processed

Ports are evaluated in the order

  • All input ports
  • Variable ports in the display order (expressions can refer to input ports and variable ports that appear earlier in the port list)
  • Output ports (expressions can reference input and variable ports
Advanced Properties for Expression Transformation
Tracing Level Amount of detail that appears in the log for this transformation. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.

Expression Transformation in Informatica Example

Design a mapping to calculate the total sal (sal+com) where common is having Nulls.

  • Create a source definition with the name (Emp)
  • Create a target definition with the name (Emp-total sal)
  • Create a mapping with the name (M-employee-total sal)
  • Drop the source and target definitions
  • create the transformation tab expression

from SQ-Emp copy the required ports to expression transformation double click on expression transformation select the ports tab from tool bar click on add a new port

port namedatatypeprecessionsaleI.O.Vexpression
tsaldecimal72

IIF (ISNULL(cinn), SAL, SAL+Com

SAL+IIF(ISNULL(comm),0,comm

click apply click ok

From expression transformation connect the ports to the target

Business Logic

Calculate the tax(sal*0.17) for top 3 employees based on salary, Who belongs to ‘sales’ department sales department Id is 30.

 

Expression Transformation in Informatica

 

Procedure of Creating Transformations

  • Create source target definition as described in DFD
  • Create a mapping with the name M_Tax_calculation
  • Drop source and target definitions to the mapping designer workspace.
  • From transformation menu select create select the transformation type filter.

Enter the name filter_tax_cal click on create

Click Done

  • From source qualifier (SQ-EMP) Copy the required ports to the filter transformation (EmpName, Eno, Job, sal, Dept no)

Double click on the filter transformation select the properties tab

Header Names are

Transformation attribute                                                              Value

Filer condition                                                                                   Dept no-30

Click validate  click ok

Click Apply

Click ok

  • From Transformation menu select create select the transformation type rank

Enter the name Rank-tax-cal

Click on create and Done

  • From filter transformation copy the ports to the rank transformation.

Double click on Rank Transformation

Select the ports tab

For port name Sal select rank port

Select the properties tab

Transformation attribute                                                              Value

Top/Bottom                                                                                       Top

Number of Ranks                                                                             3

Click Apply

And click ok

  • From Transformation menu select create

Select the transformation type expression

Enter the name Exp-Tax-cal

Click on create and Done

  • From Rank transformation copy the ports to an expression transformation (Except Rankindex)

Double click on Expression Transformation select ports tab.

  • From tool bar click on add a new port

Data type precision scale I O V Expression

Tax decimal                                7                              2                              dsl*0.17

Click Apply click on ok

  • From expression Transformation connect the ports to target from repository menu click on save.

Read 

Read about Router Transformation here Router Transformation

Power Center Transformation language

The power center transformation language is set of built in functions which are similar to SQL functions.

The Built in functions are used to develop the business logic or transformation rules.

The function are categorized into following types

  • String functions
  • Numeric functions
  • Data functions
  • Aggregate functions
  • Test functions
  • Variable functions
  • Data cleansing functions
  • Financial functions
  • Scientific functions
  • Mislenious functions

Example

Design a mapping which rejects the records contains null.

Procedure:-

  • Source definition (EMP)
  • Target definition (EMP-NULL-Validate)
  • Target columns (Empno, empname, job, comm., deptno)

Create the mapping with name  m_employee_NULL)reject

Drop the source and target definitions

Create the transformation type filter

From source qualifier copy the required ports to the filter transformation

Double click on filter transformation  select the properties tab

Transformation attribute                                                              Value

Filter condition                                                                  IIF(ISNULLcomm),False, )

Click Apply click on ok

From filter transformation connect the ports to target

Interested in mastering Informatica Training? 
Enroll now for FREE demo on Informatica Training Online.

Example

Design a mapping to verify the null for every source column, reject the record if any column contains null.

Procedure:-

  • Create source and target definition
  • Create mapping with the name m_EMP_NULL_validate
  • Create the transformation type filter from source unifier copy the required ports to filter

Double click the filter transformation select the properties tab

Transformation attribute                                                              Value

Filter validation                                                                 IIF (ISNULL empno)OR

ISNULL(ENAME)OR

ISNULL(MGR)OR

ISNULL(SAL)OR

ISNULL(COMM)OR

ISNULL(DEPTNO),False, True)

Click Apply click ok

From filter transformation connect ports to the target

Example

Design a Mapping which can load the employees whose Ename Start with ‘S’.

Screenshot_24 create source and target definitions

Screenshot_24 create the mapping with the Name M-EMP-5

Screenshot_24 Drop the source and target definitions

Screenshot_24 create the transformation type filter and develop the following filer condition

substr (EName, 1,1,)=’S’

Screenshot_24 from filter transformation connect the ports to the target

Example

Design a mapping which can load employees whose Ename is having > =6 characters

Procedure:-

Screenshot_24Create the filter transformation with the following condition length (Ename)>=6

Ex:- Design a mapping which can load employees whose Empno is an even number

Procedure:-

MOD(Ename.2)=0

For indepth understanding of informatica, click on

Summary
Review Date
Reviewed Item
Informatica Expression Transformations
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

2 Responses on Expression Transformation in Informatica"

  1. loknath says:

    its not drop source and target definitions …its drag..
    in our data languages drop completely changes the meaning of sentence

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.

Support


Please leave a message and we'll get back to you soon.

I agree to be contacted via e-mail.