Understanding Lookup Transformation in Informatica PowerCenter : Connected, Unconnected, Passive, Active

Lookup transformations are essential in Informatica PowerCenter for enriching data by adding related data from a different table. In this post, we'll explore both connected and unconnected lookup transformations using a practical example with employee and department tables.

Tables Used

Employee Table (employee):

emp_id	emp_name	dept_id
1	John Smith	101
2	Jane Doe	102
3	Alice Brown	103

Department Table (department):

dept_id	dept_name
101	Human Resources
102	IT
103	Finance

Connected Lookup Transformation

A connected lookup transformation is directly connected to the data flow pipeline and can return multiple columns. Here are the steps to configure a connected lookup:

1. Create the Mapping: Open Informatica PowerCenter Designer, create a new mapping m_Connected_Lookup, and drag the employee source to the mapping.
2. Add Lookup Transformation: Drag and drop a Lookup transformation into the mapping, name it lkp_Department, and connect the dept_id from the employee source to the Lookup transformation.
3. Configure Lookup Transformation:
   • In the Lookup Table tab, choose the department table.
   • In the Ports tab, configure the ports:
     • I (Input): dept_id from employee.
     • O (Output): dept_name.
     • L (Lookup): dept_id in the department table.
     • R (Return): The port used to return data in unconnected lookups (not used in connected lookups).
   • Set the lookup condition to match dept_id in the employee table with dept_id in the department table.
4. Connect to Target: Create a target table with columns emp_id, emp_name, and dept_name, and connect the respective fields.
5. Workflow Configuration: Create a new workflow wf_Connected_Lookup, add a session for the mapping m_Connected_Lookup, configure the session, and run the workflow.

Unconnected Lookup Transformation

An unconnected lookup transformation is not directly connected to the data flow pipeline and returns only one column value. Here are the steps to configure an unconnected lookup:

1. Create the Mapping: Open Informatica PowerCenter Designer, create a new mapping m_Unconnected_Lookup, and drag the employee source to the mapping.
2. Add Lookup Transformation: Drag and drop a Lookup transformation into the mapping, name it lkp_Department_Unconnected.
3. Configure Lookup Transformation:
   • In the Lookup Table tab, choose the department table.
   • In the Ports tab, configure the ports:
     • I (Input): dept_id from employee.
     • R (Return): dept_name from department.
   • Set the lookup condition to match dept_id in the employee table with dept_id in the department table.
4. Create an Expression Transformation: Drag and drop an Expression transformation into the mapping, name it exp_Call_Lookup, and add a new output port dept_name to call the unconnected lookup: :LKP.lkp_Department_Unconnected(dept_id).
5. Connect to Target: Create a target table with columns emp_id, emp_name, and dept_name, and connect the respective fields.
6. Workflow Configuration: Create a new workflow wf_Unconnected_Lookup, add a session for the mapping m_Unconnected_Lookup, configure the session, and run the workflow.

Summary

By following these steps, you can configure and utilize both connected and unconnected lookup transformations in Informatica PowerCenter, effectively enhancing your data integration and management processes.

When to Use Connected Lookup vs. Unconnected Lookup

The choice between connected and unconnected lookup transformations depends on the specific requirements of your data integration task:

• Connected Lookup:
  • Use when multiple columns need to be returned from the lookup table.
  • Ideal for scenarios where the lookup is required for most of the rows being processed.
  • Useful when the lookup transformation is part of the data flow and needs to be connected to other transformations.
• Unconnected Lookup:
  • Use when only one column needs to be returned from the lookup table.
  • Suitable for scenarios where the lookup is not required for every row, improving performance by calling the lookup only when necessary.
  • Useful when the lookup logic is needed in multiple mappings, allowing reuse of the same lookup transformation.

Understanding Active Lookup Transformation

While lookup transformations are generally considered passive, they can be configured to be active under certain conditions:

If your lookup table has duplicate entries, such as multiple rows with the same key, you can configure the lookup transformation to handle these duplicates in different ways:

• Return All Matching Values: This makes the lookup transformation active because it can change the number of rows being processed by returning multiple matches for a single input row.
• Return First Matching Value: The transformation remains passive, as it returns a single row for each input row, regardless of the number of matches in the lookup table.
• Return Last Matching Value: Similar to returning the first matching value, this keeps the transformation passive by ensuring a single row is returned for each input row.
• Throw an Error: The transformation can be set to generate an error if multiple matches are found, ensuring data integrity by not allowing duplicates.

