ETL - Tips on Transformations - Lookup procedure

Transformations can be active or passive. if the number of rows is changed that pass through one transformation we call it an active transaction, such as Filter transformation; otherwise, if the number of rows is not changed, we call passive transformation, such as Expression transformation.
Farther more, one transformation can be unconnected or connected to the data flow, an unconnected one can be called within another transformation(a value will be retured).

Here I will give a explaination to the difference between unconnected Lookup procedure and connected Lookup procedure.

Like we said as above, for the unconnected Lookup procedure, it is separate from the data flow, and for the connected Lookup procedure, it is joined into the mapping pipeline, and has the input and then output ports.
1) unconnected Lookup procedure.





From the above screenshot, there is one unconnected Lookup procedure named LKP_W_EXCH_RATE_G, it is called by one procedure named EXP_PLP_AP_INCR_ACTIVITY_LOAD in pipeline, one expression is wroted for one port as following in EXP_PLP_AP_INCR_ACTIVITY_LOAD ,




IIF(LOC_CURR_CODE=CURRENCY_CODE,VAR_ONE_AMT,:LKP.LKP_W_EXCH_RATE_G(DATASOURCE_NUM_ID,'M',LOC_CURR_CODE,CURRENCY_CODE,POSTED_ON_DT))

for the above :LKP expression, there are 5 parameters, which are the input of the unconnected Lookup procedure. and one the lookup result will be used by the expression.

Note:

a) If you call a connected Lookup transformation in a :LKP expression, the Designer marks the mapping invalid;

b) Best practice: adding index to every columns of the lookup table in database will be very helpfull for the performance if the lookup table is a large one.

c) If the lookup table is on the same database as the source table in the mapping and caching is not feasible, join the tables in the source database rather than using a Lookup transformation.

2) Connected Lookup procedure