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
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.
Drag in the Source tables and Target table and map the columns accordingly.
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
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
Lets see the codes and as we see it has carried out the required UNION operation between two tables.
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
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.
This functionality give an extra edge for ODI on ETL side.
Keep looking odiexperts for more ODI 11g functionalities .
May 22, 2014 at 6:46 AM
I need 3 unions and als need to order the result. Is this possible?
January 23, 2014 at 5:54 AM
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
July 25, 2013 at 3:37 PM
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?
July 31, 2013 at 2:42 AM
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.
July 23, 2013 at 4:09 PM
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
July 24, 2013 at 1:00 AM
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.
March 9, 2011 at 4:36 PM
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
March 10, 2011 at 6:57 AM
Goto data set configuration and there for the data -source 1 operator make it N/A and close it and try again .
December 16, 2010 at 10:45 AM
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