The Knowledge Module (KM) is my favorite feature of ODI. This is where I learned how to use variables to make jobs dynamic. I took that knowledge and created a KM that would go into iTunes and pull the sales of eBooks. Making something that is reusable, like a KM, is important to not only reduce build time but also maintenance in the future.
Improvements to My Organization
I have used ODI to improve run-times of many corporations' overall integration run-times. Corporations on a daily basis run integration jobs which normally take five hours or more. I have seen these jobs become hourly jobs because of the time reduction they received with ODI's involvement.
Room for Improvement
Error handling can always be improved with ODI. A lot of the errors are generic, but I will say that with a little experience, you can decipher the errors to help you fix them. In fact, I find myself not using the debugger that came out with 12c, just because I have learned to read "ODI-login-eze."
If there was a way to keep the basic user from creating a monster SQL that kills a system on execution, that would be great as well.
Use of Solution
I have used it for 10 years. It's hard to believe it has been that long, but time flies when you have fun -- and I actually have fun when developing integration solutions. I started out on 10g and was able to quickly pick up on the ELT model after working with ETL for years before that. I was one of the first to install 11g on Red Hat. The main reason for the upgrade to 11g was purely looks as there were very little actual enhancements beside a couple of tools.12c was a major overhaul. I love working with 12c as it's now a flow-based tool but still ELT. It brings me back to the days of Hyperion Application Link (HAL), except that 12c isn't slow.
It is seamless. You would have to really try to mess up the deployment.
Stability is good, better than 10g and 11g.
Scalability-wise, 12c is the best in it's class. I could hand 12c to any size client and they would be fine developing and maintaining it.
Customer Service and Technical Support
I think that you get more than the regular Oracle support when you are working with ODI. Even the "First Responders" have a very wide knowledge on the product. This is a pleasant change from some of the other products for which you get the person who asks if your computer is plugged in.
I work on all the other integration products as well as ODI. In fact, I had to compare all the integration products (ODI, SSIS, HAL, Informatica, DataStage) when we were trying to decide on the strategic direction for the major bank that I was working for. HAL was being sunset, so easy decision there. DataStage cost a lot to host and was hard to develop in. Informatica was not installed anywhere in the corporation so the knowledge base for us wasn't there, so it got the boot too. It really came down to SSIS and ODI. We had a lot of SSIS knowledge and I was the only ODI developer. I took someone who never seen SSIS and ODI before, but had basic database knowledge, sat them in front of a computer, and gave them a day course on both. To be fair, after each course they had them create a job to do the same load. Results were clear and ODI won hands-down. ODI won out because of price, support, and speed/ease of development.
In 12c, they have made the setup so much more simple then what it used to be. The interface to do the setup walks you through every setup step.
I have been on both sides of the fence for this question. I would always have a vender do the install if you have never used the product before. There are a lot of little tweaks that can be made that takes experience with the tool to know these tweaks. If you have had the product for over a year, I would say, that in-house would be ok. Just make sure that if you have to remediate the install that you involve Oracle in that process so you make sure that all the parts get cleaned up properly otherwise the reinstall could be problematic.
Pricing, Setup Cost and Licensing
I would say that you need to pay attention to the licensing to make sure that you are not paying to much. Normally, the licensing can be your friend if you don't need ODI for anything complex. You can switch ODI to go back to ETL if you don't want to spend that much money. Thin about it this way, if you are charged for only where it translates the data, then put a 4-core Red Hat in the middle of everything. Force your jobs to translate only on the Red Hat server. Your 1 million dollar implementation just went to 200k -- you're welcome. The caveat with the ETL setup is that the processing is slower per job -- you're not welcome. Again, ask yourself, what do I really need this for?
Know what you are getting into.
If you are going to use a firm to build out a solution, ask for a Proof of Concept and ask them to show you how flexible it can be. If they can't quickly come up with something, be wary. Don't just go with someone that is cheap, you get what you pay for.
This snapshot is to turn on automapping. This is a very useful function to have on when developing. This will make the magic happen when you connect a source and a target together. This is not in the documentation, so good luck finding how to turn it on if you haven't used it before.
Disclosure: My company has a business relationship with this vendor other than being a customer: We're implementation partners.