Lookup Component/Transformation in ODI 12c

A Lookup is Selector component that returns data from a lookup flow being given a value from a driving flow.

·         A lookup can be implemented in generated code either through a Left Outer Join or a nested Select statement.

·         Default join is left outer on driving table.

Pre-requisites:

1.    ODI 12c

2.    Oracle database

3.    KM: Default (IKM and LKM).

IKM: IKM Oracle Insert.GLOBAL

LKM: LKM Oracle to Oracle Pull (DB Link)

RKM: Default

4.    Components : LOOKUP

 

Source tables: Emp Table

Dept Table:

Procedure:

·         Establish connections physical and logical connection in topology for source and target tables.

·         Import the table in model in using Reverse engineering KM.

·        

·         Create mapping and rename as M_LOOKUP.

·         Drag the source table (Emp And Dept).

·         Drag the component  from component pallet.

·         Establish connection between lookup and source tables (Note: First connection is driving table and second connection is lookup table. Here Emp is driving table and Dept is department table).

·         Drag target tables (EMP_DEPT_LKP) from model and establish connection between target and Lookup component.

·        

·         Go to Lookup component properties and enter expression in Lookup Condition below.

·        

 

·         Leave it with default KMs(IKM Oracle Insert.GLOBAL) and LKM(LKM Oracle to Oracle Pull (DB Link)).

·         Save the mapping.

·         Run the mapping and see logs in operator.

Completed Successfully.

Output: