The Expression Transformation in Informatica is a passive transformation which is used to perform non-aggregate calculations on the source data. It means, you can use this transformation to perform calculations on single row for example, trimming the extra spaces, data conversions, string manipulations etc. Following are the some of the operations achieved by the expression transformation in information:
- Using this transformation, 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 required form using TO_DATE, TO_DECIMAL etc.
- Manipulating Date value: Extracting date part, calculating the date differences etc.
- Performing Arithmetic and logical operations.
In this article we are going to explain the steps involved in configuring the Expression Transformation in Informatica to store the Audit information with example. For this example, we are going to use the below show data
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 target table along with Audit Information. Before we start configuring the Expression transformation in informatica, First connect to Informatica repository service. In order to connect with Repository service we have to provide the Informatica Admin Console credentials so, Please provide the appropriate Username and Password and click on Connect button as shown below.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Step 1: Creating Expression Transformation Source Definition
Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this example, we are using [Employ] table from SQL Server database as our source definitions. Please refer Database Source in Informatica to understand the steps involved in creating source definition
Step 2: Creating Expression Transformation Target Definition
Please navigate to Target Designer and define the 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 to understand the steps involved in creating target definition
Step 3: Creating Expression Transformation Mapping
In order to create new mapping, Please navigate to Mappings menu in Menu Bar and select the Create.. option. This will open the Mapping Name window as shown below. Here, you have to write unique name for this mapping (m_Expression_Transformation) and click OK button.
TIP: Please refer Informatica Mapping article to understand the steps involved in creating Mapping.
Next, Drag and drop the [Employ] source definition from Sources folder to the mapping designer. Once you drag the source, PowerCenter designer will automatically create the default transformation called source qualifier.
Step 3(a): Creating Expression Transformation in Informatica
In order to create Expression Transformation in informatica, Please navigate to Transformation menu in Menu Bar and select the Create.. option as we shown below.
Once you click on the Create.. option, Create Transformation window will be opened as we shown below. Please select the Expression Transformation from drop down list and specify the unique name (exp_Audit_Information) for this transformation and click on Create button
Once you click on the Create button, Expression Transformation will be added to the mapping designer. Please connect the Source definition with the transformation by dragging the required fields. From the below screenshot you can observe that, we are adding [First Name], [Last Name] and [Yearly Income] to the expression transformation
Double click on the Expression transformation to write the custom expressions. From the below screenshot you can see the list of available properties in 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 more meaningful name.
- Make Reusable: If you check mark this option then, this transformation will become reusable transformation.
- Description: Please provide the valid description about this transformation.
Below screenshot will show you the list of available options in the 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 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 check mark O
Let us write the custom expression to get the information we required. To do this, Click on the arrow button besides the Mapping Name as we shown below
This will open the Expression Editor to write custom expression. Here, we want the Mapping Name so, please go to the Variables Tab and select one of the Built-in Variable called $PMMappingName.
For the remaining fields, Please add the appropriate built-in variables as shown below.
- 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
Once you finish configuring the expressions, Click OK to close the transformation window. Next, Drag and drop the target definition (expression transformation) from Targets folder to the mapping designer and connect the expression transformation with the target definition. Please use the Autolink.. option to connect them.
Before we close the Mapping, Let us validate the mapping by going to Mapping Menu bar and selecting the Validate option.
From the above screenshot you can observe that, Our Mapping is a valid one.
Step 4: Creating 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 workflow.
In this example we will create the Workflow manually. Once we created the workflow, our next step is to create session task for our mapping.
Step 4(a): Creating Session for Expression Transformation in informatica
There are two types of sessions in informatica:
- Non-reusable Session Task: Please refer Session in Informatica article to understand the steps involved init.
- Reusable Session Task: Please refer Reusable Session in Informatica article to understand the steps involved init.
For this 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 unique name (S_Expression_Transformation) for the session.
Once you click on the Create button, a new window called Mappings will be opened. Here you have to select 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).
Double click on the Session Task to configure it. Although we have to configure Sources, targets and some common properties, we not going to explain them here. We strictly recommend to visit Session in Informatica article to understand the properties.
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 navigating to Workflows menu and selecting the Start Workflow option.
Once you select the Start Workflow option, Informatica PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that, our workflow is executed without any errors.
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.
Thank You for Visiting Our Blog