Oracle Data Integrator (ODI) Reviews

The biggest feature for ODI is the fact that it uses the power of the source or target database to actually perform the ETL processing. So a typical data integration tool might have its own ETL engine, and that would be an additional server, additional costs, something else you have to maintain. ODI actually has the ability to create and generate code that runs potentially on your Oracle database, or even on a big data target, on Hadoop or somewhere like that. So, you have that flexibility, and you don't have to have that additional cost in maintenance.

A couple other additional benefits of Oracle Data Integrator would be the use of what's called a knowledge module. So this is like a code template that uses the metadata that you have available within ODI, and within your mapping, to generate that code, that then can be executed, like I said before, on the source or target system. The code will be generated in the native format of that database or that technology. So, again, you have sort of a hands-off.

These knowledge modules are built in. They can be developed and modified. So, the biggest thing, I always think, with ODI is, it fits around your data warehouse needs. Rather than taking your production, or your business, and trying to make it fit a tool, you do the opposite. You make the tool fit your business.

One thing that is included, and it's going to improve, is the integration with a configuration management tool. Right now, they integrate with Subversion. And we know in the future, there's going to be more tools implemented there for configuration management and some new features there. So right now, it's kind of one of those features that, it's been released, but it's sort of a beta version of that feature where a lot more functionality will be coming down the line.

So ODI is actually quite a stable product. As a testament to the product team that develops it, they're not going to release something that is extremely buggy or things like that. So, I do see that often they will release features that are highly anticipated and highly sought after. And they might release them a little early. So there's definitely patching that comes into play there, to kind of get the full solution there.

As far as stability, the Oracle Data Integrator has agents that run either in WebLogic, as a Java deployment there, or on a server as a Java agent, or a Java deployment. As long as a client has had the product implemented, with no issues there. So that's kind of the key there, you don't want to have those agents have a blip or any issues.

So as far as scalability of Oracle Data Integrator, from the high availability standpoint, if you use WebLogic for your agent, you can use the clustering capability within WebLogic to create multiple nodes on different WebLogic servers. And then run your agent through that process there. So if something fails, if the main agent fails, it can fail over to the secondary agent or, again, another agent in your cluster.

I'd probably put them up there around an eight, yeah. Being a system integrator of Oracle products, typically I'm on behalf of a client. So oftentimes it depends on who your client is and what their level of support is, as far as response and how things go. But I've always had great response with those guys.

A lot of the projects I've worked on recently are Oracle VM apps projects. So, it's the folks that are in the Informatica version of VI apps, and looking at moving to the latest and greatest, which has Oracle Data Integrator behind the scenes. I'm not saying they're actually talking about using Informatica, they're just, that's what they have. And they're looking to migrate. Quite often where we come into play, ODI is typically chosen at that point. And then they call us in to help with it.

It's not too difficult. It's tough to look at it from the perspective of someone who hasn't been doing it for a while. And oftentimes, that's what you need to kind of make that determination. But as far as just getting up and running, you can get it installed, configure an agent, quite quickly. And then the next step would really be getting ready to build a mapping. And they actually introduced in the most recent release, or maybe even when 12C came out, they introduced a feature that would basically get everything, topology, the models, all the data stores, everything, ready to go for you, just in one simple wizard. And then you could actually start building mappings quickly.

A lot of it is when they have home-grown ETL processes. So they'll manually script their ETL code. It's basically something that's difficult to maintain. So we talk about how we can use ODI to keep everything centralized. And even if we're not using ODI to actually build out mappings within the product, we might still be able to run that PL SQL or whatever it is, the script for ETL, from within ODI.

So we still have everything consolidated and contained and then the other approach, or reason for moving to ODI, would be the need to get off of that middle tier integration server. That we don't need with ODI.

