<Insert Picture Here>
Oracle Data Integrator – Technical Deck
Mark Pare
Sr. Sales Consultant
Oracle Higher Education
Agenda
•
•
•
•
•
4 Key Differentiators
Architecture
6 Steps to Production
ODI or ESB?
Popular Usage Scenarios
<Insert Picture Here>
3
Oracle Data Integrator
4 Key Differentiators
4
Why Data Integration?
NEED…
Information How and Where you Want It
Business Intelligence
Corporate Performance
Management
Business Process
Management
Business Activity
Monitoring
Data Integration
Migration
Data
Warehousing
Master Data
Management
Data
Synchronization
-----
Federation
Real Time
Messaging
-----
HAVE…
Data in Disparate Sources
---------------
---
Legacy
ERP
---------------
CRM
---------------
Best-of-breed Applications
5
Challenges & Emerging Solutions
In Data Integration
CHALLENGE
EMERGING SOLUTION
1.
Increasing data volumes;
decreasing batch windows
Shift from E-T-L to E-LT
2.
Non-integrated integration
Convergence of integration
solutions
3.
Complexity, manual effort of
conventional ETL design
Shift from custom coding to
declarative design
4.
Lack of knowledge capture
Shift to pattern-driven
development
6
Oracle Data Integrator
•
Data Movement and Transformation from Multiple
Sources to Heterogeneous Targets
BENEFITS
1.
2.
3.
4.
Performance:
Flexibility:
Productivity:
Hot-Pluggable:
KEY DIFFERENTIATED FEATURES
Heterogeneous “E-LT”
Active Integration Platform
Declarative Design
Knowledge Modules
7
1
Differentiator: E-LT Architecture
High Performance
Conventional ETL Architecture
Transform in Separate ETL Server
•
•
•
•
Extract
Transform
Load
Proprietary Engine
Poor Performance
High Costs
IBM & Informatica’s approach
Transform in Existing RDBMS
• Leverage Resources
• Efficient
• High Performance
Next Generation Architecture
“E-LT”
Transform
Benefits
Transform
Extract
Load
 Optimal Performance & Scalability
 Easier to Manage & Lower Cost
8
2
Differentiator: Active Integration
Batch, Event-based, and Service-oriented Integration
Oracle Data Integrator
• Evolve from Batch to Near
Real-time Warehousing on
Common Platform
• Unify the Silos of Data
Integration
• Data Integrity on the Fly
• Services Plug into Oracle
SOA Suite
Event Conductor
Service Conductor
Event-oriented
Integration
Service-oriented
Integration
Metadata
Declarative Design
Data-oriented Integration
Data Conductor
• Benefits
 Enables real-time data warehousing and operational data hubs
 Services plug into Oracle SOA Suite for comprehensive integration
9
3
Differentiator: Declarative Design
Developer Productivity
Specify ETL Data Flow Graph
Conventional ETL Design
• 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]
ODI Declarative Design
1
Define
What
Benefits
 Significantly reduce the learning curve
 Shorter implementation times
 Streamline access to non-IT pros
You Want
2
Automatically
Generate
Dataflow
Define How: Built-in Templates
10
4
Differentiator: Knowledge Modules
Hot-Pluggable: Modular, Flexible, Extensible
Pluggable Knowledge Modules Architecture
Reverse
Engineer Metadata
Journalize
Read from CDC
Source
Load
From Sources to
Staging
Check
Constraints before
Load
Integrate
Transform and Move
to Targets
Service
Expose Data and
Transformation
Services
Reverse
W
W S
S
W
S
Staging Tables
Load
Integrate
CDC
Target Tables
Check
Journalize
Sources
Services
Error Tables
Sample out-of-the-box Knowledge Modules
SAP/R3
Siebel
Log Miner
SQL Server
Triggers
DB2 Journals
Oracle
DBLink
DB2 Exp/Imp
JMS Queues
Oracle
SQL*Loader
Check MS
Excel
Check
Sybase
TPump/
Multiload
Type II SCD
Oracle Merge
Siebel EIM
Schema
Oracle Web
Services
DB2 Web
Services
Benefits
 Tailor to existing best practices
 Ease administration work
 Reduce cost of ownership
