what is Oracle data integrator?

Question

What is ODI? what is the actual use of ODI? how oracle data integrator tool, helps with programming? what actually is the difference between an sql developer tool and an ODI tool? really fed up with the definitions found on some sites, it will be great if any one comes up with gud explanation..

Answer 1

I totally agree Oracle site is confusing and these product descriptions are not for techies and written by marketing department.

Let me try to explain in simple words. ODI is an ETL tool - to be precise EL-T tool. You can read more about ETL vs EL-T on wiki.

Oracle used to sell OWB (Oracle Warehouse Builder) before and then they bought ODI from Sunopsis. OWB will no longer be supported and ODI will be the tool used by many Oracle applications.

You can do a lot of things with ODI but you would use it mostly to extract data from a source (files, databases), transform data in a staging area and load to your target database. ETL projects are normally take a lot of time and efforts and while you can do similar things by writing a custom code, ETL tools serve to help make these hards tasks easy for developers. ETL tools are used for data integration projects, data warehousing and so on.

If you ever heard about Microsoft SSIS tool shipped with MS SQL Server - that should give you an idea what ODI is for.

This is a good white paper that explains how to use ODI and what the benefits are versus the custom coding.

Oracle also came up with a version of ODI called ODI Data Services - a marketing trick to expand ODI to SOA market.

They also sell Golden Gate product which is intended for real-time ETL and data federation projects that can work well with other applications and web services. Data federation is a process to merge/integrate data in realtime without doing any ETL.

Hope this helps!

Answer 2

ODI is an ETL tool. It has two main advantages over competing products:

1) It uses an ELT type approach, i.e. all of the transformations happen on the target datawarehouse server in SQL. This means that ODI is not a black box and developers know exactly what is going on. This is great for debugging. It also means that ODI can leverage the horse power of the target warehouse server and does not require any additional application servers like traditional ETL tools, Informatica etc. It lowers the TCO.

2) The main advantage of ODI over competitive products is the code template approach. This guarantees extreme reusability of your and your developer's ETL code. Data transformation logic and strategies are encapsulated in so called Knowledge Modules and can be reused across your data warehouse. This may cut down development time by 30-50%.

Links to ODI tutorials can be found on the BI Quotient blog