ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

11g Oracle Data Integrator – Part 6/11g – Union , Minus, Intersect

Hello Friends ,

We have shows you few important features of the new ODI 11g ,today we are going to show you how to implement Union , Minus etc logic in ODI 11g which was lacking in older version and thus maturing ODI in the ETL world.

After the initial step of the Interface name ,Click on the Add/Remove Dataset

clip_image001

Rename the Dataset Name to Source 1, Source 2 for more meaningful or else you can provide the Table name too and in the Second Dataset name select the Operator as Union.

clip_image002

Drag in the Source tables and Target table and map the columns accordingly.

clip_image004

Go the Flow tab and select the Staging Area and select the appropriate LKM and IKM.

[ since my Source are in the Same database so no LKM ) and Execute

clip_image005

ODI 11g provide a new feature called Simulation as that generates all the required codes without running the Interface , useful functionality when you trying carry some complete logic where you want to test in SQL Developer or Toad because carrying out in ODI .

It worked and we got 107 inserts

clip_image007

Lets see the codes and as we see it has carried out the required UNION operation between two tables.

clip_image008

Let’s try the same interface for Minus with truncate table option yes in Target.

To do so, we have just changed the operator for Source 2 to Minus and execute

clip_image009

clip_image010

Similarly it goes for UNION_ALL and INTERSECT

What if we want to do UNION between three tables? Just select the right operator and the ODI will create the logic as defined.

clip_image011

clip_image012

This functionality give an extra edge for ODI on ETL side.

Keep looking odiexperts for more ODI 11g  functionalities .

9 Comments

Leave a Reply

Required fields are marked *.


This site uses Akismet to reduce spam. Learn how your comment data is processed.