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

Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial
eBook: $35.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 15%!
Print + free eBook + free PacktLib access to the book: $95.98    Print cover: $59.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • Discover the comprehensive and sophisticated orchestration of data integration tasks made possible with ODI, including monitoring and error-management 
  • Get to grips with the product architecture and building data integration processes with technologies including Oracle, Microsoft SQL Server and XML files
  • A comprehensive tutorial packed with tips, images and best practices 

Book Details

Language : English
Paperback : 384 pages [ 235mm x 191mm ]
Release Date : May 2012
ISBN : 184968068X
ISBN 13 : 9781849680684
Author(s) : David Hecksel, Bernard Wheeler, Peter C. Boyd-Bowman, Julien Testut, Denis Gray, Christophe Dupupet
Topics and Technologies : All Books, Enterprise Products and Platforms, Oracle Database, Enterprise, Oracle

Table of Contents

Chapter 1: Product Overview
Chapter 2: Product Installation
Chapter 3: Using Variables
Chapter 4: ODI Sources, Targets, and Knowledge Modules
Chapter 5: Working with Databases
Chapter 6: Working with MySQL
Chapter 7: Working with Microsoft SQL Server
Chapter 8: Integrating File Data
Chapter 9: Working with XML Files
Chapter 10: Creating Workflows—Packages and Load Plans
Chapter 11: Error Management
Chapter 12: Managing and Monitoring ODI Components
Chapter 13: Concluding Remarks
  • Chapter 1: Product Overview
    • ODI product architecture
      • ODI repository
        • Repository overview
        • Repository location
        • Master repository
        • Work repository
        • Execution repository
        • Lifecycle management and repositories
      • Studio
      • Agent
      • Console
      • Oracle Enterprise Manager
    • ODI key concepts
      • Execution Contexts
      • Knowledge Modules
      • Models
      • Interfaces
        • Interface descriptions
        • Interface mappings
        • Interface flow tab
        • Interface controls
      • Packages and Scenarios
    • Summary
    • Chapter 2: Product Installation
      • Prerequisites
        • Prerequisites for the repository
        • Prerequisites for the Oracle Universal Installer
        • Prerequisites for the Studio
        • Prerequisites for the Standalone Agent
      • Installing ODI 11g
        • Two installation modes
        • Creating the repository with RCU
        • Installing the ODI Studio and the ODI Agent
        • Starting the ODI Studio for the first time
      • Post installation—parameter files review
      • Summary
      • Chapter 3: Using Variables
        • Defining variables
          • Variable location and scope
          • Variable definitions
          • Refreshing variables
          • Variable history
        • Using variables for dynamic information
          • Assigning a value to a variable
            • Setting a hardcoded value
            • Refresh Variable
            • Passed as a parameter (Declare Variable)
          • Referencing variables
          • Variables in interfaces
          • Variables in models
          • Variables in topology
        • Using variables to alter workflows
          • Packages
          • Load Plans
        • Summary
        • Chapter 4: ODI Sources, Targets, and Knowledge Modules
          • Defining Physical Schemas, Logical Schemas, and Contexts
            • Defining physical data servers
            • Defining Physical Schemas
              • Data schemas and work schemas
            • Defining Logical Schemas and Contexts
            • Non-database technologies
          • Reverse-engineering metadata into ODI models
            • Standard reverse-engineering
            • Custom reverse-engineering
            • File reverse-engineering
            • XML reverse-engineering
          • Examining the anatomy of the interface flow
            • Example 1: Database and file to database
            • Example 2: File and database to second file
            • Example 3: File to Enterprise Application
          • Importing and choosing Knowledge Modules
            • Choosing Knowledge Modules
            • Importing a Knowledge Module
            • KMs—A quick look under the hood
            • Configuring behavior with KM options
          • Examining ODI Interfaces
            • Overview tab
            • Mapping tab
            • Flow tab
            • Quick-Edit tab
          • Summary
          • Chapter 5: Working with Databases
            • Sample scenario description
              • Integration target
              • Integration source
              • Integration mappings
              • Data flow logistics
            • Exercise 1: Building the Load_Customer interface
              • Building the topology
              • Reverse-engineering the model metadata
              • Moving the data using an ODI interface
              • Checking the execution with the Operator Navigator
            • Summary
            • Chapter 6: Working with MySQL
              • What you can and can't do with MySQL
              • Working with MySQL
                • Obtaining and installing the software
              • Overview of the task
              • Integrating the product data
                • Product data target, sources, and mappings
                • Product interface flow logistics
              • Integrating inventory data
                • Inventory target, sources, and mappings
                • Inventory interface flow logistics
            • Using MySql with ODI
              • Adding the MySQL JDBC driver
              • Expanding the topology
              • Reverse-engineering revisited
              • Preparing to move the product data
              • Using simulation and execution
              • Moving the inventory data
            • Summary
              • Chapter 7: Working with Microsoft SQL Server
                • Example: Working with SQL Server
                  • Overview of the task
                  • Integrating the Sales data
                    • Source
                    • Target
                    • Integrations
                • Sample scenario
                  • Expanding the ODI topology
                  • Setting up the topology
                  • Reverse-engineering the Model metadata
                  • Creating interfaces and mappings
                  • Load Sales Person interface
                  • Load Sales Person mapping
                  • Automatic Temporary Index Management
                  • Load Sales Region interface
                  • Checking the execution with the Operator Navigator
                    • Execute the Load Sales Person interface
                    • Verify and examine the Load Sales Person results
                    • Verify and examine Load Sales Region results
                • Summary
                • Chapter 8: Integrating File Data
                  • Working with flat files
                    • Scope
                    • Prerequisites for flat files
                    • Integrate the file data into an Oracle table
                      • Partner data target, source, and mappings
                      • Partner interface flow logistics
                  • Step-by-step example
                    • Expanding the topology for file handling
                    • Integrating the Partner data
                      • Creating and preparing the project
                      • Creating the interface to integrate the Partner data
                      • Running the interface
                  • Summary
                  • Chapter 9: Working with XML Files
                    • Introduction to XML
                    • Introducing the ODI JDBC driver for XML
                      • ODI and its XML driver—basic concepts
                    • Example: Working with XML files
                      • Requirements and background
                      • Scope
                      • Overview of the task
                        • Integrating a Purchase Order from an XML file
                        • Creating models from XML files
                        • Integrating the data from a single Purchase Order
                        • Single order interface flow logistics
                      • Sample scenario: Integrating a simple Purchase Order file
                        • Expanding the Topology
                        • Reverse-engineering the metadata
                        • Creating the Interface
                        • Creating procedures
                    • Summary
                    • Chapter 10: Creating Workflows—Packages and Load Plans
                      • Packages
                        • Creating a package
                        • Adding steps into a package
                        • Adding tools in a package
                          • Changed Data Capture
                          • Event Detection
                          • Files
                          • Internet
                          • Metadata
                          • ODI Objects
                          • Plugins
                          • SAP
                          • Utilities
                          • Adding tools to a package
                          • Using ODI Tools
                        • Retry versus fail
                        • Best practice: No infinite loop
                        • Generating a scenario from a package
                      • Load Plans
                        • Serial and parallel steps
                        • Objects that can be used in a Load Plan
                        • Exception handling
                        • Using Packages and Load Plans
                      • Summary
                      • Chapter 11: Error Management
                        • Managing data errors
                          • Detecting and diverting data errors
                            • Data quality with ODI constraints
                            • ODI error table prefix
                            • Contents of an error table
                            • Using flow control and static control
                            • Using error thresholds
                          • Correcting and recycling data errors
                            • Recycling errors and ODI update keys
                        • Managing execution errors
                          • Handling anticipated errors
                            • Causing a deliberate benign error with OdiBeep
                          • Handling unexpected design-time errors
                            • More detailed error investigation in Operator Navigator
                          • Handling unexpected runtime errors
                        • Handling operational errors
                        • Summary
                        • Chapter 12: Managing and Monitoring ODI Components
                          • Scheduling with Oracle Data Integrator
                            • Overview
                              • Illustrating the schedule management user interface
                              • Using third-party schedulers
                          • Fusion Middleware Console Control
                            • Launching and accessing the FMCC
                            • Domain
                            • Agent
                              • Starting and stopping
                              • Performance summary
                            • Log file visibility and aggregation
                              • Visibility
                              • Aggregation
                            • Repository visibility
                              • Session statistics
                          • Oracle Data Integrator Console
                            • Launching and accessing ODI Console
                            • Data Lineage
                            • Flow Map
                          • Summary

                          David Hecksel

                          David Hecksel is a Principal Data Integration Architect at Oracle. Residing in Dallas Texas, David joined Oracle in 2006 as a pre-sales Architect for Oracle Fusion Middleware. Six months after joining, David volunteered to add pre-sales coverage for a recently acquired product called Oracle Data Integrator and the rest (including the writing of this book) has been a labor of love working with a platform and solution that simultaneously provides phenomenal user productivity and system performance gains to the traditionally separate IT career realms of Data Warehousing, Service Oriented Architects, and Business Intelligence developers. Before joining Oracle, David spent six years with Sun Microsystems in their Sun Java Center, was CTO for 4 years at Axtive Software architecting and developing several one-to-one marketing and web personalization platforms such as e.Monogram. In 1997, David also invented, architected, developed, and marketed online the award-winning JCertify product – the industry's first electronic delivery of study content and exam simulation for the Certified Java Programmer exam. Prior to Axtive Software, David was with IBM for 12 years as a software developer working on operating system, storage management, and networking software products. David holds a BS Computer Science degree from the University of Wisconsin-Madison and a Masters of Business Administration from Duke University.

                          Bernard Wheeler

                          Bernard Wheeler is a Customer Solutions Director at Oracle in the UK, where he has a focus on Information Management. He has been at Oracle since 2005, working in pre-sales technical roles covering Business Process Management, SOA, and Data Integration technologies and solutions. Before joining Oracle, Bernard held various pre-sales, consulting, and marketing positions with vendors such as Sun Microsystems, Forte Software, Borland, and Sybase as well as working for a number of systems integrators. Bernard holds an Engineering degree from Cambridge University.

                          Peter C. Boyd-Bowman

                          Peter C. Boyd-Bowman is a Technical Director and Consultant with the Oracle Corporation. He has over 30 years of software engineering and database management experience, including 12 years of focused interest in data warehousing and business intelligence. Capitalizing on his extensive background in Oracle database technologies dating back to 1985, he has spent recent years specializing in data migration. After many successful project implementations using Oracle Warehouse Builder, and shortly after Oracle's acquisition of the Sunopsis Corporation, he switched his area of focus over to Oracle's flagship ETL product: Oracle Data Integrator. Peter holds a BS degree in Industrial Management and Computer Science from Purdue University and currently resides in North Carolina. Peter is a co-author of the book Getting Started with Oracle Data Integrator 11g: A Hands-on Tutorial.

                          Julien Testut

                          Julien Testut is a Product Manager in the Oracle Data Integration group focusing on Oracle Data Integrator. Julien has an extensive background in Data Integration and Data Quality solutions and is a co-author of Getting Started with Oracle Data Integrator 11g: A Hands-on Tutorial. Prior to joining Oracle, he was an Applications Engineer at Sunopsis, which was then acquired by Oracle. Julien holds a Masters degree in Software Engineering.

                          Denis Gray

                          Denis Gray is a Director of Product Management for Data Integration at Oracle. Denis has over 15 years of experience in the data-integration field. For the past seven years, Denis has been an integral part of Oracle Development Organization as a Product Manager within Fusion Middleware, delivering data integration solutions. Prior to this, Denis was a data integration consult for Hyperion Solutions (Oracle). Here, Denis worked at many of the largest Fortune 100 companies, building data warehouses and implementing business intelligence solutions. Denis has a Bachelor's Degree in Computer Science from the University of Missouri and currently resides in St. Louis, MO. Denis also co-authored the Packt book Getting Started with Oracle Data Integrator 11g: A Hands-on Tutorial.

                          Christophe Dupupet

                          Christophe Dupupet is a Director in the Fusion Middleware Architects Team, where he leads the expertise on ODI. The team works closely with strategic customers that implement ODI, and helps define best practices on the product in terms of architecture, implementation, and operations. Prior to Oracle, Christophe was part of the team that started the operations for Sunopsis in the US, where he lead the technical team (presales, support, and training). Sunopsis was acquired by Oracle in 2006. Christophe holds an Operations Research degree from EISTI in France, a Masters Degree in Operations Research from Florida Tech, and a certificate in Management from Harvard University.

                          Code Downloads

                          Download the code and support files for this book.

                          Submit Errata

                          Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.


                          - 5 submitted: last submission 17 Jan 2014

                          Errata type: Typo | Page number: 89

                          Do no use "localhost" in the JDBC URL
                          Should be: Do not use "localhost" in the JDBC URL


                          Errata type: Typo | Page number: 335

                          Section header: "Fusion Middleware Console Control"
                          Should be: Fusion Middleware Control Console


                          Errata type: Technical | Page number: 221

                          Under "Load Sales Person interface", we should insert these steps after "we follow these steps":

                          1. We create a project called Chapter 7 SQL Server
                          2. We right-click on the project name to import the following Knowledge Modules: IKM Oracle Incremental Update and LKM SQL to Oracle.

                          The existing steps in page 221 and 222 will have to be re-numbered starting at 3.



                          Errata type: Technical | Page number: 228

                          Execute on: Target
                          Should be Execute on: Staging Area


                          Errata type: Code | Page number: 230

                          In the code bundle, consider the files create_SALESSYSTEM_schema.sql and create_SALESSYSTEM_schemaMS2008.sql. At the end of the file, there is a series of insert statements that start with the following line:

                          INSERT INTO REGIONS VALUES (1,'West');

                          An additional parameter must be added to all of these, like this:

                          INSERT INTO REGIONS VALUES (1,'West',1);

                          The file create_DATAMART_schema.sql needs to have an additional table definition:

                          CREATE TABLE SALES_REGION
                          ( REGION_ID NUMBER NOT NULL,
                          REGION VARCHAR2(20),
                          COUNTRY VARCHAR2(50),
                          LAST_UPDATE DATE NOT NULL,



                          Sample chapters

                          You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

                          Frequently bought together

                          Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial +    Getting Started with IBM FileNet P8 Content Manager =
                          50% Off
                          the second eBook
                          Price for both: ₨461.60

                          Buy both these recommended eBooks together and get 50% off the cheapest eBook.

                          What you will learn from this book

                          • Get to grips with the various components of the Oracle Data Integrator architecture
                          • Learn how to install ODI quickly and effectively using recommended best practices
                          • Orchestrate your data integration processing by designing ODI packages and scenarios 
                          • Discover how to develop ODI interfaces in order to effortlessly move data between servers and files 
                          • Get the full lowdown on both Error Processing and Management and Monitoring
                          • Give your knowledge a boost with in-depth coverage of various data sources and targets including Oracle, Microsoft SQL Server and XML files
                          • Master key ODI concepts like Physical and Logical Architectures or the Knowledge Modules
                          • Take advantage of expert advice from the authors’ combined 15 years of ODI experience

                          In Detail

                          Oracle Data Integrator (ODI) is Oracle's strategic data integration platform for high-speed data transformation and movement between different systems. From high-volume, SOA-enabled data services, to trickle operations – ODI is a cutting-edge platform that offers heterogeneous connectivity, enterprise-level deployment, and strong administrative, diagnostic, and management capabilities.

                          "Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial" provides you with everything you to get up and running with Oracle Data Integrator, and more! Following an example scenario, the book covers essential information about the ODI architecture and using ODI across different databases (Oracle, Microsoft SQL Server and MySQL), and file types such as XML, before covering Orchestrating Data Integration Workflows, Error Management, Operational Management and Monitoring, and beyond.

                          "Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial" begins by describing the Oracle Data Integrator architecture and teaching you to install the product following best practices. You’ll then be introduced to some of the key concepts of ODI such as the Knowledge Modules.

                          Later topics include moving and transforming data from sources to targets including the Oracle Database, Microsoft SQL Server, MySQL, Flat files, and XML files, each with illustrated hands-on examples for the different technologies. Your learning experience will be made all the more rich with chapters introducing, explaining and leveraging additional ODI functionality such as variables, reusable procedures, temporary indexes and more.

                          Finally ODI's workflow and task orchestration capabilities are explained before introducing you to Error Management with ODI's built-in 'error hospital' and 'error recycling' capabilities for non-compliant data, not to mention tackling ODI Studio, ODI Console and Enterprise Manager Fusion Middleware Control.


                          "Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial" is a practical tutorial bursting with tips, illustrations and real-world best practices to get you on your way with ODI.

                          Who this book is for

                          If you are a software/ETL developer or database administrator who is new to Oracle Data Integrator but want to get hands-on with the product quickly, then "Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial" is for you. You should have some experience with databases, SQL and ETL technologies.

                          Code Download and Errata
                          Packt Anytime, Anywhere
                          Register Books
                          Print Upgrades
                          eBook Downloads
                          Video Support
                          Contact Us
                          Awards Voting Nominations Previous Winners
                          Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
                          Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software