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.
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:
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
The same process can be done to TOTAL_SALARY attribute.
Now that we have our mapping done, let’s create a workflow. On the mapping screen, right click and go > generate workflow.
A workflow will be created, a wizard comes up, just next next finish.
Open your workflow manager or refresh if it was already opened. On the Double click your task, edit your mapping. Set your local file location.
Click ok and save your workflow. Now right click the workflow and start it.
Here’s the result of the run:
Here’s on the database:
If you want to know more about Expression Transformation, read the docs: https://docs.informatica.com/integration-cloud/cloud-data-integration/current-version/transformations/expression-transformation.html
2 thoughts on “Informatica Powercenter – Expression Transformation”