by Varun Reddy

E-L-T vs. E-T-L

Oracle Data Integrator, or ODI, provides a new declarative design approach to defining data transformation and integration processes based on a unique Extract-Load-Transform (ELT) architecture.  The declarative design approach separates the declarative rules from the implementation details. ODI architecture eliminates the need for a standalone ETL (Extract-Transform-Load) server and proprietary engine while leveraging the inherent power of RDBMS engines.

Why Oracle Data Integrator?

Some of the key features that would help in making a decision if Oracle Data Integrator satisfies the data integration needs are:

• ODI reduces the time spent in learning the product as well and helps in increasing developer productivity
• ODI Eliminates the need for a standalone ETL server and proprietary engine.  It leverages the power of a relational database like Oracle for processing. Through this, ODI leverages set-based operations and as a result provides much higher performance.
• ODI also helps in providing a certain level of data quality without using an external data quality engine. This is achieved by using the data integrity rules and constraints that are defined on the target data warehouse and/or in the ODI tool models.
• ODI also supports heterogeneous databases other than Oracle such as Teradata, DB2, etc. It also supports other technologies like web services, flat files, XML, etc.
• ODI supports all platforms, hardware and operating systems with the same software
• ODI is cost-effective, as it reduces the initial hardware setup cost and does not require an ETL server and ETL engine. Also, the reduced learning time and increased developer productivity, decreases the labor costs of the project.

How does ODI work and what does it look like?

 Oracle Data Integrator Architecture Diagram

Repositories

Repositories are the central component of Oracle Data Integrator architecture. Objects developed or configured through the user interfaces are stored in the repositories. There are two types of repositories in ODI:  1.) Master Repository and 2.) Work Repository. There can be only one Master Repository and several Work Repositories configured in an ODI platform.

The Master Repository stores the following metadata:

• ODI Platform Security Information - Users, Profiles and Rights.
• Topology Information - Technologies (Database Platforms), Server Definitions, Contexts, Languages.
• Version Control Information - Objects are archived and stored with a version number.

The Work Repository stores the following metadata:

• Models (Data Models) - Tables, Data Quality Constraints, Relations, Lineage, etc.
• Projects – Interfaces (“Mappings” for those familiar with Informatica), Packages, Procedures, Knowledge Modules (Pre-built modules for data integration like CDC, SCD, etc.).
• Scenario Execution – Load plans, Scenarios, Schedules, Logs, etc.

A work repository containing only the execution information is known as an Execution Repository, usually is used in a production environment.

Graphical User Interfaces

The Graphical User Interface is called as ODI Studio. The ODI Studio is used to access the Master and Work Repositories. The various tools/components (which will be discussed below) within the ODI Studio help in administering the infrastructure, developing projects, scheduling and monitoring executions.

ODI provides 4 tools to manage different aspects and steps of an ODI project:

1. Designer
2. Operator
3. Topology Manager
4. Security Manager 

1. Designer
The Designer Navigator is the component of ODI where the most metadata of a project will be defined. It is used for designing ODI metadata and mapping objects. Some of the metadata components defined in a designer are as follows:

• Models: Models are basically the source or target definitions in an integration project. ODI supports models on various technologies; some of them are Oracle, DB2, Teradata, XML, Flat Files, Web Services, etc.
• Projects: Projects are the components that hold all the loading and transformation rules either for a functional module or an entire enterprise data warehouse. Some of the components in the projects are interfaces, procedures and packages.

2. Operator
In the operator navigator, you can monitor the execution of interfaces, packages, scenarios or load plans. The step by step session monitoring helps in performing debugging as well.

3. Topology Manager
The topology manager is used to describe the logical and physical architecture of the information system. The topology manager reads and writes only to the master repository as it maintains the technologies, data servers, schemas, contexts and other related information for each of the physical environments. This enables ODI to execute the same integration interfaces across different physical environments.

4. Security Manager
The security manager as called is used for managing security in ODI. Users and Profiles can be created here and privileges can be assigned to these users or profiles. The security manager metadata that is defined will be stored in the master repository.

Run-Time Agents

The run-time agents are responsible for executing the interfaces/packages/scenarios in ODI. The agents retrieve the code from the repository and orchestrate the execution of the code on the data servers. It also retrieves the return codes and messages for the execution.

There are two types of agents in ODI:

1. Standalone Agent

• Runs in a simple java local node
• Can be deployed where needed to perform integration flows

2. Java EE Agent

• Can be deployed as a web application
• Benefits from the features of an application server

ODI Console

The ODI Console is a web-based user interface (UI) where business users, developers, administrators and operators can have read access to the repository.  These business users can also perform topology configuration and production operations.

• Deployed on Oracle Weblogic Server.
• Plug-in available to integrate with the Oracle Fusion Middleware Control Console.

Varun Reddy Patrapalle is a Consulting Manager at KPI Partners. His areas of expertise include data warehousing, Oracle Business Intelligence, and Informatica.  His clients have included Fortune 500 companies within finance, retail, healthcare, education, and high-tech . Check out Varun's blog at KPIPartners.com.