tech oracle data integrator_new1

8/10/2019 TECH Oracle Data Integrator_New1

http://slidepdf.com/reader/full/tech-oracle-data-integratornew1 1/53



<Insert Picture Here>

Oracle Data Integrator – Technical DeckMark PareSr. Sales ConsultantOracle Higher Education



3

<Insert Picture Here>• 4 Key Differentiators• Architecture• 6 Steps to Production• ODI or ESB?• Popular Usage Scenarios

Agenda



4

Oracle Data Integrator

4 Key Differentiators



5

Data IntegrationData

WarehousingMaster DataManagement

Real TimeMessaging

FederationMigration

Data in Disparate Sources

ERP

------

------

------

CRM

- - -

Legacy

------

------

------ ------

------

------

Best-of-breed Applications

Information How and Where you Want It

Business IntelligenceCorporate Performance

ManagementBusiness Activity

MonitoringBusiness Process

Management

HAVE…

NEED…

- - -- - -

- - -- - -

DataSynchronization

Why Data Integration?



6

Challenges & Emerging SolutionsIn Data Integration

CHALLENGE EMERGING SOLUTION

1. Increasing data volumes;decreasing batch windows

2. Non-integrated integration

3. Complexity, manual effort ofconventional ETL design

4. Lack of knowledge capture

Shift from E-T-L to E-LT

Convergence of integrationsolutions

Shift from custom coding todeclarative design

Shift to pattern-driven development




• Data Movement and Transformation from MultipleSources to Heterogeneous Targets

1. Per formance : Heterogeneous “E -LT” 2. Flexibi l i ty : Active Integration Platform3. Product iv i ty : Declarative Design4. Hot-Pluggable : Knowledge Modules

BENEFITS KEY DIFFERENTIATED FEATURES



Different ia tor : E-LT ArchitectureHigh Performance

Conventional ETL Architecture

Extract LoadTransform

Next Generation Architecture

“ E-LT ” LoadExtract

Transform Transform

Transform in Separate ETL Server• Proprietary Engine• Poor Performance• High Costs• IBM & Informatica ’ s approach

Transform in Existing RDBMS• Leverage Resources• Efficient• High Performance

BenefitsOptimal Performance & ScalabilityEasier to Manage & Lower Cost

1



Enables real-time data warehousing and operational data hubsServices plug into Oracle SOA Suite for comprehensive integration


Data-oriented Integration

Event Conductor

Event-orientedIntegration

Service Conductor

Service-orientedIntegration

Declarative Design

Metadata

Data Conductor

Differentiator : Active IntegrationBatch, Event-based, and Service-oriented Integration

• Evolve from Batch to NearReal-time Warehousing onCommon Platform

• Unify the Silos of DataIntegration

• Data Integrity on the Fly• Services Plug into Oracle

SOA Suite

• Benefits

2



Differentiator : Declarative DesignDeveloper Productivity

Conventional ETL DesignSpecify ETL Data Flow Graph• Developer must define every step of

Complex ETL Flow Logic• Traditional approach requires specialized

ETL skills• And significant development and

maintenance efforts

Declarative Set-based Design• Simplifies the number of steps• Automatically generates the Data Flow

whatever the sources and target DB• Example: [SALAH]

Benefits Significantly reduce the learning curve Shorter implementation times Streamline access to non-IT pros

ODI Declarative Design

Define How : Built-in Templates

DefineWhat

You Want

AutomaticallyGenerateDataflow

1 2

3



JournalizeRead from CDC

Source

LoadFrom Sources to

Staging

CheckConstraints before

Load

IntegrateTransform and Move

to Targets

ServiceExpose Data andTransformation

Services

ReverseEngineer Metadata

Tailor to existing best practicesEase administration workReduce cost of ownership

Reverse

Journalize

Load

Check

IntegrateServices

Pluggable Knowledge Modules Architecture

CDC

Sources

Staging Tables

Error Tables

Target Tables

WS

WS W

S

SAP/R3

Siebel

Log Miner

DB2 Journals

SQL ServerTriggers

OracleDBLink

DB2 Exp/Imp

JMS Queues Check MSExcel

CheckSybase

OracleSQL*Loader

TPump/Multiload

Type II SCD

Oracle Merge

Siebel EIMSchema

Oracle WebServices

DB2 WebServices

Sample out-of-the-box Knowledge Modules

Benefits

Differentiator : Knowledge ModulesHot-Pluggable: Modular, Flexible, Extensible

4



Oracle Data IntegratorArchitecture



• Java design-timeenvironment

• Runs on any platform• Thin client for browsing

Metadata

