- Direct infusion of statements to the database, which allows the full use of database capability
- Ease of creating data flow and transformation
- Flexibility on technology on transformation
My customers started to increase their ETL performance with great margins. This was especially the case when ODI was used with Exadata, which allows each statement to be executed much faster.
The main problem with ODI is that you have to create each step of the transformation as a new interface. A big data flow can have more than 100 interfaces, which can make it difficult to trace the data. However, in v12, this approach changed to become more flexible.
Another complaint is with the user interface performance -- as the work repository gets bigger, issues arise.
I've used it for seven years now.
We have had no issues with the deployment.
There have been no stability issues.
It has been able to scale for all our needs.
I have a lot of experience on Oracle environments. I've started to have projects with my client using Oracle solutions. In my experience, ODI is a tool that is 100% compatible with all Oracle environments.
I can say that the level of complexity of the initial setup is medium. It is not straightforward because each customer may need specific logic on ETL approaches, so this means that your Knowledge Modules are mostly customized. Although except after the first installation and environment definitions, it is pretty easy to do after that.
I am a part of a vendor team. I think the most important thing is to decide where to reside this tool and its agent. This architectural question will help in the future to execute ETL processes. Secondly, the naming standards of all projects, immediate tables, Knowledge Module and folder names etc. are pretty important. Thirdly, versioning is crucial. All these standards should be done at the beginning of your use.
The product has a wide range of options on licensing.
The Knowledge Module is the heart of this solution. If you have nicely written Knowledge Modules, then you will not have any performance issues. The language should be kept as common as possible and all in one solution. You can write in many different languages, but the maintenance will need a wide variety of knowledge in the future. This can be very tricky in the long term. Do not forget to clean temporary tables after each execution of ETL. Otherwise, the database will be full of unnecessary data.