Integration Options of Oracle Which Tool is Right

Integration Options of Oracle Which Tool is Right for the Job? Paul Scott Director, Business Intelligence SSG, Ltd. Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Agenda 1. Overview 2. Oracle Products I. III. IV. V. VI. PL/SQL Advanced Replication Oracle Streams Oracle Warehouse Builder Oracle Data Integrator Golden. Gate 3. Summary and Recommendations Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Data Integration Trends Data integration requirements have been evolving beyond classic ETL for Data Warehousing. Primary drivers of change include: • Larger data volumes • Heterogeneous sources and targets • Operational requirements that shrink or eliminate batch loading windows, driving companies to near-real-time and event-driven integration • The trend towards service-oriented architectures (SOA) • Economic pressures make ROI more important than ever Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle’s Integration Products Timeline 80’s V 6 1990’s Oracle 7 SQL*Loader 1988 PL/SQL 1990 Adv Replication 1992 Bridging the Gap Between Vision and Implementation™ 2000’s Oracle 8/8 i 9 i Transportable Tablespaces 1997 Data Guard 1997 10 g Streams 2002 Web Services 2001 Warehouse Builder Data Pump 2000 2003 © 2010 SSG, Limited. All rights reserved. 11 g ODI 2006 Golden. Gate 2009

PL/SQL Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

PL/SQL • • • Procedural Language extension to SQL First released in Oracle V 6 (1990) Loosely based on Ada (Pascal) language Not a standalone language Powerful features – External tables (for accessing data files) – Transparent Gateways (for accessing non-Oracle sources) – Optional Native Compilation (C code instead of runtime interpreted code) – Automatic optimization of code (code rewrite) Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

PL/SQL Advantages Included with all versions of the Oracle database Syntax is relatively easy to learn Very flexible Access to files through system packages (UTL_FILE) and external tables Access to other databases through database links/gateways Disadvantages All coding is manual Language is proprietary to Oracle Not a standalone product Custom code makes it difficult to perform upgrades Slower performance than some alternatives Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

ADVANCED REPLICATION Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Advanced Replication • Available since Oracle 7 • Allows database objects like tables, indexes, procedures, triggers, views and synonyms to be replicated • Does NOT allow objects like sequences and data types like LOBs to be replicated • GUI available through OEM Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Advanced Replication Types Read-only materialized views A master table is copied to one or more databases. Changes in the master table are reflected in the snapshot tables whenever the snapshot refreshes. The snapshot site determines the frequency of the refreshes; data is pulled. Updateable materialized views Similar to read-only snapshots, except that the snapshot sites are able to modify the data and send their changes back to the master. The snapshot site determines the frequency of the refreshes and the frequency with which updates are sent back to the master. Multi-master replication A table is copied to one or more databases, and each database has the ability to insert, update, or delete records from it. Modifications are pushed to the other database at an interval that the DBA sets for each replication group. Procedural replication A call to a packaged procedure or function is replicated to one or more databases. No data is replicated, only the procedure call. Useful for batch jobs that update many rows. Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Advanced Replication Advantages • Good for relatively small number of transactions Disadvantages • Complex to setup • Additional Oracle objects are created (snapshot logs, db links, triggers, materialized views, refresh groups, …) • Not recommended for HA environment or VLDB’s • Does not support all Oracle objects • Designed for Oracle-to-Oracle environments • Performance Issues Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

ORACLE WAREHOUSE BUILDER Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Warehouse Builder (OWB) Released January 2000 Good integration with Oracle Object editor to build dimensions Wizard to build external tables, materialized views, time dimensions Supports Relational (star schema) and Multidimensional (OLAP) Automatically installed with Oracle 11 g R 1 and higher (separate install with prior releases) Oracle Workflow components embedded in OWB 11. 1 + Enterprise ETL Option includes: Support large scale and complex deployments Slowly changing dimensions type 2 and 3 support Transportablespaces Interactive lineage Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

OWB Components 1. 2. 3. 4. 5. 6. Design Center for designing, managing, scheduling and deploying ETL processes Control Center Manager implements physical objects (such as staging tables) Control Center Service enables you to register locations and deploy ETL logic Target Schema is where data and data objects are loaded Warehouse Builder Repository stores metadata for all sources, targets, and ETL processes Repository Browser is web browser interface for reporting on repository Bridging the Gap Between Vision and Implementation™ CLIENT Design Center SERVER Control Center Service Control Center Mgr Repository Browser © 2010 SSG, Limited. All rights reserved. ORACLE DATABASE Warehouse Repository • Design Metadata • Control Center data Target • Cubes • Tables • Views • Packages • Dimensions • ETL Processes

