Oracle Data Integrator class names to navigate ODI repository schema

Dt: 7 June 2015

Courtesy: my ODI team 

SQL override in ODI for OBIA

Dt: 25 April 2015


Recently  I interviewed for OBIA at one of the reputed firms of the world..
Interestingly this firm has rejected me 4 times now.But I keep going back to prove a point to myself.

The fact that ...everytime they have rejected me, the interview panel was so dumb and asked the most stupid questions... is what irritates me a lot.So I hop in every time they conduct drives to check out what stupid questions they have got this time.


I guess thats enough of complaining  .Lets get back to completing the post.

So the question that this beautiful lady on the panel asked me was something related to ODI.

Can we write custom SQL override queries in ODI like we can do in Informatica source qualifiers ..she asked.
When I said " yes we can"  very confidently she looked almost surprised ,,Her next response being "Are you sure".I told her  "I do not remember the KM property right now but yes we have done this before".

Obviously she must have felt that I have bluffed and must have considered that to be one of the most convincing reasons to reject my candidature for the job.

So here is the proof for all those enlightened OBIA professionals who are new to OBIA with ODI.

You can indeed write query overrides in ODI. However this feature comes along with the OBIA LKMs only.

Note: There is one out of the box KM which ships with ODI which lets you issue SQL against the DB.The name of the LKM is Source as SQL.


Let me know if anyone out there has got more to add to this


Also all the ranting at the start of the post is only in good humor :-) & not meant  to hurt anyone

Renumbering Work Repositories in ODI before migration

Dt:22/3/2015

Renumbering the work repositories is important if you want to migrate the code from one environment to another,

Normally if you install ODI at two locations say DEV & QA it would install with the same default internal IDs that is 500.


For every object within ODI we have an associated internal ID .The last three digits of the ID represent the work repository where the code was developed.

Now consider a scenario where you are creating objects in both DEV & QA.It might be possible that the internal IDs of the objects might clash .

For ex, A new model created in DEV may have the internal ID as 23500 & a new mapping created in QA may have the same internal ID(23500) ,This would create a huge confusion when  you  try to migrate the object from DEV to QA causing the migration to fail.You therefore need to ensure that the work repository IDs in all our environments are unique.



Right lick on your QA repository and choose the option renumber.

DEV is at 500.Lets go to 600 for QA

The operation completed within seconds.Not sure if it really did the job



Let me check.
Custom Folder changed to 4600 from 4500

Another thing that I observed is that the prebuilt code is not stamped with the internal Id .Its just the code that  is custom.(What I now know is that the prebuilt code carries different IDs of repositories where they were initially created)
This makes sense as the documentation says that last 3 digits represent the repository where the code was built.Which means that there should not be problems with migration.



Let me test migrating a folder which I could not yesterday.
It now goes through.
In this method of migration we need to move the models first and then move the mappings so that the references remain intact.(Avoid using Smart mode)


NOTE:All the methods mentioned above have been tested with ODI 11g .I am not sure if they remain true for the older versions.
.