11
Oracle Data Integrator
Architecture
12
Architecture: Conceptual View
•
Service Interfaces and Developer APIs
User
Interfaces
Data Flow
Generator
Designer
Operator
• Runs on any platform
• Thin client for browsing
Metadata
Runtime
Design-Time
Knowledge Module
Interpreter
Data Flow
Generator
Runtime
Session
Interpreter
Agent
•
Data Flow
Metadata Management
Master
Repository
Work
Repositories
Runtime
Repositories
Java runtime environment
• Runs on any platform
• Orchestrates the execution of
data flows
Data Flow
Conductor
Thin
Client
Knowledge Modules
Java design-time
environment
•
Metadata repository
• Pluggable on many RDBMS
• Ready for deployment
• Modular and extensible
metadata
13
Architecture: Component View
Development
ODI Design-Time Environment
Development Servers and
Applications
User Interfaces
Topology/Security
Code
Administrators
Design-time
Metadata/Rules Repositories
Execution Log
Execution
Agent
Data Flow
Conductor
Return Codes
CRM
Data
Warehouse
Legacy
ER
P
Designers
ESB
Files /
XML
Scenarios
and Projects
Releases
Production
ODI Runtime Environment
Production Servers and
Applications
User Interfaces
Topology/Security
Administrators
Code
Execution Log
Runtime
Repository
Operators
Execution Log
Execution
Agent
Data Flow
Conductor
Return Codes
CRM
Data
Warehouse
Legacy
ER
P
Thin Client
Metadata Lineage
Data Stewarts
ESB
Metadata
Navigator
Files /
XML
14
Oracle Data Integrator
6 steps to Production
15
Overview: 6 steps to Production
1.
2.
3.
Retrieve/Enrich metadata
Design transformations
Orchestrate data flows
4. Generate/Deploy data flows
5. Monitor executions
6. Analyze impact / data lineage
Development
Production
Development Servers and Applications
Production Servers and Applications
Data
Warehouse
CRM
Data
Warehouse
CRM
Legacy
Legacy
ERP
ERP
ESB
Files / XML
ESB
ODI Design-Time Environment
User Interfaces
Administrators
Designers
Design-time
Design-time
Repositories
Repositories
Files / XML
ODI Runtime Environment
Agent
Data Flow
Conductor
Agent
Data Flow
Conductor
Runtime
Repository
User Interfaces
Operator
Metadata
Navigator
16
1
Retrieve/Enrich Metadata
Development Servers
and Applications
Design-Time Environment
ODI Designer
ERP
Data
Warehouse
Design-time
Repositories
CRM
Files / XML
1. Reverse-engineer Metadata
•
•
•
2. Enrich Metadata
•
•
Legacy
ESB
Automatic
Customizable
40+ technologies supported
•
Documentation
Declarative rules for Data
Integrity
Cross-technologies references
17
2
Design Transformations
1
Define What You Want
3
Automatically Generate
Data flows
Oracle Data Integrator
“Interface”
Declarative Design
2
Define How to Do It: Select Template
Bulk Load • Changed Data Capture • Incremental Update • Slowly Changing Dimension
18
3
Orchestrate Data Flows
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
19
4
Generate and Deploy Data Flows
1. Create Scenarios
• Compile Data Flows for Run-time
2. Version the Data Flows
• Advanced Version Management
Design-time
Repositories
3. Deploy to Production
Scenarios
and Projects
Releases
Runtime
Repository
20
5
Monitor Executions
• View sessions running in realtime
• Review generated code
• Detailed run-time statistics
• Restart failed sessions
21
6
Analyze impact / data lineage
?
•
Maintain a large number of
data flows in a complex
environment
•
Web-based end-to-end data
lineage
1. Understand your data flows
2. Follow the path of data
3. Drill-down to transformations
22
Oracle Data Integrator
ODI or ESB?
23
What tool is best suited for task X?
Requirement
ES ODI Recommende
B
d
Latency / Volume
Synchronous Integration
Asynchronous Integration with routing and transformation


Asynchronous Integration for Active Data Warehousing (mini-batch)
Batch Integration with High Volume
ESB
ESB


ODI

ESB
ODI
Transformations
In-memory XSLT Transformations (XML to XML)
Transformations in App Server
Transformations in Database (E-LT)



ESB

ODI

