Updated March 27, 2023
Introduction to Lookup in Informatica
Lookup in Informatica is a transformation mechanism to find the reference value using the source value to complete a dataset for target load or business data validations. Informatica power center is the widely used ETL tool with several transformations, Informatica Cloud is the latest version of the Data integration product offering from the company Informatica.
The lookup is one of the important transformations. It belongs to the passive transformation category. Lookup Transformation can process lookup reference checks and return data from flat files, database tables, views and saas based cloud applications like Salesforce.
The Informatica Integration service queries the Lookup sources based upon the source field values, also known as the lookup ports and the lookup conditions configured during the data integration pipeline development.
There are several versions of the Informatica Power center which is popular. With the advent of cloud technologies, the Informatica cloud is increasingly trending and one of the leading tools for data integration in the industry. We will discuss the lookup transformation in Informatica Cloud in this article.
Types of Lookup Transformation
Lookup Transformation can be categorized based on source type, connection type, and cached type.
Let us start with Lookup transformation based upon source type; there are mainly 3 types.
Field Lookups from Salesforce
- Informatica cloud has a lookup feature for reference filed lookup while integrating with Salesforce CRM application which is an out of the box feature in the data synchronization service of Informatica cloud.
Lookup from Flat Files
- The source of lookup is the flat file, preferably comma-separated CSV files.
- First, select the Schema or the file definition of the flat file from the repository.
- Alternatively, import the file while creating the transformation for the schema definition.
- The Informatica power center designer uses the flat file wizard to process the lookup.
Lookup from RDBMS,
- The source of lookup is a table.
- Informatica connects with the database through connector or ODBC Driver.
- It connects and fetches the table schema definition of the lookup reference table.
Now we will discuss the Lookup Type based upon the Connection Type. There are two types.
Connected Lookup
- Connected lookup is part of the ETL pipeline mapping of the Informatica cloud.
- It receives source value, processes the lookup transformation and returns the data to the pipeline flow.
- The connected lookup is connected though lookup ports.
- It returns default value in case of no matching scenario.
- It can return multiple columns as lookup transformation.
Unconnected Lookup
- The unconnected lookup transformation is not connected to the flow-through source or target.
- A checkbox option can manage it in the general tab of lookup transformation.
- Informatica cloud manages lookup transformation by return only one matching value to the calling transformation.
Additionally, there are lookup cache options available in the advanced tab of lookup properties which can be configured to optimize the flow performance.
The Business Scenarios for Lookup in Informatica Cloud
Now, as we understand the lookup transformation in Informatica, let’s discuss in detail what are the business scenarios and technical requirements where this transformation is useful while developing a data integration pipeline using Informatica Cloud.
1. Field Lookup
- As Informatica cloud is highly used with Salesforce integration, lookup functionality is used to get SalesForce Ids or relevant fields based upon Lookup condition.
2. Getting Reference Value from Database
- To retrieve the value from the lookup reference based on a value in the source.
- Example: The source has CustomerID, Retrieve customer Email from the CustomerAddressLookup Table.
3. Business Validation
- Validating data against a lookup reference to comply with business rules or compliance in the data integration pipeline.
- Example: Checking the product rating score through the lookup with benchmark rating data.
4. Calculated Column Creation
- To retrieve a value from a lookup reference and use it in a calculation and generate a calculated value.
- Example: Calculating the discount price of a product for a retail business through discount percentage data.
Example of Lookup Transformation in Informatica Cloud
This example will show the stepwise approach in Informatica Cloud or latest known as Informatica intelligent cloud services,
We will discuss the example scenario to lookup product names from the local CSV file for a source SalesData CSV file to generate another target CSV file that will contain order data with a complete product name. This will be a connected lookup approach.
Before going to the Informatica cloud console, let’s understand source data, lookup data, and Expected Target Data using ETL Mapping specification, which is represented in the following tables.
| Source(Flat File) | |
| FileName | Column Name |
| SalesData.csv | OrderNumber |
| CustomerId | |
| transaction data | |
| ProductId | |
| Qty | |
| Price | |
| Target(Flat File) | |
| FileName | ColumnName |
| SalesDataComplete.csv | OrderNumber |
| CustomerId | |
| transaction data | |
| ProductId | |
| ProductName | |
| Qty | |
| Price | |
| Lookup Source(Flat File) | |
| FileName | Column Name |
| ProductLookup.csv | ProductId |
| ProductName | |
| ProductDescription | |
Now we will discuss the step by step approach developing the mapping with Lookup in Informatica cloud.
- Login to your Informatica cloud instance through your Informatica credentials.
- After a successful login, it will show the landing dashboard for Data integration.
- Select Data IIntegration Option to view detailed options. This will have the provision to create new mappings or projects.
- Next click on New ->Mappings->Create to create a new mapping.
- It will show the Informatica designer view on the next screen.
- Provide a Meaningful Name and proper description in the Mapping properties section.
- Next, we will add the lookup transformation from the designer option to the pipeline.
- Next, we will configure the Source, lookup and target properties to complete the mapping.
- Source configuration, we will use a local CSV file SalesData.csv, through the properties tab.
- Source Data: SalesData.csv
- Create a connection for a flat-file source is a prerequisite task for the properties configuration.
- The new connection can be created from the Home section of the Data integration view and the Informatica Administration viewer existing connection can be used.
- The configuration for flat-file connection depends on the Informatica secure agent service, which is a downloadable component. The setup needs to be done Windows or Linux system before using the Connection.
- The secure agent services should be in Up and Running state for connection to work properly.
- Lookup Data sample: ProductLookup.csv
- Lookup Configuration, we will select the source file for lookup, the relevant filed and add a condition in the lookup properties tab.
- Configure the Flat-file connection for ProductLookup CSV file
- Provide the condition for ProductID with = operator option.
- Target configuration, We will configure target properties for the SalesDataComplete.csv file.
- Similarly, configure the target connection properties and mapping fields.
- Create the field level mappings for incoming fields to the target fields.
- Once the mapping complete,
- Save and run the mapping to get the target result in the SalesDataComplete.csv file.
- Also, you can create a task to run the mappings in Informatica through the scheduler.
After the job’s successful run, the Target flat file will be populated with the relevant data.
- Target Data: SalesDataComplete.csv
It completes the Example for Lookup Transformation in the Information cloud.
Conclusion
As the lookup Transformation in Informatica evolving with additional features with new releases, it’s important to check updates with official Informatica document releases or Product documentation.
Recommended Articles
This is a guide to Lookup in Informatica. Here we discuss the types of Lookup Transformation and examples of Lookup in Informatica. You can also refer to our other related articles to learn more –
