Unconnected Lookup Transformation in Informatica

The Unconnected Lookup Transformation in Informatica is nothing but a programming function with parameter 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 Lookup operation and the Lookup table

Employees Table inside the [Informatica Source] Database is:

Unconnected Lookup Transformation in Informatica Source 1

Our Lookup table will be the Department Table inside the [Informatica Source] Database:

Unconnected Lookup Transformation in Informatica Source 2

In this 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. We can achieve the same using the Full Outer Join also 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 Informatica repository service.

In order to connect with the 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.

Unconnected Lookup Transformation in Informatica Admin Panel

Step 1: Creating Source Definition for Unconnected Lookup Transformation in Informatica

Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this Unconnected Lookup in Informatica example, we are using the Employee table from SQL Server database as our source definitions.

Please refer Database Source in Informatica to understand the steps involved in creating source definition

Unconnected Lookup Transformation in Informatica 0

Step 2: Create a Target Definition for Informatica Unconnected Lookup Transformation

Please navigate to Target Designer and define the Target. In this example, we are using 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

Unconnected Lookup Transformation in Informatica 1

Step 3: Create Informatica unconnected Lookup Transformation Mapping

In order to create a 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 a unique name for this mapping (m_Unconnected_Lookup) and click OK button.

TIP: Please refer Informatica Mapping article to understand the steps involved in creating Mapping.

Unconnected Lookup Transformation in Informatica 2

Next, Drag and drop the [Employee] source definition from 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 as we shown below.

Unconnected Lookup Transformation in Informatica 3

Once you click on the Create.. option, Create Transformation window will be opened as we shown below. Please select the Lookup Transformation from the drop-down list and specify the unique name (LKP_Department) for this transformation and click on Create button

Unconnected Lookup Transformation in Informatica 4

Once you click on the Create.. button, a Select Lookup Table for Lookup Transformation window will be opened as shown below. Here, we are selecting the department table present in the Source

Unconnected Lookup Transformation in Informatica 5

Once you click on the Create button, the Lookup Transformation will be added to the mapping designer. Since we are explaining about the unconnected lookup transformation there is no need to connect the transformation with the source qualifier.

Unconnected Lookup Transformation in Informatica 6

Below screenshot will show you the list of available options in the Ports tab. From the below screenshot you can observe that We added one new Input port (IN_DeptID) and removed the id from the output port.

Unconnected Lookup Transformation in Informatica 7

Within the condition tab, we added one condition id = IN_DeptID. Below condition states that whenever this lookup transformation is called they have to provide the parameter value of integer data type.

Once they pass the value, 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 be returned.

Unconnected Lookup Transformation in Informatica 8

Step 3(b): Creating Expression Transformation

Please refer Expression Transformation in Informatica article to understand the steps involved in creating it. Once you created it, Please connect the Source qualifier with the expression transformation by dragging the required fields.

From the below screenshot you can observe that we are adding [First Name], [Last Name] and [DepartID] to the expression transformation. Double click on the Expression transformation to write the custom expressions.

Unconnected Lookup Transformation in Informatica 9

From the below screenshot you can observe that we created New Output Column called Department name. We are going to 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 shown below to write the custom expression

Unconnected Lookup Transformation in Informatica 10

This will open the Expression Editor to write a custom expression. Here, we want to get the Department Name from the Lookup Transformation.

To do this, Go to the Functions tab and search for Lookups folder and select the Lookup that we created earlier. As we all know that, we assigned the condition in Lookup transformation. It means we have to provide the parameter value of type integer.

TIP: Lookup transformation will check the condition id = DepartID and if the condition is TRUE then it will return the corresponding department name otherwise NULL will be returned.

Unconnected Lookup Transformation in Informatica 11

Once you finish writing the expressions, Click OK to close the transformation window. Next, Drag and drop the target definition (Unconnected Lookup) from Targets folder to the mapping designer and connect the expression transformation with the target definition. Please use the Autolink.. option to connect them.

Unconnected Lookup Transformation in Informatica 12

Before we close the Mapping, Let us validate the Informatica unconnected lookup mapping by going to Mapping Menu bar and selecting the Validate option.

Step 4: Creating a Workflow for Unconnected Lookup 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 Unconnected 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 Unconnected Lookup Transformation in Informatica

There are two types of sessions in Informatica:

For this Unconnected Lookup in Informatica 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_Unconnected_Lookup) 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_Unconnected_Lookup) that we created earlier (in Step 3).

Unconnected Lookup Transformation in Informatica 13

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 to visit Session in Informatica article to understand the properties.

From the below screenshot you can observe that the unconnected 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.

Unconnected Lookup Transformation in Informatica 15

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 Informatica Unconnected Lookup workflow is executed without any errors.

Unconnected Lookup Transformation in Informatica 16

Let us open the SQL Server Management Studio to check whether we successfully stored the stored the information from both Source and lookup table using the unconnected lookup transformation in informatica or not.

Unconnected Lookup Transformation in Informatica 17

Comments are closed.