ODI
Integration Topology
Data Warehouse Loading (E-LT)
JMS to JMS
JMS to DB/App with routing and transformation (real-time or synchronous)


ESB


JMS to DB/App with bulk transformation (mini-batch)
DB/App to DB/App (batch or mini-batch with CDC)
DB/App to DB/App (synchronous or real-time with CDC Adapters)
ESB

ODI
ODI
ESB
…
24
ESB and ODI in real-life scenarios
Data
Latency
Batch
(over 2 hours)
Oracle Data Integrator
Asynchronous
Oracle Enterprise Service Bus
Synchronous
(immediate)
Message by
Message
Mini Batches
Large Volume
(over 1M)
Data Volume
Processing
25
Oracle Data Integrator
Extended Capabilities
26
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
27
Oracle Data Integrator
Master Data Management Enabled
28
MDM Enabled: Canonical Format Design
•
•
Master Data
Use in conjunction with
packaged MDM solution
Design and Populate
Canonical Format
1. Use existing metadata artifacts
to design MDM application
(entities, fields, relationships)
2. Generate and maintain Master
Data structure
3. Generate and deploy
transformations using metadata
artifacts
Enterprise Service Bus
CRM
SCM
Legacy
ERP
29
MDM Enabled: Built-in Data Integrity
Message
Duplicated Record
Duplicated Record
Invalid City Reference
Id
001
022
230
Name
John Doe
John Doe
Albert Fresh
City
New York
Boston
Maris
•
•
Data Integrity Firewall
Auditing, cleansing and
recycling
1.
Declare constraints at table
level
Design mappings and check
flow integrity
Audit, cleanse or recycle
rejected records
2.
3.
30
Oracle Data Integrator
Real-time Enabled
31
Real-time enabled: Changed Data Capture
•
Publish and Subscribe CDC
Framework
•
•
•
CDC
•
Database logs
Triggers
Third-tier solutions
Ensures “read” transaction
integrity across multiple
tables
1. Design or generate Mappings
2. Select Journalized Data Only
3. Start Journals
32
Real-time enabled: Message Oriented
Integration
•
Subscribe
CDC
JMS Provider
(MOM, ESB)
•
Publish
•
Connect to Publish and
Subscribe JMS Message
Providers
Ensure messages delivery
with transaction integrity
High-volume bulk
transformations
1. Design complex bulk
transformations mixing Queues,
Databases and Applications
2. Use JMS Queues and topics as
sources or targets
33
Oracle Data Integrator
SOA Enabled
34
SOA Enabled: Data Access Services
SOA Infrastructure
Business
Processes
Services
Data Access
Transform
ESB
Business
•
Generate and share data
access services
1.
2.
3.
Generate and deploy data services
Test data services
Leverage data services in your SOA
infrastructure
35
SOA Enabled: Data Flow Services
•
SOA Infrastructure
Bulk
Transf
.
Business
Processes
Services
Expose transformations as
Web Services
1.
2.
Data Access
Transform
ESB
Orchestrate data flows
Publish data flows as web services
in your SOA infrastructure
Business
36
Oracle Data Integrator
Extensible Framework
37
Extensibility: Knowledge Modules
KM’s Meta Code
• 120+ KMs out-of-the-box
 Tailor to existing best practices
 Ease administration work
 Reduce cost of ownership
Executed Code
KM
Interpreter
Metadata
• Customizable and
extensible
Pluggable Knowledge Modules Architecture
Reverse
Engineer Metadata
Journalize
Read from CDC
Source
Load
From Sources to
Staging
Check
Constraints before
Load
Integrate
Transform and Move
to Targets
Service
Expose Data and
Transformation
Services
Reverse
W
W S
S
W
S
Staging Tables
Load
CDC
Sources
Journalize
Integrate
Services
Target Tables
Check
Error Tables
38
Extensibility: Scripting Framework
•
Extend data flows with
scripting procedures
•
Leverage all database
languages
• SQL, PL/SQL, Transact SQL, etc.
•
Use Operating Systems shell
scripts
• Win32 DOS, sh, ksh, csh, OS400
commands, JCL, etc.
•
Choose from compatible Bean
Scripting Framework
languages
• Java, JavaScript, Jython (Java
Python), Perl, etc.
39
Extensibility: Open Tools
• Extend ODI tools
• Add your own tools to the
Design Palette
1. Implement OdiOpenToolAbstract
Java Interface
2. Register Open Tool in ODI Designer
3. Use Open Tool in your design
environment
40
Popular Usage Scenarios
41
E-LT for Data Warehouse
Create Data Warehouse for Business Intelligence
Populate Warehouse with High Performance ODI
Load
Transform
Capture Changes
Incremental Update
Data Integrity
Aggregate
Export
Cube
Data Warehouse
Cube
Cube
Analytics
Operational
-------------
 Heterogeneous sources
