(When to use ) Unconnected LookUp in Informatica.

One of the readers posted the following question, which is another frequently asked Question in Informatica

What is the absolute necessity of Unconnected lookup, if same functinality can be achived by Connected Lookup Transformation. Please post some real time situations where we decide either to use Connected/Unconnected Lookup Tranformation.

The main advantage of using an unconnected lookup is the fact that you can use it in an expression/any other transformation like a “function” in other programming languages. The Lookup Cache is calculated only once as part of the session initialization and hence more efficient, since all subsequent look-ups will re-use the Cache.

The only constraint is that the cache has to be static, i.e the data in your look-up table shouldn’t be changing once the lookup Cache has been created. Actually, it could change, but the Cache wouldn’t be updated. So the results might be incorrect.

This narrows down the usage of Unconnected Lookup to the following scenarios.
a) When you are looking up against a Static dimension (or any table) that is rarely ever updated
b) When you are looking up against a Base Dimension that is loaded before any of the Facts are loaded
c) The logic used in the Lookup-override is required at a lot of other places.

a) When you are looking up against a Static dimension (or any table) that is rarely ever updated

The most common example for this is the Date Dimension. Usually loaded once when your Data Warehouse goes to Production and very rarely updated.What makes it even more appealing to use unconnected lookup for Date Dimension is the Date Dimension Role Playing.
Example, An online order can have Order Date, Ship Date, Cancelled Date, Recieved Date and so on and the same Date Dimension table plays multiple roles.
Without an unconnected Lookup, here’s how your mapping would look..Also note that the Lookup Is being done on the same table internally (DATE_DIM), but the cache is being calculated one for each lookup.

Using an unconnected Lookup For Date Dimension, this is how it would be transformed..

As you can see, this promotes greater reuse ,a less complex mapping and is more efficient becuase of lesser Caching.

b) When you are looking up against a Base/Conformed Dimension that is loaded before any of the Facts are loaded.

Another example (one which is not static Data) is looking up against any customer Master Data/Conformed Dimension. Your DataWarehouse can have many Confirmed Dimensions, which are loaded before any of the Fact Loads.
One such example is the Employee Dimension. You can lookup the employee key multiple times in a given mapping and this makes a great candidate for Unconnected Lookup.

c) The logic used in the Lookup-override is required at a lot of other places.

This is more of a good coding practise than an Informatica specific “tip”. If your lookup involves a lookup override and you calculate the actual fact using a formula instead of using a direct column value, it would make sense to get the result using an unconnected lookup and use it at multiple places using a :LKP expression.

One such example is when you want to Lookup Revenue based on the Status of the Order instead of a direct Lookup, and you have a look-up override like the one below.

SELECT order_id,
       (CASE WHEN order_status = 'Pending' THEN revenue*0.7
            WHEN order_status = 'COnfirmed' THEN revenue
            WHEN order_status = 'Cancelled' THEN -1*revenue
        END) revenue           
  FROM order_revenue

So if your lookup data is constant during your load and you have a good case for reuse, Unconnected Lookup can be very useful, both in terms of maintainability and efficiency.

This entry was posted in Informatica. Bookmark the permalink.

22 Responses to (When to use ) Unconnected LookUp in Informatica.

  1. Giri says:

    Nice one Rajesh, very informative with very good example.. Thanks for posting this

  2. Zak says:

    Thnx for taking up the topic. Never had so much clarity on unconnected lookup before reading your blog.

  3. maheboob says:

    article is really gud ..

  4. David says:

    Hi-I have a question here.How can you call multiple ports of an unconnected lookup.
    .As far as I know we can only return one port. correct me if my understanding is wrong.

    • Rajesh says:

      Hi David,

      You are correct in saying that we can have only one return port (just like a function can have only one return value). The point I was trying to make above was that you can use the unconnected Lookup multiple times, just like you can call a function multiple times, thereby avoiding a lot of redundant code.

      Does that answer your question?


      • Anuj Chugh says:

        David ,

        we can fetch multiple ports from an unconnected lookup. For this we can update the overrid query by fetching all the required ports concatenated with each other by say ‘~’ within one port of very large precision (say 8000). Then using SUBSTR we can seperate these columns within our expression transformation.

        Anuj Chugh

        • Rajesh says:


          True. I think that answer was more along the lines of what David was looking for. Thanks for your comment.


  5. Suraj says:

    Excellent explanation of Unconnected LookUp. Thanks a lot. keep posting…. :)

  6. Sasmita says:

    excellent explanation

  7. Vadim says:

    good article.
    I would like to add, that Unconnected Lookup is also called Conditional Lookup. Another words, you can choose when (how often) to call it during the load vs. Connected (inline) Lookup which will be called for every row processed by the mapping.
    An example would be a lookup to the target to get the max(surrogate_key). You’d want to do that only once – before any new surrogate_keys are generated.

    IMHO, Conformed dimensional lookups are not good candidates for unconnected lookups. If you have surrogate keys for multiple erroneous conditions, you may need to do some error checking right after the lookup before assigning a surrogate key to source data. A mapplet, with persistent dm_date lookup and an expression transformation (or two) is very re-usable and can be set up to enforce business rules.


    • Rajesh says:

      Agree with the first point. Thanks for adding!

      ….Conformed dimensional lookups are not good candidates for unconnected lookups….
      I have found them to be pretty useful as unconnected look-ups both in terms of reusability and in simplifying the mapping. If your dimension table loads fail, your fact or subsequent processes should not run in the first place, so I don’t see how this will impact things adversely.

  8. rajarao says:

    hi Rajesh,
    Thank you for posting this type of answer with real example.I got clarity on this lookup

  9. Prasad Khedkar says:

    Very good post… cleared concepts thoroughly…..

  10. Avi Taiar says:

    Thanks, Very nice article.
    Not sure about the the example you gave (the date_dim example ) that if you recall the same table in the same for few times – the connected option will be less efficient becuase of more Caching then the unconnected – i think the map bring the table only ones in both cases.

  11. Vidya says:

    Thanks, excellent explanation with examples, appreciate it.

  12. Velava says:

    Superbe explanation. Now i got an clear idea on this Un-Connected Lookup usage.

    Thanks for such useful explanation.

  13. Mahesh says:

    Hi Rajesh,
    I have gone through a few posts of yours…They are very crisp & clear..Thank you and I appreciate your efforts….keep it going….


  14. Naresh Babu Kotha says:

    Good Article

  15. avinash says:

    really gre8 work…thnk u..:)
    Is there any scenario than can acheived only by using uncnnected lookup transformation..

  16. dbkiller says:

    awesome explanation

Leave a Reply

Your email address will not be published. Required fields are marked *


* Copy this password:

* Type or paste password here:

20,932 Spam Comments Blocked so far by Spam Free Wordpress

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>