• Java runtime environment• Runs on any platform• Orchestrates the execution of

data flows

• Metadata repository• Pluggable on many RDBMS• Ready for deployment• Modular and extensible

metadata

Design-Time

Metadata Management

Runtime

Agent

Data FlowConductor

Service Interfaces and Developer APIs

UserInterfaces

ThinClient

Data FlowGenerator

Knowledge ModuleInterpreter

Knowledge Modules

MasterRepository

WorkRepositories

RuntimeRepositories

Data FlowGenerator

RuntimeSession

Interpreter

Data Flow

Operator

Designer

Architecture: Conceptual View



Architecture: Component View

ODI Design-Time Environment Development Servers andApplications

Design-timeRepositories

Code Execution

Execution Log Return Codes

AgentData FlowConductor

CRM

LegacyERP

DataWarehouse

Files /XML

User Interfaces

Administrators

Designers

Topology/Security

Metadata/Rules

Development

ESB

Production Servers andApplications

ODI Runtime Environment

Runtime

Repository Return Codes

Code

Execution Log

Execution

MetadataNavigator

Production

CRM

LegacyERP

Data

Warehouse

Files /XML

ESB

User Interfaces

Administrators

Operators

Thin Client

Data Stewarts

Topology/Security

Execution Log

Metadata Lineage

Agent

Data FlowConductor

Scenariosand ProjectsReleases



Oracle Data Integrator6 steps to Production



ODI Design-Time Environment ODI Runtime Environment

User Interfaces

Overview: 6 steps to Production

1. Retrieve/Enrich metadata

2. Design transformations3. Orchestrate data flows

4. Generate/Deploy data flows

5. Monitor executions6. Analyze impact / data lineage

Development

Development Servers and Applications


CRM

Legacy

ERP

DataWarehouse

Files / XML

User Interfaces

Administrators

Designers

ESB

Design-timeRepositoriesDesign-time

Repositories

Production

Production Servers and Applications


CRM

Legacy

ERP

DataWarehouse

Files / XML

Operator

MetadataNavigator

ESB

RuntimeRepository



1. Reverse-engineer Metadata• Automatic• Customizable• 40+ technologies supported

2. Enrich Metadata• Documentation• Declarative rules for Data

Integrity• Cross-technologies references

Design-Time Environment

ODI Designer

Design-timeRepositories

Development Serversand Applications

CRM

Legacy

ERP DataWarehouse

Files / XML

ESB

Retrieve/Enrich Metadata1



Oracle Data Integrator“Interface”

Declarative Design

1Define What You Want

3Automatically GenerateData flows

2 Define How to Do It: Select Template

Bulk Load • Changed Data Capture • Incremental Update • Slowly Changing Dimension

Design Transformations2



1. Sequence Transformations

2. Leverage OracleDI Tools• Data Quality Processes• Files/Archives Management• Send/Receive Emails• Web Services Invokation• Event Detection• Create your Own Tools

3. Use Control Structures• Loops• Conditions• Error Handling

3 Orchestrate Data Flows



1. Create Scenarios•

Compile Data Flows for Run-time2. Version the Data Flows

• Advanced Version Management

3. Deploy to ProductionDesign-timeRepositories

Scenariosand ProjectsReleases

RuntimeRepository

Generate and Deploy Data Flows4



Analyze impact / data lineage

• Maintain a large number of

data flows in a complexenvironment

• Web-based end-to-end datalineage1. Understand your data flows2. Follow the path of data3. Drill-down to transformations

?

6



23

Oracle Data IntegratorODI or ESB?



24

What tool is best suited for task X?

Requirement ESB

ODI Recommended

Latency / VolumeSynchronous Integration ESB

Asynchronous Integration with routing and transformation ESB

Asynchronous Integration for Active Data Warehousing (mini-batch) ODI

Batch Integration with High Volume ODI

TransformationsIn-memory XSLT Transformations (XML to XML) ESB

Transformations in App Server ESB

Transformations in Database (E-LT) ODI

Integration TopologyData Warehouse Loading (E-LT) ODI

JMS to JMS ESB

JMS to DB/App with routing and transformation (real-time or synchronous) ESB

JMS to DB/App with bulk transformation (mini-batch) ODI

DB/App to DB/App (batch or mini-batch with CDC) ODI

DB/App to DB/App (synchronous or real-time with CDC Adapters) ESB…



25


ESB and ODI in real-life scenarios

Data VolumeProcessing

DataLatency

Message byMessage

Mini Batches Large Volume(over 1M)

Synchronous(immediate)

Asynchronous

Batch(over 2 hours)

Oracle Enterprise Service Bus



26

