Oracle Data Integration: CON7920 Making the Move to Oracle Data Integrator

Oracle Data Integration: CON7920

Making the Move to Oracle Data Integrator

Julien Testut

Principal Product Manager, Oracle Data Integration

Holger Friedrich

Safe Harbor Statement

The following is intended to outline our general product direction. It is intended for information

purposes only, and may not be incorporated into any contract. It is not a commitment to deliver

any material, code, or functionality, and should not be relied upon in making purchasing decisions.

The development, release, and timing of any features or functionality described for Oracle’s

Agenda

1

2

3

4

Oracle Data Integration Solutions Overview and ETL Strategy

Why is Oracle Data Integrator Unique?

Migration Utility Overview

Reuse and Migrate OWB Mappings

Q&A

Oracle Data Integration Solutions and Proven Benefits

 Improve Agility

• Deploy Projects Faster • Reliable Real-Time

 Reduce Risk

• Popular, Proven Tools • Open, Not Proprietary

 Reduce Costs

• Better Productivity • Eliminate ETL Servers

Analytic Data Integration

• Big Data Integration & Governance • Data Warehouse Integration

• Business Intelligence Applications

Enterprise Data Integration and Governance

• Enterprise Data Quality and Profiling

• Comprehensive, Heterogeneous Data Integration • Business Glossary and Metadata Management

Business Continuity

• Active-Active for Maximum Availability • Zero Downtime Migrations

• Data Consolidation / Application Modernization 24 x 7 x 365

Comprehensive Data Integration & Governance Capabilities

Real-Time Data Movement

– Low impact capture, stage in Hadoop

– Continuous data availability

Data Transformation

– Bulk data movement

– Pushdown data processing

Data Federation

– Virtualized Data Services

Data Quality & Verification

– Fix quality at the source

– Verify data consistency

Metadata Management

– Lineage and Impact Analysis

– Business Glossary Semantics

Data Governance Foundation

Oracle Data Integrator

(Transformation)

Enterprise Data Quality

(Profile, Cleanse, Match and De-duplicate)

Fast Load

Oracle GoldenGate

(Movement)

Enterprise Metadata Management & Business Glossary

(Business Glossary, Data Lineage, Impact Analysis and Data Provenance)

Data Service Integrator

(Federation)

GoldenGate Veridata

(Online Data Verification)

ELT Processing on Hadoop or SQL

Data Governance Foundation

Differentiated Technical Approach

Dynamic Data Movement

– Real-time CDC is by default, not ETL

– Least invasive on sources

– Proven best performance

– Integrated Oracle capture/apply

No ETL Engines

– Take the processing to the data; don’t move the data to the process

– Leverage your data engines for the workloads (Hadoop or SQL)

Most Heterogeneous

– Leverage open source Hadoop, not proprietary distributions

– Hadoop is the Hub, not ETL tools

– Open metadata standards

Oracle Data Integrator

(Transformation)

Enterprise Data Quality

(Profile, Cleanse, Match and De-duplicate)

Fast Load

Oracle GoldenGate

(Movement)

Enterprise Metadata Management & Business Glossary

(Business Glossary, Data Lineage, Impact Analysis and Data Provenance)

Data Service Integrator

(Federation)

GoldenGate Veridata

(Online Data Verification)

ELT Processing on Hadoop or SQL

Unified Team ODI-EE License OWB-EE 11gR2 ODI 11g 11.1.1.3 ODI 12.1.2 ODI 11g 11.1.1.7 (BI Apps) Jul 2010 Sep 2009 Jan 2009

Oct 2008 Apr 2013 Oct 2013

Knowledge Modules

OWB-EETL Concepts

In-Database ETL Features

Oracle ETL Roadmap Since January 2010

Release GA Date Premier Support Ends Extended Support Ends Sustaining Support Ends

OWB 9.2 Jul 2003 Oct 2007 Not Available Oct 2010

OWB 10.1 Apr 2004 Jul 2007 Not Available Jul 2010

OWB 10.2 Jul 2005 Jul 2010 Jul 2013 Indefinite

OWB 11.1 Aug 2007 Aug 2012 Aug 2015 Indefinite

OWB 11.2 Sep 2009 Jan 2015 Jan 2018 Indefinite

For more Information about Support Levels:

http://www.oracle.com/us/support/lifetime-support/index.html Customer Downloadable OWB Support Policies:

http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf

OWB Support Policies

Agenda

1

2

3

4

Oracle Data Integration Solutions Overview and ETL Strategy

Why is Oracle Data Integrator Unique?

Migration Utility Overview

Reuse and Migrate OWB Mappings

Q&A

Oracle Data Integrator

E-LT: Bulk Data Processing and Fast Data Transformation

Big Data Cloud

Apps

Database

Oracle Data Integrator

High Performance E-LT

Declarative Design Extensible Knowledge Modules Data Services Structured & Unstructured Data

•

Certified for leading

technologies to deliver

fast time to value

•

High-performance, low

cost of ownership E-LT

architecture

•

Lightweight deployment

•

Flexible, easy to enrich

functionality

Industry Leading Performance

Extremely Fast Execution and Reduced Cost

