User:CIS628/sandbox

Oracle Data Integrator (ODI)

Introduction

Oracle Data Integrator (ODI) is an Oracle ETL (Extract –Transform-Load) tool popular in building enterprise data warehouse projects. Initially, Oracle had its own tool called “Oracle Warehouse Builder” which later was merged with the Suonopsis tool after the acquisition of Sunopsis in October 2006. In January of 2010, Oracle introduced the product as “Oracle Data Integrator Enterprise Edition’ in the ETL tool market. In principal, ODI tool is a comprehensive data integration platform that covers various heterogeneous data integration requirements; from high volume, high performance batch loads, to event-driven, trickle feed integration processes, to SOA-enabled data services. With its superior performance and flexible architecture, ODI can be used in various types of projects such as Data Warehousing, SOA, Business Intelligence or Application Integration. The key aspect of ODI is related to its architecture in which it performs the ETL process. Unlike, other tools available in the market ODI performs ELT i.e., Extract Load and Transform (E-LT) technology that improves performance and reduces data integration costs—even across heterogeneous systems.

Released Versions

The following are the different versions of ODI released in the market

• ODI 10g
• ODI 11g
• ODI 12c

ODI Architecture

The Oracle Data Integrator architecture is organized around a modular repository, which is accessed in client-server mode by components such as ODI Studio and execution Agents that are written entirely in Java. The architecture also incorporates web-based components such as the management pack for ODI which integrates with the Oracle Enterprise Manager Cloud Control infrastructure of the ODI Console which enables users to access metadata information through a Web interface.

ODI Studio

ODI Studio provides four graphical Navigators for managing ODI artifacts: Designer, Operator, Topology and Security. ODI Studio offers an easy to use graphical user interface and can be installed on various platforms such as Microsoft Windows or Linux.

Designer: All project development takes place in this module; that is where database and application metadata are imported and defined. It uses metadata and rules to generate integration scenarios or load plans. It is the core module for developers and administrators.

Operator: It manages and monitors data integration processes. It consists of execution logs with error counts, number of rows processed, actual code executed, execution statistics etc. It can be also used for troubleshooting purposes.

Topology: It defines the physical and logical architecture of the infrastructure. The infrastructure or projects register servers, database schemas and catalogs, and agents in the master repository through this module.

Security: It manages user profiles, roles and their privileges. Security can also assign access authorization to objects and features. Security administrators generally use this module.

Runtime Components: The Agents

At runtime, the Agent coordinates the execution of the ODI sessions. It retrieves the code stored in the ODI repository, connects to the various Source and target systems and executes the overall data integration process. Note: It is important to understand that although the Agent can act as a transformation engine, it is rarely used for that purpose. It simply retrieves code from the ODI repository and then request database servers, operating systems, or scripting engines to execute that code.

Repositories

The repository is consists of one or more Master repositories and generally several work repositories. These repositories are sets of tables stored in relational database management systems such as Oracle, MS SQL Server, IBM DB2, and MySQL. All objects that the ODI modules configure, develop, or use are stored in one of these repositories, and are accessed in client-server mode by the various components of the architecture.

ETL Market and Developments

With the rise of Business Intelligence, corporations are moving towards the adoption of Data Warehouses to conduct BI analytics and maintain the competitive advantage. As any other markets, ETL market is also full of several tools and technologies provided by various vendors. Some of the popular tools in the ETL market are;

• Oracle warehouse Builder
• SAP Business Objects Data Services
• SAS Data Integration Studio
• IBM Infosphere Information Server
• Power Center Informatica

Based on the Independent research and analysis performed by ETLtool group, ODI has been given 5 stars. A copy of their ETL tools comparisons can be found at the following link; http://www.etltool.com/etl-tools-comparison/