Look up Transformation FAQs

Look up Transformation FAQs

Lookup Transformation : It is a passive and both connected and unconnected transformation. It is used to lookup data from in a relational table, view or synonym. Lookup definition can be imported either from source or target tables

In lookup which one is better one connected and unconnected lookup?
performance wise i am asking?if i am using five connected lookup in mapping that time what happens?
Ans>Exactly Depends.

Definitely unconnected is better performance wise. But its not wise to use 5 unconnected lookups. In this case better to use one connected with the required 5 ports as output port. If u need only one or two output port, you can use unconected for better performance.
Ashok>how can u say that unconnected is better performance then connected lookup. in connected lookup we are going to use only lookup table in unconnected we are going to add an expersion with lookup table then the performance in unconnected lookup is decreased due to another transformation than connected lookup
Vinod >It depends on the scenario u r using, if u want to lookup multiple times for the same condition in one mapping it is better to use unconnected lookup and in the situation like when u want multiple return values then u can only use connected lookup. In connected lookup, we can use both static and dynamic cache but in case of unconnected lookup, we can use only static cache.


=================================

Can any one explain about the new port that is created in the lookup after enabling the dynamic lookup cache?and what is the associate port??????

Hi,Associate port is either an i/p or o/p . Data in this port is used by IS[Infa Server] to insert or update lookup cache


Manasa Says> A Lookup transformation using a dynamic cache has the following properties:
New Lookup Row. The Designer adds this port to a Lookup transformation configured to use a dynamic cache. Indicates with a numeric value whether the PowerCenter Server inserts or updates the row in the cache, or makes no change to the cache. To keep the lookup cache and the target table synchronized, you pass rows to the target when the NewLookupRow value is equal to 1 or 2. Associated Port. Associate lookup ports with either an input/output port or a sequence ID. The PowerCenter Server uses the data in the associated ports to insert or update rows in the lookup cache. If you associate a sequence ID, the PowerCenter Server generates a primary key for inserted rows in the lookup cache.Ignore Null Inputs for Updates. The Designer activates this port property for lookup/output ports when you configure the Lookup transformation to use a dynamic cache. Select this property when you do not want the PowerCenter Server to update the column in the cache when the data in this column contains a null value. Ignore in Comparison. The Designer activates this port property for lookup/output ports not used in the lookup condition when you configure the Lookup transformation to use a dynamic cache. The PowerCenter Server compares the values in all lookup ports with the values in their associated input ports by default. Select this property if you want the PowerCenter Server to ignore the port when it compares values before updating a row.


==============================

can we lookup a table from a source qualifer transformation-unconnected lookup
nandam>no we cant lookup data premlahari>No. we can't do. I will explain you why. 1) Unless you assign the output of the source qualifier to another transformation or to target no way it will include the feild in the query.2) source qualifier don't have any variables feilds to utalize as expression

==========

nayana>How do i get data in a unconnected lookup transformationi.e if i want to compare customer_id in the source with the customer_id in the unconnected lookup, how can i get the data from the source for the customer_id if it is an unconnected lookupmanichamp123 Says>You need to create input port to take the input in_customer_id then from your source flow i.e. from the expression you can call the unconnected lookup :LKP.lkp_customer(customer_id) then in unconnected lookup condition you can put the condition like in_customer_id=customer_idnayana> i did the same, its advising invalid mapping

=========

Q >in which particular situation we use unconnected lookup transformation?Ans> We can use the unconnected lookup transformation when i need to return the only one port at that time i will use the unconnected lookup transformation instead of connected. We can also use the connected to return the one port but if u r taking unconnected lookup transformation it is not connected to the other transformation and it is not a part of data flow that why performance will increase.Ans2> The major advantage of unconnected lookup is its reusability. We can call an unconnected lookup multiple times in the mapping unlike connected lookup.=======
Q> Why we use lookup transformations?Ans>Lookup Transformations can access data from relational tables that are not sources in mapping. With Lookup transformation, we can accomplish the following tasks:Get a related value-Get the Employee Name from Employee table based on the Employee IDPerform Calculation.Update slowly changing dimension tables - We can use unconnected lookup transformation to determine whether the records already exist in the target or not.=======Q>How do we lookup data in the target and the source to compare them without using lookup transformation? basically i mean getting lookup functionality without using lookup transformation?Ans> You Can user a Joiner Transformation. Use the Target as a Source JOIN it with the Source 'Set the Joiner Condition and do the Required Tasks,Sidhu>Hey u can use a stored procedure to get the functionality of the look up.Pass the source rows as input to the SP,the SP will compare the input args to that of the target table , if value found can return some flag.Depending upon the outcome of the flag u can set ur conditionAns 3> You could have a SQL override in the Source Qualifier with something like: select * from source_table where not exisits in target_tableAns4>Hi,you can have source left outer joined to target table using the key columns to verify if an incoming row already exists in the target.e.g. consider A as source & B as target. And the key column is key_id in both tables, the join would be likeselect A.*, b.key_idfrom A, B whereA.key_id = B.key_id(+)now, in the mapping you can have logic to consider the row as new b.key_id is NULL and as existing if b.key_id is not NULL
==============