OWB Interface – Two Options Design Center GUI Oracle Meta. Base for scripting OMB similar to SQL OMB*Plus like SQL*Plus Syntax similar to Tcl Cross Platform JDeveloper integration Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Warehouse Builder Summary Advantages Graphical representation of data flow and mappings Included with Database Install (OWB “standard”) Designed for Oracle as target database Support for modeling of relational and multi-dimensional data structures Support for loading Slowly Changing Dimensions (EE option) Both graphical and script based supported Disadvantages Oracle centric. Repository must be created in Oracle database. Oracle Meta. Base (OMB) scripting language To be integrated with Oracle Data Integrator in future release Advanced features require additional license (OWB-EE) Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

ORACLE STREAMS Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Streams Overview • Available since Oracle 9 i Release 9. 2 (May 2002) • Integrated feature of the database • Streams enables data and event movement – Implicit by reading redo or archived logs – Explicit by application enqueuing messages into staging area • Database must be in ARCHIVELOG mode • Supplemental logging must be enabled • Setup by use of Oracle Packages or OEM • Create RULES to indicate which changes to include and which to exclude – DBMS_STREAMS_ADM. ADD_TABLE_RULES • White list • Black list • Conflict resolution handlers provided Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Streams Flow - DML Source Target Table 1 Data Change Log Writer Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Streams Flow – Redo Log Source Target Table Log Writer Redo Log 2 Bridging the Gap Between Vision and Implementation™ Redo Written © 2010 SSG, Limited. All rights reserved.

Oracle Streams Flow – Stream Capture Source Target Table Log Writer 3 Redo Log Bridging the Gap Between Vision and Implementation™ Extract LCR Streams Capture © 2010 SSG, Limited. All rights reserved.

Oracle Streams Flow – Queue LCR Source Table Target Streams Queue Log Writer Redo Log Bridging the Gap Between Vision and Implementation™ Streams Queue 4 Streams Capture © 2010 SSG, Limited. All rights reserved. Write to local Queue or Remote Queue table

Oracle Streams Flow – Propagate LCR Source Table Target Streams Queue 5 Table Log Writer Redo Log Stream Apply can do transformation Streams Capture Streams Apply Non. Oracle Table Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Streams Restrictions Does not support: • CLOB, BLOB, NCLOB or ROWID datatypes • Raises ORA-26744 error • Changes to SYS, SYSTEM, CTXSYS, XDB, SYSMAN, … • Following DDL commands ignored: – – – CREATE/DROP DATABASE LINK CREATE/DROP MATERIALIZED VIEW LOG FLASHBACK DATABASE RENAME • Oracle Label Security • Index Organized tables that contain ROWID, User defined types • Spatial data types not supported Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Streams Advantages • • • Active/Active replication Near Real-time ETL for data warehouses Availability during database migration & upgrades Message queuing Event management and notification Good integration with OEM Disadvantages • • Oracle centric Poor for bulk loading Restrictions on datatypes Not long term direction for Oracle Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

ORACLE DATA INTEGRATOR Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Data Integrator • Purchased from Sunopsis in October 2006 • ODI supports heterogeneous databases, not just Oracle • Not a replacement for Oracle Warehouse Builder – OWB has Oracle data warehouse modeling features – OWB “mostly” free – ODI supplements OWB in the following scenarios: • Target database is non-Oracle (DB 2, Teradata, XML, SQL Server etc) • Sources include Web Services, XML, SOAP etc • Data integration is complex / real-time Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

ODI Differences • E-LT (Extract, Load and Transform) Engine • Knowledge Modules – Code templates – Over 100 provided out-of-the-box (SQL Server, DB 2, SQL*Loader, Log. Miner, EBS) – API’s available to develop custom KM’s • Data Integrity Controls create a data “firewall” – Reduces data prep time by not processing erroneous data • Good performance because data can be processed at a set basis on target database, not row-by-row. • Platform Independence • Built for SOA environments • Supports batch, event-based and near real-time integration Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

ODI Knowledge Modules Architecture Reverse Build Metadata Journalize Read from CDC Source Load Move from Source(s) to Target(s) Check Enforce constraints Integrate Transform and send to Target(s) Service Expose data WS Reverse WS WS Staging Tbls Load Integrate Services Journalize CDC Check Error Tbls Knowledge Models DB 2 Sybase SQL*Loader Bridging the Gap Between Vision and Implementation™ SQL Server JMS Journalize Oracle 11 g © 2010 SSG, Limited. All rights reserved. SCD CDC

ODI GUI Tools 1. Designer – Define data flows, transformations and integrity checks 2. Operator – Monitors production. Shows execution logs (errors, row counts, etc. ). 3. Topology Manager – Defines physical infrastructure, SOA connections 4. Security Manager – Manages user profiles and access privileges Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