Oracle Data IntegratorExtended Capabilities



27

Extended Capabilities

• Master Data Management enabled• Common Format Designer• Automated generation of canonical format and transformations• Built-in Data Integrity

• Real-time enabled• Changed Data Capture• Message Oriented Integration (JMS)

• SOA enabled• Generation of Data Services• Generation of Transformation Services

• Extensibility• Knowledge Modules Framework• Scripting Languages• Open Tools



28

Oracle Data IntegratorMaster Data Management Enabled



29

MDM Enabled: Canonical Format Design

• Use in conjunction withpackaged MDM solution

• Design and PopulateCanonical Format1. Use existing metadata artifacts

to design MDM application(entities, fields, relationships)

2. Generate and maintain MasterData structure

3. Generate and deploytransformations using metadataartifacts

CRM

Enterprise Service Bus

SCM Legacy ERP

Master Data



30

MDM Enabled: Built-in Data Integrity

• Data Integrity Firewall

• Auditing, cleansing andrecycling

1. Declare constraints at tablelevel

2. Design mappings and checkflow integrity

3. Audit, cleanse or recyclerejected records

Message Id Name CityDuplicated Record 001 John Doe New YorkDuplicated Record 022 John Doe BostonInvalid City Reference 230 Albert Fresh Maris



31

Oracle Data IntegratorReal-time Enabled



32

Real-time enabled: Changed Data Capture

• Publish and Subscribe CDC

Framework• Database logs• Triggers• Third-tier solutions

• Ensures “read” transactionintegrity across multipletables

1. Design or generate Mappings

2. Select Journalized Data Only

3. Start Journals

CDC



33

Real-time enabled: Message OrientedIntegration

• Connect to Publish and

Subscribe JMS MessageProviders• Ensure messages delivery

with transaction integrity• High-volume bulk

transformations1. Design complex bulk

transformations mixing Queues,Databases and Applications

2. Use JMS Queues and topics assources or targets

JMS Provider(MOM, ESB)

CDC

Subscribe

Publish



34

Oracle Data IntegratorSOA Enabled



35

SOA Enabled: Data Access Services

• Generate and share data

access services1. Generate and deploy data services2. Test data services3. Leverage data services in your SOA

infrastructure

SOA Infrastructure

Services

Data Access

Transform

Business

BusinessProcesses

ESB



36

SOA Enabled: Data Flow Services

• Expose transformations as

Web Services1. Orchestrate data flows2. Publish data flows as web services

in your SOA infrastructure

SOA Infrastructure

Services

Data Access

Transform

Business

Business

Processes

ESB

Bulk

Transf .



37


Extensible Framework

b l l d d l



38

JournalizeRead from CDC

Source

LoadFrom Sources to

Staging

CheckConstraints before

Load

IntegrateTransform and Move

to Targets

ServiceExpose Data andTransformation

Services

ReverseEngineer Metadata

Reverse

Journalize

Load

Check

IntegrateServices

Pluggable Knowledge Modules Architecture

CDC

Sources

Staging Tables

Error Tables

Target Tables

WS

WS W

S

Extensibility: Knowledge Modules

• 120+ KMs out-of-the-boxTailor to existing best practicesEase administration workReduce cost of ownership

• Customizable andextensible

KMInterpreter

KM’s Meta Code

Metadata

Executed Code

ibili S i i k



39

Extensibility: Scripting Framework

• Extend data flows with

scripting procedures

• Leverage all databaselanguages

• SQL, PL/SQL, Transact SQL, etc.

• Use Operating Systems shellscripts

• Win32 DOS, sh, ksh, csh, OS400commands, JCL, etc.

• Choose from compatible BeanScripting Frameworklanguages

• Java, JavaScript, Jython (JavaPython), Perl, etc.

E ibili O T l



40

Extensibility: Open Tools

• Extend ODI tools

• Add your own tools to theDesign Palette

1. Implement OdiOpenToolAbstract Java Interface

2. Register Open Tool in ODI Designer3. Use Open Tool in your design

environment



41

Popular Usage Scenarios

E LT f D W h



42

E-LT for Data WarehouseCreate Data Warehouse for Business IntelligencePopulate Warehouse with High Performance ODI

Heterogeneous sourcesand targetsIncremental loadSlowly changingdimensionsData integrity andconsistency

Changed data captureData lineage

Data Warehouse

Cube

Cube

Cube

--------

--------

O p er a

t i on

al

An

al y

t i c s

Metadata

LoadTransformCapture Changes

Incremental UpdateData Integrity

AggregateExport

SOA I i i i



43

SOA InitiativeEstablish Messaging Architecture for IntegrationIncorporate Efficient Bulk Data Processing with ODI