For example, consider a department table with duplicate entries for department ID 20:

dept_id	dept_name
20	Sales
20	HR

If the lookup is configured to return all matching values, the lookup transformation will be active, as it can produce multiple output rows for a single input row:

• Input Row: 20
• Output Rows: 20, Sales and 20, HR

Thus, the lookup transformation becomes active by dynamically changing the number of rows based on the lookup results.

Use Case: Replacing Zip Code with Location

Let's consider a use case where you have a source table with a column for zip codes and a lookup table that contains the corresponding location names. You want to either replace the zip code column with the location name or add the location name alongside the zip code. Here's how you can determine whether to use a connected lookup, unconnected lookup, or joiner transformation:

Connected Lookup

Use a connected lookup when:

• You need to return multiple columns from the lookup table.
• You want to replace the zip code column with the location name directly in the data flow.
• You are performing the lookup for most or all rows in your source table.

Example: You want to add the location name to each row in your source table based on the zip code.

Unconnected Lookup

Use an unconnected lookup when:

• You only need to return a single column from the lookup table.
• You are performing the lookup conditionally, not for every row.
• You need to reuse the lookup logic across multiple mappings.

Example: You want to perform the lookup only for specific rows based on a certain condition.

Joiner Transformation

Use a joiner transformation when:

• You need to join data from two heterogeneous sources.
• You are dealing with large datasets where a join operation might be more efficient.
• You want to combine columns from both the source and lookup tables in the output.

Example: You want to create a result set that includes both the zip code and location name columns, effectively merging the two tables.

Decision Criteria

To decide which approach to use, consider the following:

• If you need to enrich your source data by adding or replacing columns with lookup data, a connected lookup is usually appropriate.
• If you need to perform lookups conditionally or reuse lookup logic, an unconnected lookup might be more suitable.
• If your task involves merging data from two large tables, especially from different sources, a joiner transformation can be more efficient and straightforward.

In summary, the choice between connected lookup, unconnected lookup, and joiner transformation depends on the specific requirements of your data integration task, including the nature of the data, the desired output, and the performance considerations.

Unconnected Lookup Transformation: Practical Example

Unconnected lookup transformations in Informatica are used when you need to perform a lookup operation on specific conditions rather than for every row. Here, we will provide a practical example using an employee table and a department table.

Example Scenario

Imagine you have the following two tables:

Employee Table (emp)

EmpID	Name	DeptID	ZipCode
1	John Doe	10	12345
2	Jane Smith	20	67890
3	Mike Johnson	30	13579

Department Table (dept)

DeptID	DeptName
10	Finance
20	HR
30	IT

Requirement

You want to look up the department name for employees based on the DeptID in the employee table but only for employees in the 'HR' department. For this, an unconnected lookup is suitable because it allows conditional lookups.

Steps to Configure Unconnected Lookup

1. Create the Lookup Transformation:

   Create a Lookup transformation on the Department table (dept). This transformation will be unconnected, meaning it won't be directly connected to the data flow.

   • Select the DeptID and DeptName columns from the Department table.
   • Configure the lookup condition to match DeptID.
2. Define Return Port:

   Set the DeptName as the return port in the Lookup transformation. This port will return the department name for the matched DeptID.

3. Create an Expression Transformation:

   Add an Expression transformation to the mapping. This transformation will call the unconnected Lookup transformation conditionally.

   • Create a new port in the Expression transformation to call the Lookup transformation, e.g., LKP_DEPTNAME.
   • Use the :LKP expression to call the Lookup transformation and pass the DeptID as a parameter.
   • Conditionally call the Lookup transformation only if the DeptID is 20 (HR).

   IIF(DeptID = 20, :LKP.LKP_DEPT(DeptID), 'Not HR')
         

4. Map the Output:

   Map the output of the Expression transformation to the target table or further transformations as needed.

Advantages of Unconnected Lookup

• Allows conditional lookups, improving performance and efficiency.
• Can be reused across multiple mappings, reducing redundancy.
• Useful when lookup is required for a subset of data.

Conclusion

Unconnected lookup transformations in Informatica provide a flexible way to perform conditional lookups. In our example, we used an unconnected lookup to retrieve department names for employees only if they belong to the 'HR' department. This approach is efficient and reduces unnecessary lookup operations, enhancing overall performance.