Expression Transformation in Informatica

The Expression Transformation in Informatica is a passive transformation that is used to perform non-aggregate calculations on the source data. It means you can use this Informatica Expression transformation to perform calculations on a single row. For example, you are trimming the extra spaces, data conversions, string manipulations, etc.

The following are some of the operations achieved by the expression transformation in Information:

  • Using this expression transformation in Informatica, we can test the data before passing it to another transformation or target table using conditional statements IIF
  • Data Manipulation: We can manipulate the data using built-in functions. For example, String Concatenation, Trimming the extra spaces using LTRIM and RTRIM and Rounding the values, etc.
  • Data Conversion: Converting the existing data to a required form using TO_DATE, TO_DECIMAL, etc.
  • Manipulating Date value: Extracting date part, calculating the date differences, etc.
  • Perform Arithmetic and logical operations.

This example explains the steps to configure the Expression Transformation in Informatica to store the Audit information. For this example, we are going to use the below show data

Expression Transformation in Informatica Source

In real-time this audit information will be very helpful to check the session time, Mapping Name, Workflow Name, Session Name, Integration Service Name, Repository Service Name, Repository User Name, Source Table Name, Session Start Time and System Date.

Configure Expression Transformation in Informatica

In this example, Our task is to load [First Name], [Last Name], and [Yearly Income] from the above source to the target table along with Audit Information. Before we start configuring the Expression transformation in Informatica, First connect to Informatica repository service.

To connect with the Informatica Repository service, we have to provide the Admin Console Username and Password you specified while installing the Informatica Server. Next, click on the Connect button.

Expression Transformation in Informatica Admin page

Create Informatica Expression Transformation Source Definition

Once you connected, Please navigate to Source Analyzer and define your Sources. In this example, we are using the [Employ] table from the SQL Server database as our source definitions. Please refer Database Source in Informatica article for source definition

Expression Transformation in Informatica Source Analyzer

Create Informatica Expression Transformation Target Definition

Please navigate to Target Designer and define the Informatica Expression Transformation Target. In this example, we are using the already created SQL table (Expression Transformation) as our target definition. Please refer Create Informatica Target table using Source Definition article

Expression Transformation in Informatica 1

Create Expression Transformation in Informatica Mapping

To create new Informatica Expression Transformation mapping, Please navigate to the Mappings menu and select the Create.. option. It opens the Mapping Name window to write a unique name (m_Expression_Transformation) and then click OK button.

Expression Transformation in Informatica 2

Next, Drag and drop the [Employ] source definition from Sources folder to the mapping designer. Once you drag the source, the PowerCenter designer will automatically create the default transformation called the source qualifier.

Creating Expression Transformation in Informatica

To create Expression Transformation in Informatica, Please navigate to the Transformation menu and select the Create.. option.

Expression Transformation in Informatica 3

Once you click on the Create.. option, Create Transformation window will open. Please select the Expression Transformation and specify the unique name (exp_Audit_ Information) and then click on Create button

Expression Transformation in Informatica 4

Once you click on the Create button, Expression Transformation will add to the mapping designer. Please connect the Source definition with the transformation.

From the below screenshot you can observe that we are adding [First Name], [Last Name] and [Yearly Income] to the expression transformation

Expression Transformation in Informatica 5

Double click on the Informatica Expression transformation to write the custom expressions. From the below screenshot, you can see the list of available properties in the Transformation tab:

  • Select Transformation: By default, it will select the transformation you clicked on.
  • Rename: This button will help you to rename the Expression transformation to a more meaningful name.
  • Make Reusable: If you checkmark this option, this transformation will become a reusable transformation.
  • Description: Please provide a valid description.
Expression Transformation in Informatica 6

Below screenshot will show you the list of available options in the Informatica Expression Transformation Ports tab:

  • Port Name: List of available column names. By clicking the New column button, you can add new columns, and by clicking the scissors button, you can delete the unwanted columns.
  • I: Columns that are check-marked under this section are the expression transformation Input columns.
  • O: Columns that are check-marked under this section are the expression transformation Output columns. If you unchecked any column then, that column will not be available to load in a target table.
  • V: Please check mark this if it is variable

From the below screenshot, you can observe that We added 9 new columns using the New Port button.

We are going to use these new columns to store the Audit information such as Mapping Name, Workflow Name, Session Name, Integration Service Name, Repository Service Name, Repository User Name, Source Table Name, Session Start Time and System Date.

TIP: All these new columns are output ports only. So, please checkmark O

Expression Transformation in Informatica 7

Let us write the custom expression to get the information we required. To do this, click on the arrow button beside the Mapping Name

Expression Transformation in Informatica 8

It opens the Expression Editor to write a custom expression. Here, we want the Mapping Name. So, please go to the Variables Tab and select one of the Built-in Variables called $PMMappingName.

Expression Transformation in Informatica 10

For the remaining fields in Informatica expression transformation, Please add the appropriate built-in variables.

  • Workflow Name: $PMWorkflowName
  • Session Name: $PMSessionName
  • Integration Service Name: $PMIntegrationServiceName
  • Repository Service Name: $PMRepositoryServiceName
  • Repository User Name: $PMRepositoryUserName
  • Source Table Name: [email protected]
  • Session Start Time: SSSSTARTTIME
  • System Date: SYSDATE
Expression Transformation in Informatica 11

Once you finish configuring the expressions of Informatica expression transformation. Click OK to close the transformation window. Next, Drag and drop the target definition (expression transformation) from the Targets folder to the mapping designer. Next, connect the expression transformation with the target definition using the Autolink.. option.

Let us validate the mapping by going to the Mapping Menu bar and selecting the Validate option.

Expression Transformation in Informatica 12

From the above screenshot, you can observe that Our Mapping is a valid one.

Create a Workflow for Expression Transformation in Informatica

Once we finish creating the Mapping, we have to create the workflow for it. PowerCenter Workflow manager provides two approaches to create a workflow.

In this Informatica Expression Transformation example, we will create the Workflow manually. Once we created the workflow, our next step is to create a session task for our mapping.

Step 4(a):

There are two types of sessions in Informatica:

For this Informatica Expression Transformation example, we are going to create Non-reusable Session. Please navigate to Tasks Menu and select the Create option to open the Create Task window. Here you have to select the Session as Task type (default) and enter a unique name (S_Expression_Transformation) for the session.

Once you click on the Create button, a new window called Mappings will open. Here you have to choose the mapping you want to associate with this session.

From the below screenshot, you can observe that we are selecting the mapping (m_Expression_Transformation) that we created earlier (in Step 3).

Expression Transformation in Informatica 13

Double click on the Session Task to configure it. Although we have to set Sources, targets, and some common properties, we are not going to explain them here. We strictly recommend visiting Session in Informatica article to understand the features.

From the below screenshot, you can observe that the Expression Transformation in Informatica workflow is a valid one. Now, let us start the Workflow by selecting the Start Workflow option in the Workflows menu.

Expression Transformation in Informatica 14

Once you select the Start Workflow option, the Informatica PowerCenter Workflow monitor opened to monitor the workflow. From the below screenshot, see our Informatica Expression Transformation workflow executed without any errors.

Expression Transformation in Informatica 15

Let us open the SQL Server Management Studio to check whether we successfully stored the Audit Information using the Expression Transformation in Informatica or not.

Expression Transformation in Informatica 16

Comments are closed.