As far as why consider Oracle Data Integrator, I think the big thing is the ability to consolidate all of your ETL into one place. Whether it's an Oracle to Oracle, source to target load, or even Oracle to Hadoop, or anything in between. SQL Server, MySQL, XML. ODI can basically talk to all of those different technologies. From a consolidation of all of your data warehouse or your data integration, if you will. And also, just use the same approach for mapping and creating processes for all of those different types of solutions. You have everything logically defined. And so when you're developing, it actually doesn't look any different than an Oracle source, and target doesn't look any different than a Hadoop source and target.

So I would rate ODI a nine. And that's really because, just looking across the other ETL tools, so I mentioned Informatica, there's a lot of other ones out there. I've seen what ODI does from a sort of a declarative design approach, and the push-down of work to the source and target. I've seen that replicated in other tools that have come out after Oracle Data Integrator. That's a big key.

It puts it kind of at the top, if you're going to look at a scale across all of the ETL tools. The other aspect to that rating is how they're integrating a lot of the big data technologies now. And it's a big deal from an Oracle standpoint, it's kind of how things are going. And it also just makes sense to, again, keep everything consolidated in one place. You already have an investment in ODI, it makes sense to try to drive some of your other big data type Hadoop loads or whatever it may be from that same place.

The ODI topology and designer are the most valuable features. The topology gives me total freedom in switching between technologies / ETL agents. The mapping designer gives me a good graphical overview of ETL mappings.

The speed in which we create/generate our ETL mappings has increased by at least a factor of four as compared to Oracle Warehouse Builder.

Overall performance needs improvement. Especially when running ODI clients on Windows desktops. ODI seems to generate a lot of network traffic.

ODI Studio can be very slow. Objects (mappings, packages, tables, etc.) can take minutes to open for editing when you are running ODI Studio on a Windows client when you have your repository database running on a server. It gets even worse when you are working on the same repository with multiple developers, which is not uncommon. :) The solution to this problem is running the ODI Studio in a virtual desktop on the same database server.

I have used it for over two years.

Migrating from 12.1.2 to 12.1.3 did not go as smooth as it should.

Sometimes the ODI agent shows running in WebLogic (middleware), while it cannot be reached by the ODI clients.

More than six or seven developers working in the same repository is the maximum.

Oracle support does not always seem to deliver. At one time during the migration of 12.1.2 to 12.1.3, we had issues taking over six months to resolve.

We previously used Oracle Warehouse Builder, which was discontinued by Oracle. Oracle Data Integrator is Oracle's strategic choice for ETL tooling going forward. We could not risk running out of support. That is why we had to buy licenses for Oracle Data Integrator (whereas Oracle Warehouse Builder came free with the Oracle database - until version 11.2).

The initial setup is really not straightforward. You really have to think about the architecture in which you want to use ODI (i.e., agent(s), repository setup, topology). But this makes the software as flexible as it can be.

As with all Oracle products, you need a licensing specialist to look at your needs.

We considered moving forward with Oracle Warehouse Builder, but dismissed OWB because it was running out of support.

We also considered Informatica PowerCenter, but it seemed to be even more expensive an option than ODI. With ODI, we could still use some of the present Oracle skills (mapping design), so the learning curve seemed less steep compared to Informatica.

• Think about the architecture in which you are going to use ODI very well.
• Think about where to place an agent and, if possible, put your ODI repository as close to this agent as possible.
• If possible, run your agent closer to the target than to the source.
• Try splitting up your repository into multiple work repositories for groups of developers of no more than five developers.

The most important aspect of the product is the ability to work with almost any data source or target. I can readily develop processes that use relational, file, XML, JMS messaging, web and big data sources or targets.

I can control the style of integration through "knowledge modules", and if they don’t do exactly what I want. I can write my own or customize the Oracle supplied ones.

The ability to execute third-party (or in-house developed) Java code by installing JAR files allows a great deal of flexibility; for example, I can add custom processors to do access web APIs that use token based authentication.

Another key feature is that we do not need to pass our data through some form of ETL engine hosted on a server; in some cases for example transforming data within a data warehouse all of the processing is done in single SQL queries thus reducing network traffic.

Finally, the rich SDK supplied with ODI allows developers to create virtually any form of development or deployment automation.

It allows a single skill set to handle virtually all of the data transport and transformation needs of a company. It moves the ETL processing to where the data resides and saves network traffic and the need for dedicated transformation engines - hardware that needs to be purchased, managed and supported.

There needs to better support of external version control software, currently only SVN is present, but I hear the GIT is planned in future patch.

From a DevOps point of view it would be useful to add better separation between ‘code’ and ‘executable’ exports, at the moment a piece of code will contain the embedded executable which bloats any source control objects (this may only be relevant to those the develop their own source and code control processes).

I've been using it for the last 10 years.

We've had no issues with deployment.

12cR2 is a new version, and there are a few issues with stability, but I expect most to be resolved in the first patch.

There have been no issues with the scalability.

I rarely engage with support.

As an independent consultant, I work with other ETL products, and switching is requirement of my employers. However, of the products I used recently (Informatica and Talend), I feel ODI gives me the most flexibility.

Set up of ODI Studio and the ODI repository is relatively simple, it is all done through a single JAR file executable. The complexity comes when you need to create ODI agents - there are three flavors of agent and the best choice of agent will depend on your agent management needs and infrastructure. The ODI agent executes ODI code and interacts with the host OS, typically one agent is sufficient, but more may be needed.

The licensing model has changed a few times over the years - read the Oracle price list or speak to sales.

Plan your ODI infrastructure, especially where data is transformed to ensure you get the best balance between license costs and performance. Get your developers trained in best practices so that avoid unnecessary pit falls.

The tool has a in-built "Knowledge Modules" using Oracle ADF, and this provides by default, all possible databases and technologies for data integration, such as file system, Oracle RDBMS, Teradata, and Microsoft SQL Server. This ensures that customers do not compromise their requirements and also falls within many companies budget and technical feasibility.

We had a customer whose core business was a logistics suite. ODI was used for their reporting purpose in some key BI reports such as "Container Load Plan (CLP) Reports", "Master Data Management Reports", "Business Partner Reports - Non Shipper Reports ", "Customer Consignee Details Reports". The customers requirements were delivered by using OTM (Oracle Transportation Management) as the Source OLTP System by us. We were also able to integrate ODI with their transactional system and therefore, we generated the reports through an ETL & data warehousing model. Due to this, the customer was able to get a deep-dive insight into their business performance across the south-east Asian logistics industry.

Although we're satisfied with its overall performance in terms of integrity, scalability, and usability, it could be improved by including a separate UI for viewing the session runs through the operator layer of ODI. They could also reduce the complexity of having to move between tabs in the same window.

We have had no issues with the deployment.

There have been no performance issues.

It's been able to scale for our needs.

ODI is integrated with Hyperion Planning, Essbase and other Oracle products. Data transfer is also easy since ODI already knows the metadata of these applications between the different databases. There is also ease of development in terms of the interface and customization with scripts, as well as data lineage and tracking, which are advantages to us.

We hadn’t upgraded in a long time, so all of our business users were creating their own solutions with spreadsheets, for example. We realized we were behind and purchased a whole stack of Oracle products that have simplified the way we move and track data. The accountants enjoy being able to reconcile where the data is and how it is changing. That’s the reason we bought the tool.

It would be nice to improve reconciliation. Additionally, better external authentication support through Microsoft Active Directory would be useful. Right now, it doesn’t support this very well. You have to buy Oracle’s special product instead.

When you start integrating products, it's not that stable. For example, if you upgrade other applications, like Hyperion Planning, it's going to break ODI.

I still need to learn a bit more about scalability in ODI, but I don't think you can scale it like DataStage.

Technical support for ODI has been awful, but I think it differs by product because support for OFSAA is wonderful. When I gave tech support a problem, the answer I got was “Oh. Well, keep testing.”

Initial setup for version 11.1.1.7 of ODI was a little difficult, but installing 12c was really easy.