Invoke external services fordata integrationDeploy data servicesDeploy transformationservicesIntegrate data andtransformation services inyour SOA infrastructure

Services

Data Access

Transformation

Others

--------

--------

O p er a

t i on

al

Metadata

Generate DataServicesExpose TransformationServices

Deploy and reuseServices

BusinessProcesses

M t D t M t



44

Master Data ManagementCreate Single View of the TruthSynchronize Data with ODI

Use in conjunction withpackaged MDM solutionUse as infrastructure for

designing your own hubCreate declarative data flowsCapture changes (CDC)Reconcile and cleanse thedata

Publish and share master dataExtend metadata definitions

Master Data--------

--------

Metadata

Change DataCaptureMaster Data Load

Canonical FormatDesignCleansing andReconciliation

Master DataPublishing

----

------------

CDCCDC

CDC

Mi ti



45

MigrationUpgrade Applications or Migrate to New SchemaMove Bulk Data Once and Keep in Sync with ODI

Bulk-load historical datato new applicationTransform source formatto targetSynchronize new andold applications duringoverlap timeCapture changes in a bi-directional way (CDC)

Ol d A

p pl i c

a t i on

s

N ew A

p pl i c

a t i on

Metadata

Initial bulk loadCDC forsynchronization

Transformation tonew applicationformat

CDC for loop-backsynchronization

CDC

CDC--------

--------

ODI E h O l BI



46

ODI Enhances Oracle BIPopulate Warehouse with High Performance ODI

Oracle Business IntelligenceSuite EE:Simplified Business Model View

Advanced Calculation &Integration EngineIntelligent Request GenerationOptimized Data Access

Oracle Data Integrator:Populate Enterprise DataWarehouseOptimized Performance for Loadand TransformExtensible Pre-packaged E-LTContent

SiebelCRM

OracleEBSPeopleSoftSAP/R3

OtherSources


E-LTMetadata

E-LT Agent

Oracle BIEnterprise Data

Warehouse

Oracle BI Suite EE

Oracle BI Server

Oracle BI Presentation Server

Answers InteractiveDashboards Publisher Delivers

Bulk E-LT

ODI E h O l SOA S it



47

ODI Enhances Oracle SOA SuiteAdd Bulk Data Transformation to BPEL Process

Oracle SOA Suite:BPEL Process Manager forBusiness ProcessOrchestration

Oracle Data Integrator:Efficient Bulk Data Processingas Part of Business ProcessInteract via Data Services andTransformation Services

Oracle SOA SuiteBusiness Activity

Monitoring

Web ServicesManager

Business RulesEngine

Enterprise ServiceBus

BPEL Process Manager

Bulk DataProcessing


E-LTMetadata

E-LT Agent

ODI Enhances Oracle SOA Suite



48

ODI Enhances Oracle SOA SuitePopulate BAM Active Data Cache Efficiently

Oracle SOA Suite:Business Activity Monitoring for Real-time Insight

Oracle Data Integrator:High Performance Loading ofBAM’s Active Data Cache Pre-built and Integrated

DataWarehouse

Oracle SOA SuiteBPEL Process

Manager

Web ServicesManager

Business RulesEngine

Enterprise ServiceBus

Bulk andReal-Time

Data Processing

SAP/R3PeopleSoft

Message

Queues

CDC

Business Activity Monitoring

Active Data Cache

Event Engine Report Cache

Event Monitoring Web Applications


Metadata Agent



49

Roadmap and Direction



50

Oracle Data Integrator: Roadmap

•Focus Areas for Next Major Release• Deep Integration with Fusion Middleware

• Runtime, Design time, Security, Administration, Events

• Functional Integration with Oracle Warehouse Builder•

Runtime Integration, Metadata Sharing, Knowledge Module Sharing

• Deployment of ODI for Embedded Data Integration• OracleBI Enterprise Edition, Data Hubs, Application Migrations

• Enhanced Usability and Debuggability• Wizards, New Views, User-definable Debugging

• Improved Support for Native Oracle Database Features• Oracle OLAP

f



51

ODI Statement of Direction

•Statement of Direction• http://www.oracle.com/technology/products/odi/statement-of-direction.pdf

• Key Points of Direction• Commitment to heterogeneous systems support

• Including: DB2, Teradata, Netezza, Hyperion, etc.

• Commitment to Fusion design principles• Including: J2EE compliance, container portability

• Commitment to best-of-class E-LT performance• Across platforms, batch & realtime, high complexity

http://www.oracle.com/technology/products/odi/statement-of-direction.pdf






tech oracle data integrator_new1

Documents