What are the differences between connected look up and unconnected look up?

What are the differences between connected look up and unconnected look up?

Asked on November 13, 2018 in Informatica.
Add Comment

  • 2 Answer(s)

        The main difference  is that in Connected Lookup, it can return/pass multiple rows/groups of data but in unconnected lookup it can return only one port.

    Connected Lookup:

    • Receives input values directly from the pipeline.
    • We can have a dynamic or static cache.
    • Cache enables all lookup columns which are used in the mapping .
    • It can return multiple columns from the same row or insert into the dynamic lookup cache.

    Unconnected lookup:

    • Receives input values from the result of another transformation.
    • We can have a static cache.
    • Cache enables all lookup/output ports in the lookup condition.
    • It uses only one return port . It also returns one column from each row.
    Answered on November 13, 2018.
    Add Comment

    connected and unconnected Transformation.

    The way to differentiate between the two is rather simple. Take a look at the mapping and see if they are “connected” to the data flow or not. In other words, is there data going in and coming out of the transformation and is it being fed to a next transformation.

    From the Informatica Documentation…

    Apart from being called from within another transformation, an unconnected transformation also include those that can be called in the beginning or end of the session to do things like calculate statistics, build/drop indexes etc. A common example for this is an (unconnected) external stored procedure Transformation.

    One of the practical cases for this is the stored procedure that will be executed at the end of the load to collect the table statistics and to make an entry into a load status table . You can compile this as a stored procedure with an input parameter for table name and include it in the mapping using an external stored procedure transformation.

    Most transformations that we use in mappings are “connected Transformations” as they are involved in the data flow. An input is passed to them, processed based on what the transformation is and the output is moved on to the next downstream transformation.

    Here is the list of transformations that can be either connected (or) unconnected…

    Stored Procedure Transformation :
    Connected :
    Stored procedures / functions like calculate_commission based on a set of complex rules. If it is already present in your database and you do not want to “re-code” the same set of logic again, you can import it as a stored procedure and pass input to it and get the output . This is then passed on to the next transformation, hence “connected”.

    Unconnected :
    Examples include
    a) truncate table before the load using a stored procedure that executed pre-session.
    b) Compute statistics and make load status entries after the load..etc.

    LookUp Transformation : Can be connected or unconnected.

    From the Informatica Documentation
    Connected or unconnected lookup.

    Connected Lookup : As the above quote indicates, a connected lookup is a part of the data flow and is connected to other transformations. It is generally used when you want to get multiple columns from a particluar source file / table / pipeline ) based on a lookup key. Eg.. Let’s say you want to pass in a department ID and get the department name and location for it, Connected Lookup would be the way to go.

    Unconnected Look Up Transformation : Unconnected lookup is generally used when the result of a query is a single column. More importantly, if it returns values like that indicate “Does the record exist”, “what’s the value of this column” and so. It can return only one input , but it is reusable and can save you a lot of effort in some cases where it’s applicable.

    Answered on February 22, 2019.
    Add Comment

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.