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

  1. I need 3 unions and als need to order the result. Is this possible?

  2. Hello
    I must use ODI v11 IKM on a project that was completed in v10. during the execution of an interface that uses “IKM slowly changing dimension” on Operator everything is green but there is no data in the target table

    I know that a relationship with DataSets (INSERT option in KM, I found the methods and DataSetMax DataSetMin), but I do not know how to solve. if you have a solution or a track I’m interested. thank you

  3. Kdevendr, thanks for your reply. I tried the first option , After Duplication, it doesn’t create mappings [from first data store] and I still have to manually start doing the mappings [transformations] . so that’s not helpful. I don’t think second option will help me with this either. If I understood the second option correctly, all this package going to do is send the resource name during run time to interfaces, but it still looks for mapping [which should have been done in interface already]. So, any other thoughts?

    • In relation to second suggestion , assuming the filenames are different but the structure is different , in such a situation it will work and yes you will need to create atleast one single Interface mapping.

  4. I was searching for seting up Data sets and found this link. Very helpful. I got a quick question though. if I am adding a Data set 2, is it possible to duplicate it as Data set1 since I have flat files with same format but in different files and since I already created the Target mapping [a lot of mapping] I would like to jsut duplicate it and change the source file name instead of going thorugh mapping all over again for three more flat files.

    Thanks for your help

    • Kenny,

      solution 1
      Duplicate you file datastore and change the file name and drag and drop the other source data store in other dataset.

      Solution 2.
      In the file ODI datastore replace the RES_NAME of file with ODI Variable
      now in the package Variable in Set Mode (file Name1) -> Interface -> Same variable in Set mode(file name2) -> Interface and so on.

  5. I’ve created union with two tables. During development I was messing with DatasetConfiguration window by changing order – first became second and second become first. By the way, very strange that there is no ‘SAVE’ button- only ‘Close’.

    After execution of interface got error on integration step.

    Code looks like :

    insert into TARGET (field1,…fieldn)
    select field1,…fieldn
    FROM (

    UNION
    select fields from source1
    UNION
    select fields from source 2

    ) ODI_GET_FROM

    Looked at the FLOW tab and can see difference between your clip_image0051.png that both sourses has UNION in it.

    Source1(UNION)-10
    SOURCE2(UNION)-20.

    Any idea how to remove UNION from first one?

    Thanks

  6. I tried to use UNION in ODI .
    I created an interface with all the steps you said.
    I used LKM also since i have a table which is coming from different oracle database from target database.
    I have to join two different tables.
    In source1 , i joined a table from two different databases with a outer join
    similarly in source2, i joined two tables from differet databases with a outer join
    When i am trying to execute the interface, ODI is unable to start execution, it is thrwoing an error saying ‘Missing PopMapping instance for PopCol.

    Can you please help me with this

Leave a Reply

Required fields are marked *.