ODI Runtime Scheduler Agent: 1. Retrieves code from the execution repository 2. Requests database servers, operating systems, or scripting engines to execute that code 3. Upon completion, updates the execution logs in the repository and then reports error messages and execution statistics Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

OWB/ODI Direction OWB-EE 11 g. R 2 • Adds ODI based integration • Support for native connectivity to non-Oracle sources using JDBC, as well as other data extraction and movement options • OWB code templates similar to ODI knowledge modules ODI 12 g (Future release) • Combined data integration platform for Oracle will provide the superset of the product features from both ODI and OWB • Expected in Calendar year 2011 Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Data Integrator Advantages • Does not require middle-tier server. • Heterogeneous environments supported (sources and targets) • Architecture moves data directly from source system to target using each server’s native technology • Optimized SET-based transformation for high volume transformations • Integrates to Oracle Data Quality • Complex transformations • Supports Web Services, XML, SOAP, … as sources Disadvantages • • Longer learning curve than other tools Some latency introduced in moving data OWB has better DW modeling features OWB still better choice if all Oracle environment Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

GOLDEN GATE Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Golden. Gate Key Differences • • Extracts transactions from Redo or Archive logs Only committed transactions are extracted Message queue is stored outside of the database in Trail Files No publish/subscribe model Near real-time (realistically 2 -3 seconds) Delayed Apply (allows target to be user-defined delay) Heterogeneous (No Gateways required, same trail file format) Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Golden. Gate Architecture Extract LAN/WAN Source Systems Target Systems • Reads transaction logs (redo and/or archive logs) of source database(s) • Captures committed transactions (insert, update, delete) • Maintains transaction integrity • Low impact on source database • Offers filtering, selective capture, matched patterns, … Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Golden. Gate Architecture Extract Trail File LAN/WAN Source Systems • • Target Systems Proprietary trail file contains data operations Platform-independent Stored outside of database Four formats: • • Golden. Gate proprietary XML Formatted ASCII ANSI ‘ 99 SQL Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Golden. Gate Architecture Capture Trail File Data Pump LAN/WAN Source Systems Target Systems • Data Pump process reads trail file and distributes data to target database(s) • Multiple data pumps may be configured to send data to multiple targets (Oracle or non-Oracle) Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Golden. Gate Architecture Capture Trail File Data Pump LAN/WAN Trail File Source Systems Target Systems • Proprietary trail file contains data operations • Platform-independent • Stored outside of database Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Golden. Gate Architecture Extract Trail File Data Pump LAN/WAN Source Systems Replicat Target Systems • Delivery applies changes to target system Bridging the Gap Between Vision and Implementation™ Trail File © 2010 SSG, Limited. All rights reserved.

Golden. Gate Architecture Extract Trail File Data Pump Trail File LAN/WAN Replicat Source Systems Target Systems Replicat Trail File Bi-Directional Data Pump • Bi-directional • Target system can also be a source system Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved. Trail File Extract

Golden. Gate Advantages • Simple, modular architecture • Active/Active replication • Future replication product for Oracle. Will receive considerable development over next couple of years • Supports older Oracle versions, back to Oracle 8 i • Heterogeneous Disadvantages • • • More of a replication tool, not for transformations Golden. Gate Command Interpreter (GGSCI) somewhat clumsy interface Restrictions on multi-byte character languages. External tables not supported. Does not have pre-built conflict resolution Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

CONCLUSION Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Comparison Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Summary • Expect Oracle to continue to evolve Integration Story – Streams -> Golden. Gate – OWB -> ODI • For New Projects -- look to ODI or Golden. Gate first SOA Use Cases: Ø Oracle Data Integrator Data Synchronization, Query Offloading: Ø Golden. Gate Traditional batch-oriented Data Warehouse loading: Ø Oracle Data Integrator and/or Oracle Warehouse Builder Near real-time Data Warehouse loading: Ø Golden. Gate and ODI Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.

Oracle Real Time Integration Model 1 Source 1 EMP DEPT Oracle Golden. Gate • Real time extracts from transactional systems • Continuous streaming load into ODS Schema of target 2 Oracle Data Integrator • High Performance ‘E-LT’ on target data warehouse Oracle Data Integrator On-Disk Logs Oracle Golden. Gate DIM Source 2 EMP DIM FACT DEPT Oracle Golden. Gate On-Disk Logs Bridging the Gap Between Vision and Implementation™ EMP DEPT ODS Schema © 2010 SSG, Limited. All rights reserved. DIM DW Schema

Thanks for Attending! Paul Scott SSG, Ltd. pscott@ssglimited. com Bridging the Gap Between Vision and Implementation™ © 2010 SSG, Limited. All rights reserved.