and targets
 Incremental load
 Slowly changing
dimensions
 Data integrity and
consistency
 Changed data capture
 Data lineage
Metadata
42
SOA Initiative
Establish Messaging Architecture for Integration
Incorporate Efficient Bulk Data Processing with ODI
Generate Data
Services
Expose Transformation
Services
Deploy and reuse
Services
Services
Operational
Data Access
-------------
Transformation
Others
Business
Processes
 Invoke external services for
data integration
 Deploy data services
 Deploy transformation
services
 Integrate data and
transformation services in
your SOA infrastructure
Metadata
43
Master Data Management
Create Single View of the Truth
Synchronize Data with ODI
Change Data
Capture
Master Data Load
Canonical Format
Design
Cleansing and
Reconciliation
Master Data
Publishing
CDC
CDC
-------------
Master Data
CDC
-------------
Metadata
 Use in conjunction with
packaged MDM solution
 Use as infrastructure for
designing your own hub
 Create declarative data flows
 Capture changes (CDC)
 Reconcile and cleanse the
data
 Publish and share master data
 Extend metadata definitions
44
Migration
Upgrade Applications or Migrate to New Schema
Move Bulk Data Once and Keep in Sync with ODI
Initial bulk load
CDC for
synchronization
Transformation to
new application
format
CDC for loopback
synchronization
-------------
CDC
New Application
Old Applications
CDC
 Bulk-load historical data
to new application
 Transform source format
to target
 Synchronize new and
old applications during
overlap time
 Capture changes in a bidirectional way (CDC)
Metadata
45
ODI Enhances Oracle BI
Populate Warehouse with High Performance ODI
Oracle Business Intelligence
Suite EE:
Oracle BI Suite EE
Answers
Interactive
Dashboards
Publisher
Delivers
 Simplified Business Model View
 Advanced Calculation &
Integration Engine
 Intelligent Request Generation
 Optimized Data Access
Oracle BI Presentation Server
Oracle BI Server
Oracle BI
Enterprise Data
Warehouse
Oracle Data Integrator:
 Populate Enterprise Data
Warehouse
 Optimized Performance for Load
and Transform
 Extensible Pre-packaged E-LT
Content
Bulk E-LT
Oracle Data Integrator
E-LT Agent
Other
Sources
SAP/R3
E-LT
Metadata
PeopleSoft
Oracle
EBS
Siebel
CRM
46
ODI Enhances Oracle SOA Suite
Add Bulk Data Transformation to BPEL Process
Oracle SOA Suite:
Oracle SOA Suite
Business Activity
Monitoring
BPEL Process Manager
Web Services
Manager
Business Rules
Engine
 BPEL Process Manager for
Business Process
Orchestration
Enterprise Service
Bus
Oracle Data Integrator
E-LT Agent
E-LT
Metadata
Oracle Data Integrator:
 Efficient Bulk Data Processing
as Part of Business Process
 Interact via Data Services and
Transformation Services
Bulk Data
Processing
47
ODI Enhances Oracle SOA Suite
Populate BAM Active Data Cache Efficiently
Oracle SOA Suite:
Oracle SOA Suite
BPEL Process
Manager
Business Activity Monitoring
Event Monitoring Web Applications
Event Engine
Web Services
Manager
Report Cache
 Business Activity Monitoring
for Real-time Insight
Business Rules
Engine
Active Data Cache
Enterprise Service
Bus
Oracle Data Integrator:
Oracle Data Integrator
Bulk and
Real-Time
Data Processing
Agent
CDC
Data
Warehouse
PeopleSoft
Metadata
 High Performance Loading of
BAM’s Active Data Cache
 Pre-built and Integrated
Me
ss
ag
e
Qu
eu
es
SAP/R3
48
Roadmap and Direction
49
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
50
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
51