EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Informatica Tutorial Lookup in Informatica
 

Lookup in Informatica

Priya Pedamkar
Article byPriya Pedamkar

Updated March 27, 2023

Lookup in Informatica

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

Login in to Informatica

  • After a successful login, it will show the landing dashboard for Data integration.

Dashboard

  • Select Data IIntegration Option to view detailed options. This will have the provision to create new mappings or projects.

Mappings or projects

  • Next click on New ->Mappings->Create to create a new mapping.

Create New Mappings

  • It will show the Informatica designer view on the next screen.

Designer View

  • 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.

Mapping properties section

  • 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

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

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.

Lookup in Informatica

  • 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.

Lookup in Informatica

  • 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

Lookup in Informatica

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 –

  1. Transformations in Informatica with Example
  2. Lookup in MongoDB
  3. Informatica Architecture
  4. Careers In Informatica

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW