Oracle Data Integrator overview

In this article I talk a little bit about the history of oracle data integrator (ODI) and some of the core differences compared to other tools in the same segment.

ODI History:

ODI used to be a product of a company called Sunopsis which also named the tool itself. Sunopsis headquarters was based out in Lyon-France and had operations in Massachusetts- USA. Since data integration has become extremely critical and important to corporations and has become more and more important with a rapidly increase of data generation by companies and virtually anything and anybody can generate data like never before.

Having said that, enterprises understood the need of exploring better these information and Oracle as a big player and pioneer in the segment quickly acted and since then has been investing very heavily and developing its portfolio of solutions in not only database space but also in the surrounding applications which is most known as middleware.

So in the last decade with the fusion middleware strategy, Oracle has acquired and improved a significant amount of applications.

The idea is to have those applications complete, pre-integrated and hot-pluggable; some of those applications that initially ran on a stand-alone mode were later integrated into bundles becoming the middleware suites, compatibility a integration have been constantly improved and the 12C version have improved dramatically.

As part of the fusion wave, one of the Oracle’s acquisitions was “Sunopsis” back in October 2006; such acquisition is considered to be a very strategic move towards the oracle data integration space since until then the official oracle ETL tool was OWB which was good but with a few issues and limitations making it not so flexible. So sunopsis was the preferred tool due to its unique advantages compared to other tools in the market, also a couple years later Oracle acquired golden gate which is another data integration tool focused in the real time data replication, both tools can be used together.

ODI advantages

Oracle data integrator is an ELT/ETL tool that has the same objective as other ETL tools which is “data integration”. A simple definition of an ETL process is to extract/pull data from “data sources” and load to “data targets.

The traditional ETL process stands for ”Extract”, “Transform” and “Load”

The difference of ODI compared to other ETL tools is its concept being “ELT” which is basically “Extract, load and transform”, this is a modern concept since the environment becomes much simpler, you will have only the source and target “data servers” giving you the choice and flexibility to choose which server will do the “T”(transformation)work which is the hard work where you transform, cleanse, merge, integrate the data….

That approach speaks for itself, it is clear that you avoid extra costs by not having a third server/machine, the costs is not only the server but all the ecosystem around it ranging from infrastructure, software installation, maintenance, professional support etc…

The figures below illustrates a typical ETL and ELT (ODI setup) architecture:

The ODI ELT architecture:

In which Scenarios can I use it?

ODI is really flexible for almost any type of data integration, it is typically used in data warehouse environments supporting companies to eliminate silos of data by creating a highly integrated data repository (DW) which becomes a single source of the truth for business user’s access, it can also be used for data migrations between transactional systems and real time data replication.

Below is a few of many advantages:

1- advantage: it takes the ELT approach which means you do not need a third ETL server between your source and target while other  ETL tools requires that you first extract the data, transform in a separated server and then load to the final target database sitting on a different server .

2- advantage: platform Independence, Oracle Data Integrator supports all platforms, hardware and OSs with the same software.

3- Data Connectivity: Oracle Data Integrator supports all RDBMSs including all leading Data Warehousing platforms such as Oracle, Exadata, Teradata, IBM DB2, Netezza, Sybase IQ, MSSserver and numerous other technologies such as flat files, ERPs, LDAP, XML besides of its highly integrated support to SOA framework.

4- ODI supports enterprises in several types of data integration, for example : it can integrate data between  legacy systems, from transactional systems to DW, it supports change data capture as well as Business to Business data sharing (webservices, ftp etc..), you can use ODI to pull data from a cloud based database, for example MSSql server sitting on Microsoft Azure, you can load that data to an Oracle Database or DB2 on premises, this is based on personal experienced implemented on a client.

Below in the ODI topology you can see a list of technologies supported, with built-in drivers/jdbc .

Here you can read more about the product’s architecture, history and what the market says about it :

Thanks for the reading !

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s