The Lookup Transformation in Informatica is very useful to look up data present in Flat Files, Relational tables and Views. In this article, we will show you, How to perform the lookup operation on the SQL server database table using the Lookup Transformation in Informatica with an example.
Before we start designing the Mapping let us look at our source table on which we are going to perform Informatica Lookup operation and the lookup table
Our Lookup table will be the table inside the Source Database is:
Our source table will be the below table inside the Database is:
In this Informatica Lookup Transformation example, our task is to compare the Country name present in the Source table with a Lookup table and
- If they match then we will load them in [Lookup Matched Rows] target table along with Country code present in the Lookup table
- If they don’t match then we will load them in [Lookup Unmatched Rows] target table
TIP: You can extend this functionality by performing some operations on Non-matching records and store them in a matched table using the Union Transformation.
Lookup Transformation in Informatica Example
Before we start configuring the Lookup Transformation in Informatica, First connect to the Informatica repository service. In order to connect with the Repository service we have to provide the 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 Server.
Step 1: Creating Informatica Lookup Transformation Source Definition
Once you connected successfully, Please navigate to Source Analyzer and define your Sources.
In this Informatica Lookup Transformation example, we are using the table from the SQL Server database as our source definitions. Please refer to Database Source to understand the steps involved in creating a source definition
Step 2: Creating a Target Definition for Informatica Lookup Transformation
Please navigate to Target Designer and define the Target. In this Informatica Lookup Transformation example, we are using the already created SQL tables ([Lookup Matched Rows] and [Lookup Unmatched Rows]) as our target definitions. Please refer Create Target table using Source Definition to understand the steps involved in creating a target definition
Step 3: Creating Informatica Lookup Transformation Mapping
In order to create a new mapping for Informatica Lookup Transformation, 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 a unique name for this mapping (m_Lookup_Transformation) and click OK button.
TIP: Please refer Mapping article to understand the steps involved in creating Mapping
Next, Drag and drop the source definition from the Sources folder to the mapping designer. Once you drag the source, the PowerCenter designer will automatically create the default transformation called source qualifier.
Step 3(a): Creating Lookup Transformation in Informatica
In order to create Lookup Transformation in Informatica, Please navigate to Transformation menu in Menu Bar and select the Create.. option.
Once you click on the Create.. option, Create Transformation window will be opened as we shown below. Please select the Informatica Lookup Transformation from the drop-down list and specify the unique name (LKP_Country) for this transformation and click on Create button
Once you click on the Create.. button, a Select Lookup Table for Lookup Transformation window will be opened as shown below. Here, we want to import a relational table from SQL database so, click on Import and select From Relational Table.. option
TIP: If your lookup table is present in Source or Target then select it.
Once you select From Relational Table.. option, Import tables window will be opened. Here we are selecting our lookup table called [LookupTransformationSource]. Please refer Import Data from Relation Database article to understand the steps involved in it.
Once you click on the Create button, the Informatica Lookup Transformation will be added to the mapping designer. Double click on it to configure its settings.
The list of available options in the Ports tab. We added one new Input port (IN_CountryName) in this Informatica Lookup Transformation.
Within the Properties section, we change the Connection Information and assigned it with the $Source variable.
Within the condition tab we added one condition CountryName = IN_CountryName. Below condition states that it will check the lookup input source (i.e., Country Name) is equal to existing Country Name or Not, and if the condition is TRUE then it will return the corresponding Country Code.
Once you finish configuring the Informatica Lookup transformation, Please drag the Country Name field from the Source Qualifier to the IN_CountryName field in the lookup table
Step 3(b): Creating Router Transformation in Informatica
Please refer Router Transformation article to understand the steps involved in creating it. Once you created it, Please connect the Source qualifier with the router transformation by dragging the required fields.
From the below Informatica Lookup Transformation screenshot you can observe that we are adding all the columns from the Source Qualifier and Cid, Country Name from a Lookup table.
Double click on the Router transformation to add Groups. From the below screenshot, you can observe that we created one group for Non-Matching records and one default group.
Let us provide the expression or condition for the First group (Non-Matching) by clicking the arrow button as we shown below. This will open the Expression Editor to write a custom expression.
Here, we want the records whose Country Name is not present in the Lookup table. So, we are writing the below-shown expression (ISNULL(CountryName). It means all the Null values will be placed in Non Matched Group and Not null values will pass to Default Group.
Once you finish configuring the properties, Click OK to close the transformation window.
Next, Drag and drop the target definitions ([Lookup Matched Rows] and [Lookup Unmatched Rows]) from Targets folder to the mapping designer as shown below.
Next, Please connect the Informatica Lookup Transformation (Output Group 1) with the [Lookup Unmatched Rows] table and Default Group with Router [Lookup Matched Rows].
TIP: You can use the Autolink.. option to link those fields.
Before we close the Mapping, Let us validate the mapping by going to Mapping Menu bar and selecting the Validate option.
Step 4: Creating a Workflow for Informatica Lookup Transformation
Once we finish creating the Mapping for Informatica Lookup Transformation, we have to create the workflow for it. PowerCenter Workflow manager provides two approaches to create a workflow.
In this Informatica Lookup 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): Creating Session for Lookup Transformation in Informatica
There are two types of sessions:
- Non-reusable Session Task: Please refer Session article
- Reusable Session Task: Please refer to Reusable Session article
For this Informatica Lookup 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_Lookup_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.
In this Informatica Lookup Transformation example, we are selecting the mapping (m_Lookup_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 are not going to explain them here. We strictly recommend visiting the Session article to understand the properties.
From the below screenshot you can observe that the lookup 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, the PowerCenter Workflow monitor will be opened to monitor the workflow. And our Informatica Lookup Transformation workflow is executed without any errors.
Let us open the SQL Server Management Studio to check whether we successfully stored the unmatched records or not.
Let us open the SQL Server Management Studio to check whether we successfully stored the matched records using the lookup transformation in Informatica or not.
