Expression Transformation in Informatica

Introduction to Expression Transformation in Informatica

Expression Transformation in Informatica is one of the passive transformation features that is used to manipulate row-wise data through the mapping. It accepts data from input ports and processes the record-wise data modification using several operations or functions though the variable ports and output ports. It supports multiple expressions on multiple output ports in an expression transformation that helps in creating the calculated or transformed values. Expression transformation supports dynamic and pass-through ports for data propagation. One of the use cases of expression transformation in Informatica is calculating the retail price of the products based upon the tax rates and the regular sales prices using the price list data.

How to Perform Expression Transformation in Informatica?

Here we will see how to perform expression transformation in Informatica using the Informatica cloud that is the latest offering from Informatica for data integration. Informatica Cloud is software as a service (Saas) that provides a cloud-based platform and functionality to develop data integration solutions.

Given below are the steps to use expression transformation in Informatica.

Step 1: Log in to the Informatica cloud.

The first step for developing data integration through the Informatica cloud is to log into the cloud instance provided by Informatica. Use the credentials to log in to the Informatica cloud environment.

Step 2: Navigate to the Data Integration option.

Once the login is successful, the My Services dashboard will appear. This section or the dashboard view is based upon the license subscription options of Informatica.

Select the Data Integration in the My service section to enter into the data integration tool dashboard. The other Tabs such as Administrator, DiscoveryIQ, and Monitor shown in the dashboard are for configuring and managing the data integration resources.

Step 3: Selecting the New option from the data Integration dashboard.

The Data integration homepage displays the various sections for resource and artifacts creation and the summary of the artifacts.

Step 4: Create a new mapping from the ‘New Asset’ options.

The New Asset window shows multiple options such as creating new Tasks, mappings, Templates, Task Flows, and Components. The New Mapping is the design of the data integration flow using the inbuilt features and functionalities that help in the extract, transform and load (ETL) process.

Step 5: Name the Mapping with a meaningful word.

The Mapping windows show the default view with a source and target component views in the design palette. It shows the properties section that allows the user to enter names, select the location and enter the description of the mapping.

We will provide the name of the mapping as ‘DemoMapping’ in the Name text box and click on the ‘save’ button shown on the Top apply the Name to the mapping.

By default, it will show the ‘Invalid’ label on the top of the mapping window. This indicates the source and the destination pallets are not configured with the proper connections. It will be shown as ’valid’ once the connection configuration will be applied.

Step 6: Add the Expression transformation to the mapping.

As Expression Transformation is connected and passive transformation, it needs to be added in between the source and target computer inside the mapping. The Expression option is available on the left section design view and listed as the 4th option with the function f(x) icon.

To add the expression to the mapping select and drag it to the position after the source as shown in the image and save the mapping. Informatica will automatically connect it with the source and target components. Expression properties shown in the bottom section provide the configuration option for General, Incoming Fields, Expression and Advanced properties.

Step 7: In the next step we will write expression transformation to concatenate two strings values representing the first name and last name to an output Field called FullName to show the use of Expression.

Select the Expression option in the properties and click on the + icon to create new by selecting the Field Type as Output Field, entering Name as FullName. Selecting Type as a string and providing the precision as 20 to define the output field size.

Next, we will assign a fixed string value to the value by using the string function available in the Expression option for configuring the output field.

Select the Configure option shown in the Expression section.

Next, to apply the string function in the expression select the ‘built-in function’ option in the second dropdown list of the Field expression window.

Next, select the string option followed by the ‘fx concat’ function from the list. Once the concat function is selected it will appear on the expression editor window. For the example expression, we are entering ‘John’ as string1 and ’Smith’ as string 2 in the Concat function parameter.

The next step is to validate the expression by clicking on the validate button. If there is no syntax issue the green checkbox with ‘The expression’ is valid’ message will appear as shown in the subsequent image. If any issue, you can fix that in the expression editor and validate it again.

Once the expression for the field is valid. Click Ok to return to the Properties View of Expression. The expression can be used with any input ports or fields of available ports for the data manipulation operations.

On the Expression properties window, the field level expression will be shown. Save the mapping to apply the expression.

We have completed how to perform expressions in Informatica.

The output or variable ports can be used to propagate the transformed data to the subsequent transformation or to the target system in the mapping.

Similarly, there are various ways to use the expression that is implemented based upon the business requirements and according to the target systems.

Conclusion

The expression transformation is very useful to develop the custom business logic and apply it to transform the source data and load it on that target system. Because of the variety of features. This transformation is a mostly used transformation in the Informatica mappings and used for complex data manipulation.

Recommended Articles

This is a guide to Expression Transformation in Informatica. Here we discuss the introduction and how to perform expression transformation in informatica? You may also have a look at the following articles to learn more –

1. Mapping in Informatica
2. Router Transformation in Informatica
3. Transformations in Informatica
4. Transformations in Informatica with Example