Book Review: "Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial"

In July 2010, Oracle Data Integrator 11gR1 (or ODI) was made available to the marketplace.  It has roots from the Oracle acquisition of Sunopsis in October 2006.  This product is important because:

• It is Oracle's strategic data integration platform 
• It is the first version of ODI architected, designed, and implemented as part of Oracle

Challenges of Data Integration Tasks

Data integration tasks include loading, enrichment, quality control and transformation of data.  The challenges of its tasks include:

• Data Complexity
• Data is stored in disparate applications, databases, files, operating systems, and in incompatible formats.
• Large volume of data has to be exchanged
• Transformation Complexity
• Complex transformations needed are beyond the capabilities of the SQL languages.
• Operational Complexity
• Systems with data of heterogeneous types flowing across different platforms are not easy to design or maintain.  In addition, data security, integrity and auditing requirements add more complexity.

Oracle Data Integrator

Facing such challenges, ODI has provided a unique solution.  This is where its ELT architecture (Extract-Load-Transform) comes into play. The concept with ELT is that instead of extracting the data from a source, transforming it with a dedicated platform, and then loading into the target database, you will extract from the source, load into the target, then transform into the target database, leveraging SQL for the transformations.

To some extent, ETL and ELT are marketing acronyms. When you look at ODI for instance, it can perform transformations on the source side as well as on the target side. You can also dedicate some database or schema for the staging and transformation of your data, and can have something more similar to an ETL architecture. Similarly, some ETL tools all have the ability to generate SQL code and to push some transformations at the database level.

However, the key differentiators of ODI are as follows:

• Its ability to dynamically manage a staging area (location, content, automatic management of table alterations)
• Its ability to generate code on source and target systems alike, in the same transformation
• Its ability to generate native SQL for any database on the market
• Its ability to generate DML and DDL, and to orchestrate sequences of operations on the heterogeneous systems

The Book

This book has done a great job of introducing ODI at both conceptual and operational levels.  It covers how ODI leverages next generation Extract-Load-Transformation technology to deliver extreme performance in enabling state of the art solutions that help deliver rich analytics and superior business intelligence in modern data warehousing environments.

At conceptual level, it has discussed how ODI can deal with heterogeneous datatypes with ease and efficiency.  For example, it introduces the concepts of:

• Abstraction
  • ODI models and data integration objects are abstracted to be independent of the physical data server type (database, file, JMS queues and topics, and so on), so they will have a common look and feel despite having dramatically different physical representations. 
• Decoupling
  • Logical schema names are used to decouple us from any specific operational environment while contexts are used to link the physical and logical representations.
• Declarative Design
• The definition of the transformation logic is isolated from the definition of the data movement and integration logic.
  • Separating the two allows developers to focus more on the moving parts (the transformations) than on the stable parts (the integration strategy).
• Variables are used to store dynamic information.
• Delegating and distributing processing
• Agents are used as orchestrators for the data movement and transformations.

Using these design patterns, ODI allows:

• Integration strategies be reused
• Heterogeneity of data server types be encapsulated via models and other integration objects
• Life management of development, test, or production environments be easily maintained

For operational details, the largest part of the book is a set of hands-on step-by-step tutorials that build a non-trivial Order Processing solution that you can run, test, monitor, and manage. It has highlighted the key capabilities of the product in relation to data integration tasks (loading, enrichment, quality, and transformation) and the productivity achieved by being able to do so much work with heterogeneous datatypes while writing so little SQL.

References

1. Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial
2. Oracle Fusion Middleware Supported System Configurations
3. Take SOA Deployments to the Next Level with Oracle Data Integrator