The Unconnected Lookup Transformation in Informatica is nothing but a programming function with parameters, and in order to call this unconnected lookup, we have to use the Expression transformation.
In this article, we will show you, How to perform unconnected 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 the Lookup operation and the Lookup table
Employees Table inside the Source Database is:
Our Lookup table will be the Department Table inside the Database:
In this Informatica unconnected lookup transformation example, our task is to load [First Name] and [Last Name] from the source (Employee table) table into the target table and also [Department Name] from the Department table. Of course, we can achieve the same using the Full Outer Join, but we will show you the alternative i.e., unconnected Lookup Transformation in Informatica.
Unconnected Lookup Transformation in Informatica Example
Before we start configuring the unconnected Lookup Transformation in Informatica, First connect to the Informatica repository service.
To connect with the Repository service, we must provide Admin Console credentials. So, Please provide the appropriate Username and Password and click on Connect button as shown below.
TIP: Here, you must provide the Admin Username and password specified while installing the Server.
Step 1: Creating Source Definition for Unconnected Lookup Transformation in Informatica
Once you have connected successfully, Please navigate to Source Analyzer and define your Sources. In this Unconnected Lookup in Informatica example, we use the Employee 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: Create a Target Definition for Informatica Unconnected Lookup Transformation
Please navigate to Target Designer and define the Target. In this example, we use the already created SQL table (Unconnected Lookup) as our target definition. Please refer Create Target table using Source Definition to understand the steps involved in creating a target definition.
Step 3: Create Informatica unconnected Lookup Transformation Mapping
In order to create a new mapping, Please navigate to the Mappings menu in Menu Bar and select the Create.. option. It will open the Mapping Name window as shown below. Here, you must write a unique name for this mapping (m_Unconnected_Lookup) and click the OK button.
TIP: Please refer Mapping article to understand the steps involved in creating Mapping.
Next, Drag and drop the [Employee] 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 Unconnected Lookup Transformation in Informatica
In order to create Lookup Transformation in Informatica, Please navigate to the Transformation menu in Menu Bar and select the Create.. option as shown below.
Once you click on the Create.. option, the Create Transformation window will open, as shown below. Please select the Lookup Transformation from the drop-down list, specify the unique name (LKP_Department) for this transformation, and click the Create button.
Once you click the Create.. button, a Select Lookup Table for Lookup Transformation window will open, as shown below. Here, we are selecting the department table present in the Source.
Once you click the Create button, the Lookup Transformation will add to the mapping designer. Since we are explaining the unconnected lookup transformation, there is no need to connect with the source qualifier.
The below image shows the list of available options in the Informatica unconnected lookup transformation Ports tab. Here, we added one new Input port (IN_DeptID) and removed the id from the output port.
Within the condition tab, we added one condition id = IN_DeptID. The below condition states that whenever this lookup transformation is called, they have to provide the parameter value of the integer data type.
Once they pass the value, the Lookup transformation will check whether the value is equal to id or not, and if the condition is TRUE, then it will return the corresponding department name; otherwise, NULL will return.
Step 3(b): Creating Expression Transformation
It is a very important step in Unconnected Lookup Transformation in Informatica.
Please refer Expression Transformation article to understand the steps involved in creating it. Once you have created it, Please connect the Source qualifier with the expression transformation by dragging the required fields.
We are adding [First Name], [Last Name] and [DepartID] to the expression transformation. Double click on the Expression transformation to write the custom expressions.
From the below screenshot, you can observe that we created New Output Column called the Department name. We will use this column to store the department name we get from the Informatica unconnected lookup transformation.
Click on the arrow button beside the Mapping Name as we have shown below to write the custom expression.
This will open the Expression Editor to write a custom expression. Here, we want to get the Department Name from Lookup Transformation.
To do this, Go to the Functions tab, search for the Lookups folder, and select the Lookup we created earlier. As we all know, we assigned the condition in Lookup transformation. It means we have to provide the parameter value of the type integer.
TIP: Lookup transformation will check the condition id = DepartID, and if the condition is TRUE, it will return the corresponding department name; otherwise, NULL will return.
Once you finish writing the expressions, Click OK to close the transformation window. Next, Drag and drop the target definition (Unconnected Lookup) from the 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 Informatica unconnected lookup mapping by going to the Mapping Menu bar and selecting the Validate option.
Step 4: Creating a Workflow for Unconnected Lookup
Once we finish creating the Mapping, we have to create the workflow. PowerCenter Workflow manager provides two approaches to creating a workflow.
In this Informatica Unconnected Lookup Transformation example, we will create the Workflow manually. Once we have created the workflow, our next step is to create a session task for our mapping.
Step 4(a): Creating Session for Unconnected Lookup Transformation in Informatica
There are two types of sessions:
For this Unconnected Lookup in Informatica example, we will create Non-reusable Session. So please navigate to Tasks Menu and select the Create option to open the Create Task window. Here you have to choose the Session as Task type (default) and enter a unique name (S_Unconnected_Lookup) for the session.
Once you click the Create button, a new Mappings window will open. You must select the mapping you want to associate with this session. In this Informatica unconnected lookup transformation, we choose the mapping (m_Unconnected_Lookup) 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 will not explain them here. Instead, we strictly recommend visiting the Session article to understand the properties.
The unconnected lookup transformation in Informatica workflow is a valid one. Now, Let us start the Workflow by navigating to workflow menu and selecting the Start Workflow option.
Once you select the Start Workflow option, the PowerCenter Workflow monitor will open to monitor the workflow. And our Informatica Unconnected Lookup workflow executes without any errors.
Let us open the SQL Server Management Studio to check whether we successfully stored the information from both Source and lookup table using the unconnected lookup transformation in Informatica or not.