Informatica Powercenter – Expression Transformation

First post of the many to be on Informatica Powercenter, starting right with Expression Transformation.

In this scenario our goal is to get data from a flat file, transform it using expressions and then load into our database the result of that transformation.

First, we need:

a source,
then a target,
then this target needs to create a table in our database,
then a mapping with our transformation steps, which in this case is the expression transformation,
and then we need to take this to the workflow manager,
then create a workflow,
adjust the workflow mapping,
then run it, and its done.

The source

Our source is a flat file, which I created myself using Excel and saved as csv. Here’s the structure, pretty simple.

The original name is EMP_FILE.csv, the one in the picture is a copy that I leave open while I create the ETL, for double check purposes.

So, load this file in your Powercenter Designer, how to do that I explained in this previous post.

Here’s how your result should look like:

The Target

Time to create our target. Just drag your source and drop here, double click and do the adjustments. Rename it to EMP_TOTALS, make sure you also select your database type.

Once it’s done, you select that EMP_TOTALS target and ask Powercenter to create that table for you. It will create this target table in your database, with no data, of course. To do so:

Go to target > generate/execute SQL

Select your database, in my case it’s SQL_DW, then select what you want, in my case its Create Table, and then hit Generate and Execute.

The table is now inside our database.

The Mapping and Transformation

Create a new mapping and call it T_EXPRESSION. Drag in your source and target, and create a new transformation of expression type inside your mapping.

Here’s the final product:

The trick part here is the transformation. So, double click your transformation.

In this project we want to create a new column called FULLNAME and a TOTAL_SALARY.

To do that we need to uncheck first and last name output ports, and the same for JAN and FEB salary output ports. We also need to uncheck FULLNAME and TOTAL_SALARY input ports. When we do that the EXPRESSION option will become available.

Click and go inside FULLNAME transformation and type this code:

CONCAT( CONCAT( FIRST_NAME, ‘ ‘ ), LAST_NAME )

If you wanna know more about concat, check the docs: https://docs.informatica.com/data-integration/data-services/10-2/transformation-language-reference/functions/concat.html