Thursday, September 23, 2021

ODI - How to use lookup component


  • A lookup is a selector component that returns data from a lookup flow being given a value from a driving flow.The attributes of both flows are combined, similarly to a join component. 
  • A lookup can be implemented in genereted SQL either through a Left outer join or a nested select statement.
  • A lookup condition can be created by dragging an atrribute from the driving flow and then the lookup flow onto the lookup component. The lookup condition is an equi-join between the two attributes.
  • The lookup component gives us some additional functionality compared to a join. We can supply a default value for a key that does not exist in our lookup table. Using join, we might have to do an outer join followed by an expression operator providing a CASE statement to replace the NULL from the unmatched key.
  • Using lookup, we can define match row rules and no match rows too.

Implementation Steps:

Step1: In Topology, create physical and logical architecture and corresponding schemas and context.

Step2: In Designer tab, Create models and revers engineer to get the required data store tables.

Step3: Create a project and create a map.

Step4: Drag and drop the departments and employees tables and connect with the lookup.

Step5: provide the lookup condition. 

Step6: drag and drop the target table EMP_Depart and connect with the lookup.

Step7: go to the connectors point and see input and ouput connectors.

Driver table: Departments
LOOKup table: Employees

Step8 : select sk field and edit expression and use nextval sequence.

Step9: uncheck update.

Step10: add the optimization context.

Step11: Add LKM=LKM SQL to SQL for lookup

Step12: add IKM for target table and make flow control as false.

Step13: save. Run and check operator tab and target db table .

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...