What are the differences between connected look up and unconnected look up?
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.
- 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.
- 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.
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…
Transformations can be connected to the data flow, or they can be unconnected.
An unconnected transformation is not connected to other transformations in the mapping.
An unconnected transformation is called within another transformation,
and returns a value to that transformation.
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.
create or replace procedure post_load_procedure (i_tabname in user_tables.table_name%type) as
insert into load_status_XXXX (table_name, load_date, process_date ) values
(i_tabname, sysdate, sysdate–1);
ownname => ‘SCOTT’,
tabname => i_tabname,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
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 :
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”.
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.
A connected Lookup transformation receives source data, performs a lookup, and returns data to the pipeline.
An unconnected Lookup transformation is not connected to a source or target.
A transformation in the pipeline calls the Lookup transformation with a :LKP expression.
The unconnected Lookup transformation returns one column to the calling transformation
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.