Intended audience: Informatica professionals with 0 to 1 year of experience. Advance ETL or data warehouse professional should avoid reading this. This has a very basic description of lookups, which can build understanding for need and type for a lookup in general. Lookup is one generic term and can be learnt as exact as word says. There are two type of Informatica lookup called connected and unconnected lookups.
If you are aware of Lookup transformation, you must be surely aware that Lookup Transformation could be designed in two modes – Connected / Pipeline and Unconnected. Let’s find the differences between Connected and Unconnected Lookup transformation to effective choose the mode of Lookup transformation in Informatica.
When do you go for Un-connected Lookup?
When you require only one column from the Lookup table.
When you want to use the Lookup multiple times within the same mapping.
What are the differences Connected Vs UnConnected Lookup?
There are quite a many differences between Connected and Unconnected Lookup transformation, you can choose the best fit mode based on your requirement. Some of those differences are below –
How to choose the Lookup type?
The below algorithm can very well help you to choose the right type of the Lookup for your Informatica mapping code.
Verbose description of the above algorithm –
If number of columns to return from the Lookup table are more than, you can opt Connected Lookup Transformation.
If you are looking up your target table and there is a need of Dynamic caching – you can always opt Connected Lookup.
If you are required to return one output port and no dynamic caching is required, and you need multiple instances of the Lookup transformation then you can always go for Un-Connected Lookup transformation.
Points that can help you to tune your Lookup Transformation
Flat file Lookups are always cached, so you do not really hesitate much in choosing the Lookup type in this case.
Partitioning the Flat file source which is used as Lookup will greatly help with respect to the performance.
Flat file Lookups are always cached, so I would not prefer Connected / Unconnected if my Lookup source is Flat File.
The first question that pops up is “How huge is the data?” Quite a many times you end up with huge data volumes that practically do not allows you to use the caching feature from the performance point
Partitioning in Informatica again greatly helps
In case of absence of Informatica partitions, you can very well scale the advantages of your DB partitions to improve your session performance.
Do let us know if you want us to have something added here ? and i hope it might help to build little understnaidng for informatica connected and unconnected lookups.