Oracle Data Integrator Best Practices For A Data Warehouse
Oracle Data Integrator BestPractices for a Data WarehouseOracle Best PracticesMarch 2008
Oracle Data Integrator Best Practicesfor a Data WarehousePREFACE . 7PURPOSE . 7AUDIENCE . 7ADDITIONAL INFORMATION . 7INTRODUCTION TO ORACLE DATA INTEGRATOR (ODI) . 8OBJECTIVES . 8BUSINESS-RULES DRIVEN APPROACH. 8Introduction to Business rules . 8Mappings . 9Joins . 9Filters. 9Constraints . 9TRADITIONAL ETL VERSUS E-LT APPROACH . 9UNDERSTANDING ORACLE DATA INTEGRATOR (ODI) INTERFACES . 10A BUSINESS PROBLEM CASE STUDY . 11IMPLEMENTATION USING MANUAL CODING. 13IMPLEMENTATION USING TRADITIONAL ETL TOOLS . 15IMPLEMENTATION USING ODI’S E-LT AND THE BUSINESS-RULE DRIVEN APPROACH . 17Specifying the Business Rules in the Interface. 17Business Rules are Converted into a Process . 18BENEFITS OF E-LT COMBINED WITH A BUSINESS-RULE DRIVEN APPROACH . 20ARCHITECTURE OF ORACLE DATA INTEGRATOR (ODI) . 23ARCHITECTURE OVERVIEW. 23GRAPHICAL USER INTERFACES . 24REPOSITORY . 24SCHEDULER AGENT . 26METADATA NAVIGATOR. 27Oracle Data Integrator Best Practices for a Data WarehousePage 2
USING ORACLE DATA INTEGRATOR IN YOUR DATA WAREHOUSE PROJECT . 28ODI AND THE DATA WAREHOUSE PROJECT . 28ORGANIZING THE TEAMS. 28REVERSE-ENGINEERING, AUDITING AND PROFILING SOURCE APPLICATIONS. 30DESIGNING AND IMPLEMENTING THE DATA WAREHOUSE’S SCHEMA . 32SPECIFYING AND DESIGNING BUSINESS RULES . 33BUILDING A DATA QUALITY FRAMEWORK . 38DEVELOPING ADDITIONAL COMPONENTS . 39PACKAGING AND RELEASING DEVELOPMENT . 40VERSIONING DEVELOPMENT . 40SCHEDULING AND OPERATING SCENARIOS . 41MONITORING THE DATA QUALITY OF THE DATA WAREHOUSE . 41PUBLISHING METADATA TO BUSINESS USERS . 41PLANNING FOR NEXT RELEASES . 42DEFINING THE TOPOLOGY IN ORACLE DATA INTEGRATOR . 44INTRODUCTION TO TOPOLOGY. 44DATA SERVERS . 45Understanding Data Servers and Connectivity . 45Defining User Accounts or Logins for ODI to Access your Data Servers . 47Defining Work Schemas for the Staging Area . 47Defining the Data Server . 48Examples of Data Servers Definitions . 49Teradata .49Oracle.50Microsoft SQL Server .50IBM DB2 UDB (v8 and higher) .50IBM DB2 UDB (v6, v7) and IBM DB2 MVS .51IBM DB2 400 (iSeries) .51Flat Files (Using ODI Agent).52XML .52Microsoft Excel .53PHYSICAL SCHEMAS. 53CONTEXTS . 54LOGICAL SCHEMAS . 56PHYSICAL AND LOGICAL AGENTS . 56THE TOPOLOGY MATRIX . 56OBJECT NAMING CONVENTIONS . 59DEFINING ODI MODELS . 61INTRODUCTION TO MODELS. 61MODEL CONTENTS. 62IMPORTING METADATA AND REVERSE-ENGINEERING . 63Introduction to Reverse-engineering. 63Reverse-engineering Relational Databases. 64Non Relational Models . 64Flat Files and JMS Queues and Topics.64Fixed Files and Binary Files with COBOL Copy Books .65XML .65LDAP Directories .66Other Non Relation Models .66Troubleshooting Reverse-engineering . 67Oracle Data Integrator Best Practices for a Data WarehousePage 3
JDBC Reverse-engineering Failure .67Missing Data Types .67Missing Constraints .68CREATING USER-DEFINED DATA QUALITY RULES . 68ADDING USER-DEFINED METADATA WITH FLEX FIELDS . 70DOCUMENTING MODELS FOR BUSINESS USERS AND DEVELOPERS . 71EXPORTING METADATA . 72IMPACT ANALYSIS, DATA LINEAGE AND CROSS-REFERENCES . 74OBJECT NAMING CONVENTIONS . 76IMPLEMENTING ODI PROJECTS AND MAPPINGS . 78INTRODUCTION TO PROJECTS . 78IMPORTING KNOWLEDGE MODULES . 79IMPLEMENTING BUSINESS RULES IN INTERFACES . 79Definition of an Interface . 79Designing the Business Rules . 80Mappings .80Joins .82Filters .83Constraints or Data Quality Rules .84Insert/Update Checkboxes on Mappings .84UD1.UD5 Markers on the Mappings .85Update Key and Incremental Update Strategy .85Flow Diagram, Execution Plan and Staging Area . 86Impact of Changing the Optimization Context. 94Enforcing a Context for Sources or Target Datastores . 94USING PROCEDURES. 95Introduction to Procedures . 95Writing Code in Procedures. 95Using the Substitution API. 98Using User Options. 99Handling RDBMS Transactions. 100Using Select on Source / Action on Target . 102Example1: Loading Data from a Remote SQL Database.102Example2: Dropping a List of Tables .103Example3: Sending Multiple Emails .103Example4: Starting Multiple Scenarios in Parallel .104Common Pitfalls . 105USING VARIABLES . 105Declaring a Variable . 106Assigning a Value to a Variable . 106Evaluating the Value of a Variable. 106Use Cases for Variables . 107Using Variables in Interfaces .107Using Variables in Procedures .108Using Variables in Packages .109Using Variables in the Resource Name of a Datastore .109Using Variables in a Server URL .110USING SEQUENCES. 112USING USER FUNCTIONS . 113BUILDING WORKFLOWS WITH PACKAGES . 114INTRODUCTION TO PACKAGES . 114ODI TOOLS OR API COMMANDS . 115Oracle Data Integrator Best Practices for a Data WarehousePage 4
PACKAGES EXAMPLES . 118Performing a Loop in a Package .118Starting Scenarios from a Package .119Waiting for incoming files .121ORGANIZING OBJECTS WITH MARKERS . 123GLOBAL OBJECTS . 123OBJECTS NAMING CONVENTIONS . 123ODI KNOWLEDGE MODULES . 126INTRODUCTION TO KNOWLEDGE MODULES . 126Loading Knowledge Modules (LKM) . 127Integration Knowledge Modules (IKM) . 127Check Knowledge Modules (CKM). 129Reverse-engineering Knowledge Modules (RKM) . 130Journalizing Knowledge Modules (JKM) . 131ODI SUBSTITUTION API . 132Working with Datastores and Object Names. 132Working with Lists of Tables, Columns and Expressions . 133Generating the Source Select Statement . 137Obtaining Other Information with the API . 138Advanced Techniques for Code Generation . 139LOADING STRATEGIES (LKM) . 140Using the Agent. 140Using Loaders . 142Using Loaders for Flat Files .142Using Unload/Load for Remote Servers .144Using Piped Unload/Load .146Using RDBMS Specific Strategies . 149INTEGRATION STRATEGIES (IKM) . 149IKMs with Staging Area on Target . 149Simple Replace or Append .149Append with Data Quality Check .150Incremental Update .153Slowly Changing Dimensions .157Case Study: Backup Target Table before Load .162Case Study: Tracking Records for Regulatory Compliance .163IKMs with Staging Area Different from Target . 165File to Server Append.165Server to Server Append .167Server to File or JMS Append .168DATA QUALITY STRATEGIES (CKM) . 169Standard Check Knowledge Modules . 169Case Study: Using a CKM to Dynamically Create Non-Existing References.174REVERSE-ENGINEERING KNOWLEDGE MODULES (RKM). 178RKM Process . 178SNP REV xx Tables Reference . 181SNP REV SUB MODEL .181SNP REV TABLE .181SNP REV COL .183SNP REV KEY .184SNP REV KEY COL .185SNP REV JOIN .185SNP REV JOIN COL.187Oracle Data Integrator Best Practices for a Data WarehousePage 5
SNP REV COND .
Oracle Data Integrator Best Practices for a Data Warehouse Page 7 Oracle Data Integrator for Best Practices for a Data Warehouse PREFACE Purpose This book describes the best practices for implementing Oracle Data Integrator (ODI) for a data warehouse solution. It is designed to help setup a successful
Oracle Data Integrator Log Locations and Configuration 4-5 Oracle Data Integrator High Availability and Failover Considerations 4-6 Oracle Data Integrator Clustered Deployment 4-7 Oracle Data Integrator Protection from Failure and Expected Behavior 4-8 WebLogic Server or Standalone Agent Crash 4-8
Oracle Data Integrator 12c New Features Overview Advancing Autonomous Database and Big Data O R A C L E W H I T E P A P E R SEPTEMBER 2 0 1 9 . ORACLE DATA INTEGRATOR 12C NEW FEATURES WHITEPAPER Table of Contents Executive Overview 6 Oracle Data Integrator 12.2.1.4.0 7 Oracle Sales Cloud 7 .
Oracle Data Integrator 12c New Features Overview Advancing Big Data and Cloud O R A C L E W H I T E P A P E R DECEMBER 2 0 1 8 . ORACLE DATA INTEGRATOR 12C NEW FEATURES WHITEPAPER Table of Contents Executive Overview 6 Oracle Data Integrator 12.2.1.3.1 7 Oracle Object Storage and Oracle Object Storage Classic 7 .
Oracle Compute hosting Oracle Data Integrator, the Oracle BI Applications Configuration Manager and Oracle Database Cloud Service. See detailed deployment documentation published on Oracle Support. (Figure 2) Hybrid solutions deploy the BI semantic model, analyses and dashboards on Oracle Analytics Cloud with Oracle Data Integrator and Oracle .
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
Refer to the Oracle Data Integrator Installation Guide for installing Oracle Data Quality products as well as Oracle Data Integrator. Setup the Data Files 1. On your server, create a directory where the sample files will be stored. We will refer to this directory as ODQ_SAMPLE_FILES throughout this document. (for example C:\demo\oracledq ). 2.
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
Alfredo López Austin). Co-Edited Volume: Art and Media History –––Modern Art in Africa, Asia and Latin America: An Introduction to Global Modernisms. Boston: Wiley-Blackwell, 2012 (Elaine O’Brien, editor; Everlyn Nicodemus, Melissa Chiu, Benjamin Genocchio, Mary K. Coffey, Roberto Tejada, co-editors). Exhibition Catalogs ––– “Equivocal Documents,” in Manuel Álvarez Bravo (c