E-LT provides a flexible architecture for

optimized performance on any platform

Benefits



Leverages set-based transformations



Improves performance for loading,

no network hop



Takes advantage of existing infrastructure:

hardware and software

Conventional ETL Architecture

Extract _Load

Transform

Next Generation Architecture

“E-LT”

Load Extract

Declarative Design

Improved Developer Productivity For Faster Time to Solution

mappings

 Robust and Reusable: Create Mappings once for any

physical design

 Quick to define and refactor maps, high degree of

automation

 Extensible model for any data integration mechanism Benefits

 Significantly reduces the learning curve

 Shortens implementation times

 Simpler maintenance

ODI Declarative Design

Define How: Knowledge Modules

2 1 Automatically Generate Dataflow Define What You Want

Reverse Engineer Metadata Journalize (CDC) Load from Source to Staging Check Constraints Integrate, Transform Data Service

Knowledge Modules

Simpler Physical Design and Shorter Implementation Time

Oracle Sqoop Hive HBase Oracle

Merge SAP ERP

SAP BW Oracle Datapump Oracle DBLink JMS External Tables Teradata Oracle Spatial Siebel eBusines

Suite IBM DB2 Netezza SCD

Key Architecture Benefits



Faster development and simpler

maintenance using templates



Easy to extend and add new best practices



Enforces predictability and reduces cost of

ownership

Pluggable Knowledge Modules Architecture



Big Data: Hive, HBase, Sqoop



Best for Oracle: Merge, Spatial, Multi-Table

Insert, Optimizer Hints and more



Real-Time: CDC with GoldenGate



Applications: E-Business Suite, Siebel,

PeopleSoft, JD Edwards Enterprise One, JD

Edwards World, SAP ERP and SAP BW



Heterogeneous: Optimizations for all major

RDBMS: IBM DB2, Microsoft SQL Server,

Teradata, Netezza etc.

Applications

Legacy

Databases

Big Data

Cloud

Extended Connectivity

Agenda

1

2

3

4

Oracle Data Integration Solutions Overview and ETL Strategy

Why is Oracle Data Integrator Unique?

Migration Utility Overview

Reuse and Migrate OWB Mappings

Q&A



ODI 12c supports a phased migration approach

 Execute and monitor existing OWB 11gR2 jobs from ODI 12c

 Use the migration utility to convert existing OWB 11gR2 objects into their ODI equivalents  Start new developments using ODI 12c



Understand that OWB and ODI are different ETL tools

 There is no “free lunch” when migrating – a technical migration between different tools will never be 100% perfect or predictable

Invoking OWB 11.2 Processes from ODI 12c



New ‘OWB Runtime Repository’

technology available in Topology



OWB processes can be invoked

using the OdiStartOwbJob tool in

Packages



OWB audit information is

available in Operator and

Enterprise Manager



Versions supported

 OWB 11.2.0.3/11.2.0.4 to ODI 12.1.2/12.1.3



Design-time metadata migration only

 Data Objects

 Mappings



Separate command line utility

 3 different running modes: Fast Check/Dry Run/Run

 log and report files are available

Overview

Migration Scope Details



Module and Location



Data Objects

 Table (Columns, Keys,

Indexes)

 View (Columns, Keys)

 Materialized View

(Columns, Keys, Indexes)

 External Table (Columns)

 File (Records, Fields)

 Sequence



Mappings

 Classic, Code Template, Pluggable



Most Mapping Operators

 Aggregator, Constant, Deduplicator

 Expression, Filter

 Joiner, Key Lookup

 Mapping Input Parameter

 Pivot, Pre/Post Mapping Process, Sequence

 Set, Sorter, Splitter, Subquery

 Table Function, Transformation

 Unpivot



Process Flows



Dimensional metadata



Oracle Discoverer metadata and derived OBIEE metadata



Data Quality components



Custom PL/SQL



OMB Plus scripts



_Experts

Unsupported OWB Design time metadata (sample)

Agenda

1

2

3

4

Oracle Data Integration Solutions Overview and ETL Strategy

Why is Oracle Data Integrator Unique?

Migration Utility Overview

Reuse and Migrate OWB Mappings

Q&A

Making the Move from

Oracle Warehouse Builder to Oracle Data Integrator

Agenda

• Introduction

• Runtime integration

• Meta data migration

sumIT AG

• Consulting and implementation services in Switzerland

• Experts for

– Data Warehousing and

– Business Intelligence solutions

• Focussed on Oracle technology

• ‘BI Foundation specialized’ partner

• ‘Data Warehousing specialized’ partner

• Exalytics competence center with own server

• Our motto: Get Value From Data

• Visit our web site: www.sumit.ch

Holger Friedrich

• Computer Science diploma of

Karlsruhe Institute of Technology (KIT)

• Ph.D. in Robotics and Machine Learning

• More than 16 years experience with Oracle technology

• Expert for

–

Data Integration

–

Data Warehousing,

–

Data Mining and

–

Business Intelligence

• Technical Director of sumIT AG

Our Old Friend: Oracle Warehouse Builder

•

widely successful

– flow-based design

– performant code (EL-T)

