Oracle Data Integrator (ODI) Review

It allows us to create scripts and share them across multiple data load processes.

What is most valuable?

Their code-once-use-everywhere approach for coding data flows. Unlike other ELT solutions, ODI allows for creating scripts ("modules") which are then shared across multiple data load processes. If you find a bug, you fix it once, and it is fixed for everyone. It is the same if you need to code a new feature. It is also worth mentioning that ODI supports over a dozen of scripting languages. Chances are that your favorite language is amongst them.

How has it helped my organization?

The main improvement was unifying and standardizing ELT processes. For example, each table in the EDW solution has a set of standard columns used solely for auditing, data lineage and debugging purposes. Because the code to support those columns is shared across the whole solution, we are now capable of getting the auditing information for every single piece of data in the entire solution, without having to code it individually for each stream.

What needs improvement?

The GUI is Java based, and is less than friendly. It feels a little bit like being in the late 1980s when I started using it. So I would say: hey, Oracle guys! Improve the GUI. Make it more intuitive. Snappier. Sleeker.

For how long have I used the solution?

I've used it for three years.

What was my experience with deployment of the solution?

Not really. There were some caveats when upgrading from one major release to another but that's expected with tens of thousands of highly customized ELT streams. Nothing that could not be resolved within a day or two.

How are customer service and technical support?

It is Oracle. So, you get what you pay for. We used their customer support once or twice (for instance, when resolving incompatibility with certain versions of the JDBC driver) and got all our issues fixed.

Which solution did I use previously and why did I switch?

I used DTS/SSIS, Cognos DM, and Informatica previously. They all have their little pros and cons. For ODI, the killer feature was the shared code. Plus, the whole EDW solution is built upon Oracle databases so it simply makes sense to use Oracle ELT tool.

How was the initial setup?

Well, it is certainly not the "Run the installer, click Next Next Next Next and have it up and running" kind of an install. There are many types of ODI agents that need to be installed and tweaked on uncountable number of servers so it does take a while to get the whole thing functional. It is all well documented though. Don't expect any major issues here but make sure you book enough time for the initial setup.

What about the implementation team?

We have implemented this with an in-house team. The main thing is to get one or two ODI gurus into your DEV team. ODI is built with unique philosophy in mind and if you try to start implementing your solution using your past (non-ODI) knowledge, you will get into trouble very quickly.

What was our ROI?

I have no idea. I was not involved in ROI related discussions. I can assume that because the tool is extremely stable and, once correctly implemented, runs practically without supervision, it is a good investment in a long run. But that's just my private guess.

What other advice do I have?

Make sure you understand differences between ETL and ELT (ODI is the latter). Have a well-structured source data. And if you don't know anything about ODI, find someone who does before diving into your data-warehouse project. You can learn SSIS or Informatica yourself in days. This approach is not going to work for ODI.

Which version of this solution are you currently using?

**Disclosure: I am a real user, and this review is based on my own experience and opinions.
More Oracle Data Integrator (ODI) reviews from users
Learn what your peers think about Oracle Data Integrator (ODI). Get advice and tips from experienced pros sharing their opinions. Updated: February 2021.
465,339 professionals have used our research since 2012.
Add a Comment

author avatarBrian Dandeneau
Top 5LeaderboardConsultant

What are your thoughts on ODI 12c? Did you think that the GUI improved? Do you think that its more in line with the other ETL tools out there?

author avatarit_user102120 (Business Intelligence Consultant at a tech services company with 51-200 employees)

I haven't had a chance to use it so far. All my recent projects were MS SQL

author avatarAlan Yves
Top 5LeaderboardConsultant

Which platform/ Technology are you using as a target ?

author avatarit_user746463 (User with 201-500 employees)

How to maintain the Dev Environment with multiple users.
How can the changes be tracked ?
What should be the deployment process ?

author avatarBrian Dandeneau
Top 5LeaderboardConsultant

All my response can be used for any version of ODI.
You can create users with multiple levels of access. Security can be wide open or very granular.
You can use versioning and even create reports from the repository to have a full audit.
You should have one master repository and multiple work repositories. You would have one work repository installed in Development mode and the other work repositories should be installed in Execution mode. Basically Development is the Development mode and Test, UAT, and Production should be in Execution mode. Development mode is self explanatory you can create jobs. Execution mode you cannot change the job at all, only execute the job. Please only deploy scenarios. Do not do multiple Development environments without reading all the documentation or taking a course.
Let me know if you want to talk more in depth.
Brian Dandeneau

author avatarit_user746463 (User with 201-500 employees)

Thanks @Brian Dandeneau.

Let me explain the cases as follows: [Note : We are using ODI 11G]

1. Track Changes : There is a module which requires multiple objects [packages,interfaces,user fumctions,variables,......] to be added/modified. Now after doing the changes; how does one user knows which objects have been changed ? ---Is there a way in ODI ?

2. Versioning Issues : Multiple Users are doing development on the same Development Environment. Now lets say at the end of day-1 ; User 1 has completed 100% of the task. User-2 has completed 75% and User-3 has completed 25%. Now User-1 ;as he has completed his task;wants to create a version and he can do that at mutiple levels i.e at project level or at each object level.
>>> If user-1 creates a version at project level -- All the changes done by user-1 would be included in single version but the partial changes done by user-2 and user-3 would also be included;which should not be the case.
>>> If user-1 creates a version at object level --then firstly; the user should remember which objects have been changed and even if the user remembers ;a seperate version would needed to be create for each object.

Can you please let know how the above issues would be taken care of ?

author avatarBrian Dandeneau
Top 5LeaderboardConsultant

1. There is a as of date in the properties. The second recommendation of using the space ODI provides to document when object have been updated. There are a few other ways to make sure that you track changes. You can even compare versions.
2. You are right. Project level versioning with the scenario described won’t work By themselves. There are places to document what was changed. There has to be a project lead assigned to each project. The project lead would be responsible for working with the other developers to ensure all changes are being captured.
The biggest mistake we see is not having team leads when there are multiple people developing. This is for any ETL/ELT tool. Making sure you have a solid design and being organizated helps as well. Pick the leader not just the most senior person.

Sounds like you just need some help getting your foundation setup.
Please reach out to my via Facebook
Or via LinkedIn

author avatarAlan Yves
Top 5LeaderboardConsultant

In response to "reviewer746463
Item#1 - For all main objects in ODI, such as PKG, Interface/Mappings, Procedures, variable, KM there is a "version" tab/ kind of property of the object. Once you navigate in there, you will be able to see the following: "Created By": username, "Created on": dd/mm/yyyy 00:00, as well as
"Updated By": username, "Updated on": date, besides of these, under "Definition" you can add notes about the creation of an object and changes to the description field or else under "Memo".
Item#2 - Regarding versioning, "if" agreed and if it is part of the development framework (if there is one), project/technical environment, regardless of the lead/senior developer it has been probably communicated to anyone working with ODI.
a- If versioning is a practice in the environment then all notes can go into the versions created.
b- Make sure you create a version of the object affected by versioning both or the object being changed.
For example, an Interface needs to be changed :
1- if there isn't already a version, create the initial version and save
2- Apply required changes and right after create a new version with comments related.
* If architecture has a single Master repository and you work repositories are spread out across the diff environments the versioning management and releases to other environments are easier but tougher control in topology is required(pain for developers)
* If ODI architecture has one Master per environment then developers have more freedom in Dev .
I don't see a huge impact in versioning and release automation(this is the recommended architecture).