I haven’t checkout out other solutions, so I don’t know where it scales, but from a developer’s standpoint, I’d give the product a seven or an eight. On infrastructure, I’d bring it down to a five.

The main features we use and has helped us to implement our ETL process are

• Data transformation
• In memory engine
• KM customization
• User friendly GUI
• Versioning
• Big Data support
• Security support
• Easy deployment
• Reusable Mapping
• Multiple Target Load
• ELT Architecture
• Application Server support for other vendor servers such as IBM Websphere

The features that we have applied to our project has helped us reduce manual effort and increased our scalability and growth. I worked on multiple domains where requirements were adversely different. We used many ODI features to enhanced our data transformation capability.

I am personally satisfied with current version, however, I think they should be able to provide a feature where we can easily use a configuration tool for TFS, GIT etc. This is essential for code management.

I've been using it for six years.

We have had no issues with the deployment.

There have been no performance issues.

It's been able to scale for our needs.

9/10

8/10

I have not used any other product. However, I have seen other ETL tools in use such as BODS, SOA, and Informatica and I did not like them as I did not that they were user friendly.

It was straightforward. I used an application server for the initial setup and I did not find any challenges.

We performed an in-house one. If you have a technical team for ODI, then they can easily perform any implementation. If not, then you should have the vendor perform your implementation.

10/10

It is not as costly as other ETL tools.

You should use Oracle Data Integrator for your data transformation, or at the very least give it a try.

I'm not very comfortable with ODI, but it is one of the Oracle product that we use. I know the interfaces and that it's used for financials. The ODI client can be configured so that Excel data can automatically be pumped into Oracle Forms directly.

It requires a client, which I don't think is the best way to go about connecting data. Basically, you need to establish a connectivity between the client and the database, so when you launch something through Reports, in can be shown in the Excel.

Also, I think they should have something like Google Docs, where an engine opens up a document directly instead of having to depend on a client for connectivity. That would make working with it much more efficient and better.

If you are applying it on more clients, it becomes a thick client because to have the client installed, it needs memory from the desktops. This slows down deployment.

If it has a client that demands more thick clients than thin clients, that means that you need to give more memory and more CPU for your desktops that are also using those same resources.

We haven't had problems with scaling it.

I wasn't involved in the implementation, but I haven't heard that it was particularly complex.

I believe we implemented it with our in-house team.

It is a clever integration tool to extract data from every possible source, and it can transform the source data to a format the targets can handle. It is suitable for data warehouses and data migrations.

We replaced many manual actions and tasks with mappings in Oracle Data Integrator, and created schedules, and then used SOA Suite services to automatically transfer large data amounts between different systems.

The integration between the Oracle Repository and versioning tools like TortoiseSVN.

We’ve used 11G for four years, and 12.1.3 for two years.

One of the benefits of it is the usage of the principle ‘write once, execute anywhere’. You set up the architecture in the topology within Oracle Data Integrator.

We've experienced no issues with performance.

We haven't needed to scale it yet.

We haven't needed to use it yet.

Previously we used Oracle Warehouse builder, and custom made PL/SQL data warehouses. We’ve chosen Oracle Data integrator over other solutions because it is the successor of Oracle Warehouse builder, and Oracle Data Integrator provides a more structured working-method, and the data warehouse is easier to maintain.

The installation is rather straightforward, with the available documentation it must be possible to do the installation by yourself. You must have sufficient knowledge of Oracle Data Integrator to make the correct decisions during the creation of the architectural setup.

We used an in-house team but hired interim experts, and created a team with combined knowledge levels, from rooky to expert. The initial setup is rather complex, and needs an experienced architect to make the correct decisions. The change of success is highly depending on the decisions that are made during the setup.

I have no info on the ROI or the licensing, but I know the license costs are high, so Oracle Data Integrator is only suitable for the larger companies.

To benefit the most of Oracle Data Integrator it is important to assemble a team with the experience to make the project a success.