Creating Expression Transformation in Informatica

Expression Transformation in Informatica is a passive transformation can be used to calculate values in a single row. It also can be used to perform non-aggregate calculations. i.e concatenation, division, multiplication nothing but all your SQL level row functions can be applied here. This will help to apply any row level functions those include string, numeric, date and conversion inside the Informatica. .i.e. concat(), substr(), length(), abs(), round(), to_char(), to_date(). These row level functions will help to change any value or derive any values.

In this Informatica tutorial, we learn how to create an Expression Transformation in Informatica with an example. To create Expression Transformation, Check the following scenario: Create a mapping to load only WASHINGTON city customers from customer table along with Full Name from First Name and Last Name columns into stagedb table.

Here our source table is customer and target table is Stg_exp_customer. 

• Mapping Name: m_<Target Table Name>
• Session Name: s_<mapping name>
• Workflow Name: wf_<mapping Name>

Creating  Expression Transformation in Informatica

• Import source table to source analyzer. If you already have, no need to import again.
• Import target table structure into target designer with odbc

• Now create mapping with name ‘m_Stg_exp_Customer’ from mapping designer.

• Now drag source and target tables into mapping designer.

• Now create filter transformation and drag & drop columns from SQ_CUSTOMER to filter

• Also define filter condition at filter transformation level

• Now create a new transformation called as Expression and drag all columns to expression from filter.

• Now double click on expression transformation and go to ports tab and click on SQUARE BOX on right side

• Now provide name as ‘Full_Name’ in the NEWFILED along with 40 Prec.

• Now uncheck ‘I’ check box for full_name column.

• Now click on ‘Expression’ on right side crossed arrow.

• Now provide logic as CONCAT(FIRST_NAME,LAST_NAME)

When you open this editor, on the left side it has three Tab’s. One is for Functions, other one is for Ports and last one is for Variables. The functions consist of all the data modification functions like concat(), substr(),…and ports consist of all transformation level columns and those can be used to write any logic

• Now come out of expression editor window and column give links from Expression to target

• Now save and final mapping looks as below.

• Goto Workflow manager(W), go to Task developer and create session from Task developer.

• Provide connections for source and target inside the session level.

• Now go to workflow designer and create workflow and assign session.

• Now save and run workflow.
• Verify data in Monitor level and test data with SQL queries.

How to test mapping in Expression Transformation

Code checking:-

Before testing or executing any mapping, we first need to check that weather we have given right links or not. And also need to check properties of transformation weather required properties defined or not.

Data Checking:-

Generally in ETL process what we do is we just extract data from source with Informatica nothing but a selecting data from source and we do transform data inside ETL then load to target.

We can test this ETL process with a simple formula as below.

Target Data = Source data + Transformation logic inside Informatica

With the above formula we have to test two main test cases.

1. No of rows from Target is equal to no of rows with source data plus Transformation logic inside Informatica.

For the above mapping, sql’s for this test case are

Select count(*) from  Stg_exp_customer;
Select count(*) from Customer where CITY='WASHINGTON';

Now fire source query on source database and see count. In same way fire target query on target database and see the count then compare source count and target count. As per our formula it looks like given below.

Select count(*) from  Stg_exp_customer = Select count(*) from Customer + where 
CITY='WASHINGTON';

This will not execute in database, just give you to refer your formula.

2. Data should match in target with source and Transformation logic inside Informatica.

SELECT CUSTOMER.CUSTOMER_ID, fullName, CUSTOMER.ADDRESS1, CUSTOMER.ADDRESS2, CUSTOMER.CITY,
 CUSTOMER.COUNTY, CUSTOMER.POSTAL_CODE, CUSTOMER.COUNTRY_CODE, CUSTOMER.COUNTRY_NAME,
 CUSTOMER.CREATION_DATE,CUSTOMER.CREATED_BY, CUSTOMER.LAST_UPDATE_DATE, 
CUSTOMER.LAST_UPDATED_BY FROM Stg_exp_customer customer where CUSTOMER_ID=100004