Oracle ODI

The Summary
===============
ODI….hmmmmmm……………..‘nuff said ….

The Detail
========
ODI is really good at

- Gluing the batch together – better than, say, Unix shell scripts
- Scheduling the batch
- Reporting on how the batch did
- Getting data from non-Oracle sources – although even here I would use other ‘Oracle’ methods were possible – e.g. heterogeneous services

Its not so good at
- Moving data from one Oracle source to another e.g. ‘live’ Oracle database to Oracle warehouse
- Checking that the results are correct
- Rerunning processes which different parameters (eg load the data from 20 days ago for this interface)
- The generation of scenarios (compiled code) means it is too easy to loose track of whether to designer version of the interface is the same as the as the scenario(compiled code) that is currently running in live.

Justification
=========

Hang on, moving data around is the one of the major functions of ODI. True.. but it is too simplistic. It will only do simple variations on ‘Get data from source table into staging table and from there to target table’. If you need to do anything more complex (for say performance reasons) you need to resort to either Jython or pl/sql . A good example is that a common method of adding data to large table is to create a table and then swap it with on of the target tables partitions.

Why would you want to learn yet another obscure language (Jython) and if you are going to write PL/SQL, it should be in the form of database packages and (at a pinch) database procedures anyway.

The other problem is it does not do much in the way of checking that the number of rows in landing table is the same as the number of rows in the target/fact table. It is very easy to ‘loose’ rows when doing joins to dimension tables and ODI will not tell you. This is not necessarily a problem if the developer is aware of such issues (he can simply tack a pl/sql check on the end of the scenario) but the ‘clicky, pointy’ nature of the development interface is not conducive to thinking too hard about what you are doing.

Lack of parameters – again this may be because I don’t know the tool well enough but it seems really difficult to run a process with different paras (as you would a pl/sql routine).
e.g. re- load the data for 20 days ago
A developer here always copies the interface changes the variables and then runs the copy – dooh!


Documentation
==============

It is argued that ODI is ‘self documenting’, a phrase that should always be viewed with suspicion. The arguement goes that Oracle is going to integrate (in future version) ODI and OBI so that it will be possible to trace back from a report the original data on the live system through the ODI/OBI system. I will leave you to form an opinion on that statement.
Lets just say (and this may be ignorance on my part) I find it really difficult to work out what interface loads data into which data warehouse tables at the moment.

How’s that , sorry it is not more positive , perhaps I should add that the bits I have mentioned that it is good at, it is really good at, but does that justify the cost ?........dunno