– option of

Oracle DB 10g & 11g

– PL/SQL code generation

– f

reemium licensing model

•

end of lifetime

‣

certified with DB 12cR1 only

‣

in 'maintenance' mode

•

lacking state-of-the-art DI

‣

doesn’t support new DB

features

New Kid in Town: Oracle Data Integrator 12c

•

best of both world

– now flow-based design

– performant code (EL-T)

– built-in code generation for

๏

๏

๏

– Knowledge Module architecture

– Cloud Control integration

– Fusion Middleware integration

•

typical customer situation

‣

large investments in OWB

‣

OWB maps & flows run well

‣

new developments to do

‣

little resources for migration

‣

changes in existing DI

implementations required

Customers Need to be Able to…

•

Keep benefitting from their past

investments in OWB

– Keep running existing OWB code – Port code if it adds value only

•

Do new stuff with ODI12c

– further integrate DI tools (e.g. OGG)

– extend our DI to new technologies (e.g. Big Data, Hyperion, …)

Agenda

• Introduction

• Runtime integration

• Meta data migration

Co-Existence - OWB Runtime Integration

• Execution of OWB objects in an ODI

package

– mappings

– p

rocess flows

• Integration of OWB objects in the ODI

infrastructure

– adding the OWB Runtime

Repository to the ODI topology

– e

ntire OWB auditing is

integrated with ODI auditing

• Orchestration of steps where desired

(OWB or ODI)

– OWB process flows

– ODI load plans

OWB RT-Integration - Technology

OWB Runtime Repository Technology

Repository owner or user

OWB RT-Integration - Logical Architecture

OWB Repository made available via context & logical schema

OWB RT-Integration - Package Integration

OWB processes & mappings represented by ODI operator

OWB RT-Integration - Exec Properties

Mapping or process flow configuration as needed

Agenda

• Introduction

• Runtime integration

• Meta data migration

You Gotta Move It - Meta Data Migration

• Automatic, configurable migration

tool

• Simple handling

• Large, yet limited functionality

• Offers two migration modes

– Big bang

๏

– On demand

๏

Support of OWB Meta Data Objects

Unsupported:

•

Process Flows

•

Dimensional metadata

•

Oracle Discoverer metadata &

derived OBIEE metadata

•

Data Quality components

•

Custom PL/SQL

•

OMB Plus scripts

•

Experts

Supported:

• Classic OWB Mappings

• Code Template Mappings

• Pluggable Mappings

• Mapping Operators:

– Aggregator, Constant, Deduplicator,

Expression, External Table, Filter, Flat File, Joiner, Lookup, Input parameters,

Materialized View, Pivot, Pluggable

Mapping, Pre/Post Mapping, Sequence, Set, Sorter, Splitter, Subquery, Table, Table Function, Transform-Function, Unpivot, View

Tool Architecture & Migration Process

11.2.0.4 &

Patch 18537208

12.1.3

conf file log file

<OWB_HOME>/owb/bin/unix>./migration.sh

<ODI_MASTER_PASSWORD> <ODI_USER_PASSWORD> <OWB_WORKSPACE_OWNER_PASSWORD>

Configuration Information

• connection credentials for OWB & ODI

• migration mode

– just check – dry run – run

• reporting- & logging setup

• selection of objects

– all or some

• operator handling

– (don't) split Ansi joins – do or don’t migrate

Agenda

• Introduction

• Runtime integration

• Meta data migration

Summary & Conclusion

• Summary:

– Existing OWB DI applications can be used with ODI 12c, thanks to RT integration

– Automatic migration of OWB mappings with the OWB2ODI migration tool

– Customers can choose between

•

Big Bang migration and

•

incremental migration on demand over time

– Like always in life the 80/20 rule applies

• Conclusion: Oracle provides a powerful, easy-to-use tool for migration support from OWB to the next

generation data integration technology ODI12c

• Recommendation:

– start testing the migration tool right away

– develop migration/replacement strategy depending on results

Agenda

1

2

3

4

Oracle Data Integration Solutions Overview and ETL Strategy

Why is Oracle Data Integrator Unique?

Migration Strategy

Reuse and Migrate OWB Mappings

Q&A

Oracle DIS Session @ OOW ’14 – Oracle Data Integrator

4:00PM – CON7899 Oracle Data Integrator: Product Update and Future Strategy

5:00PM – CON7820 Making he Move from Oracle Warehouse Building to Oracle Data Integrator

3:30PM – CON7934 Tapping into the Big Data Reserve with All Data

4:45PM – CON7922 Tame Big Data with Oracle Data Integration

9:30AM – CON7926 Oracle Data

Integration: A Crucial Ingredient for Cloud Integration

10:45AM – CON7923 Oracle Data

Integration & Metadata Management for Seamless Enterprise

2:30PM – CON7921 Insight into Action: Business Intelligence Applications and Oracle Data Integrator

TUE

MON

Join the Community

#OOW14 #ODI12c #GoldenGate12c #EDQ12c

Oracle Data Integration blog

blogs.oracle.com/dataintegration

Connect with Oracle on Social Media

OR connect via the web

Oracle Data Integration Home Page