- 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 IntegratorFacing 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.
- 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
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:
- 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.
- 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.