Oracle Warehouse Builder: Overview

1
(No Transcript)
2
Oracle Warehouse Builder Overview

• Jean-Pierre Dijcks and Michelle Bird
• Warehouse Builder Product Management

3
Agenda
ltInsert Picture Heregt

• Integrated Solution
• Market Position
• Roadmap
• Merging the ODI and OWB products
• Common pricing bundle
• Product Overview
• New Features in OWB 11.1
• Preview of OWB 11.2
• Pricing and Packaging
• For More Information ...

4
ltInsert Picture Heregt
Integrated Solution
5
Distributed Data Marts and ServersExpensive data
warehouse architecture
6
Typical ETLMultiple tools require separate
training, install, maintenance
7
Consolidated Data WarehouseSingle source of
truth on low cost servers storage
Oracle Database 11g with integrated ETL,
Analytics Data Mining
8
OWB Integrated in the Oracle Database

• Centralized metadata repository
• hosted on Oracle Database ? secure, scalable
• import metadata
• manage propagate metadata changes

9
Oracle Warehouse BuilderOWB is optimized for
Oracle environments

• Benefit from your Oracle investments
• Better performance through Oracle optimizations
• Skills, people and technology
• Improved productivity
• Use the latest Oracle features without rewriting
  your code
• Advanced task automation features
• Shorten your project times while improving data
  quality
• Discover problems up-front with data profiling
• Improve data quality in a declarative way

10
In-Database ETL Higher Productivity
Oracle In-Database ETL
In-house ETL
Tune and Administer
Test and Update
CreateData QualityRoutines
Tune and Administer
Test and Update
Create Data Transformations
Create DQ Routines
Create Data Transformations
Months to weeks
Years to Months
11
Advanced features made easy
12
ltInsert Picture Heregt
Mark Rittman Co-founder, Rittman Mead Consulting
"Rittman Mead recommends Oracle Warehouse
Builder because it is the most effective way to
leverage advanced capabilities of the Oracle
Database for ETL development, while also
providing metadata management and data quality
features required for full life-cycle
deployments.
13
ltInsert Picture Heregt
Market Position
14
Market Position Recognized as a leader since OWB
10.2

• 10,000 companies rely on OWB
• Optimized for Oracle environments

2005
15
ltInsert Picture Heregt
Roadmap
16
Product Highlights An abbreviated history of OWB
2000
Release 2.0.4
Basic ETL functionality
Mapping Editor
3,000 companies Compete against Informatica
and Ascential
Set and row based code generation
Recognized as a leader in Gartner Magic Quadrant
Recognized as a leader in Forrester Enterprise
ETLWave
17
High-Level Roadmap Unified platform that is a
superset of OWB and ODI

• OWB/ODI investments are fully protected
• No forced migrations
• Natural upgrade path
• No regressions in functionality
• Training and support to continue for both
  product releases

10gR2
11gR1
10gR1
11gR2
OWB
ODI
11gR2
11gR1
10gR3
18
Common PricingIncludes ODI product and OWB
Enterprise ETL option
DATA INTEGRATOR
ENTERPRISE EDITION
WAREHOUSE BUILDER
DATA INTEGRATOR
ENTERPRISE ETL
OWB EETL
ODI
Flagship Data
Flagship
Integration Product
ETL Product
Heterogeneous, High-Performance Data Integration
Extensible Metadata Management
Wizards for Slowly Changing Dimensions
Modern Java/SOA Architecture
Interactive Lineage and Impact Analysis
Flexible Knowledge Module framework
19
Oracles Roadmap for its ETL tools

• Long Term Direction Deliver an integrated data
  integration product
• Short TermDeliver stand-alone releases with
  first integration steps
• Shared knowledge module framework (OWB 11.2)
• Runtime interoperability (future releases)
• Same user interface framework (future releases)
• Protect investments for the entire customer base

http//www.oracle.com/technology/products/warehous
e/pdf/owb_roadmap.pdf
20
ltInsert Picture Heregt
Product Details
21
Oracle Warehouse BuilderThe ETL and DQ tool
shipped with Oracle Database

• Oracle Database as transformation engine
• Oracle Database as metadata repository

Oracle Database 11g
Oracle Warehouse Builder 11.1
HeterogeneousData Integration
In-lineData Quality
SmartData Modeling for Oracle
Unified Metadata
Unified Metadata
22
OWB 11.1Oracle-centric data integration platform
BI Tool Integration
Non-Oracle BI design leverage Oracle objects

Oracle Database 11g
Oracle Warehouse Builder 11.1
HeterogeneousData Integration
In-lineData Quality
SmartData Modeling for Oracle
Unified Metadata
Unified Metadata
23
Architecture
24
Sources Targets
Data Targets
Data Sources

• Oracle
• Tables, Views, MViews, Queues, External Tables,
  Sqlloader, Transportable Tablespaces, Data Pump
• DB2, Sybase, SQLServer, Informix, Mainframes,
  (Oracle Transparent Gateways)
• ODBC
• Flat Files
• XML
• Applications
• Oracle eBusiness Suite
• PeopleSoft
• SAP R/3
• Siebel
• Oracle MDM (UCM, CDH, PIM)

• Oracle
• DB2, Sybase, SQLServer, Informix, Mainframes,
  (Oracle Transparent Gateways)
• ODBC
• Flat Files
• XML
• Applications
• Oracle eBusiness Suite
• PeopleSoft
• Siebel and UCM

OWB can target many data stores. However, its
metadata repository and therefore DWH target is
Oracle Database.
25
ETL Mappings

• Declarative modeling of Data Flows
• Map from Source to Target
• Integrated Data Quality
• NA standardization
• Match/Merge
• Profiling
• Generates SQL PL/SQL
• Merge, transportable tablespaces, data pump,
  sqlloader, xml data types, BLOBS/CLOBS,
• Leverage custom data transformations
• Pluggable maps for reusability of logic

26
ETL Process flows

• Declarative modeling of Process/work Flows
• Co-ordinate execution of Maps and other
  activities
• Create complex transitions
• Send email, FTP source/target files, call any
  external process, SQL Plus, Notifications
• Generates Oracle Workflow, Oracle Scheduler XPDL

27
Data Quality in OWB

• Data Quality functionalities are integrated into
  ETL processes
• Disciplined approach to Data Quality, not an
  afterthought
• Data Quality is modeled, executed and audited
  just like any other transformation
• Consists of
• Data Profiling
• Name and Address Cleansing
• Match-Merge

28
Data Profiling
29
Data Cleansing

• Parsing
• Standardization
• Correction
• Augmentation

30
Match-Merge

• Matching is the process of determining, through
  business rules, which records refer to the same
  logical data
• Merging is the business rules driven
  consolidation of the data from the matched set
  into a single record
• Supports multiple algorithms
• Uses of Match-Merge are
• De-duplication
• House-holding
• Record Linking

31
Integrated Data Object Design

• One editor for
• Creation of objects
• Configuration for multiple databases
• Validation
• DDL Code generation
• Deployment to multiple databases
• Data viewing

32
Integrated Data Object Design

• One editor for
• Relational modeling
• Tables, views, materialized views
• All storage and database properties
• Dimensional modeling
• Star and snow flake schemas
• Multi-dimensional (OLAP) structures for Oracle
  OLAP
• Support for Skip-Level, calculated measures,
  value based hierarchies
• Slowly changing dimensions (including hierarchy
  versioning)
• Support for business definitions
• Oracle BI SE

33
Data Object Editor
34
Metadata Services

• Dependency Management
• Data Lineage at attribute level
• Impact Analysis at attribute level
• Metadata Snapshots
• Change Management (diff, merge and reconcile)
• Security (roles)
• Multi-language Support (MLS via XLIFF)
• Extensibility - user defined properties, objects,
  and associations
• Reporting (browser)
• APIs (Scripting, SQL, PL/SQL)
• Exchange (import/export)

35
Impact Analysis
36
Runtime Management

• Both client and web based administration GUI
• Smart deployment
• Start, stop, expedite jobs from a web client
• Extensive auditing for
• Deployments
• Executions
• Error scenarios
• Full scripting support
• Full public view support

37
User-Defined Objects Icons

• User-Definable
• Objects
• Associations
• Properties
• ALL in the OWB repository!
• Custom icons for easy recognition
• Access full metadata services of OWB e.g. impact
  analysis

38
Experts

• Re-use OWB Components to build your OWN
  application!
• Define best practices directed guidance
• Run Standalone or within OWB Design-time
• Declarative Definition Guided execution

39
ltInsert Picture Heregt
New Features in OWB 11.1
40
Some New Features for OWB 11.1

• New Connectivity Options
• 11g Database support
• Siebel Connector
• Gateway 11g support
• Performance Features
• Embedded explain plan support
• Embedded statistics gathering
• Embedded SQL/View tuning advisor
• Simplified Installation
• Embedded in the 11g database install
• Includes Oracle Workflow in OWB install

41
Some New Features for OWB 11.1 (cont.)

• Enhanced SQL Generation
• Advanced aggregation support
• DML error logging in mappings
• More Dimensional Support
• Hierarchy versioning (supports multiple
  hierarchies)
• Advanced binding options
• Better support for federation
• Graphical creation of views/MVs
• Full impact analysis and lineage for federated
  structures

42
ltInsert Picture Heregt
Preview of OWB 11.2
43
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
Oracles products remains at the sole discretion
of Oracle.
44
OWB 11gR2Plays well with others
BI Tool Integration
OBI EE (Enterprise Edition) design deploy
UDML
Discoverer (Standard Edition) design deploy
EUL
Databases
Databases
Non-Oracle BI design leverage Oracle objects

Oracle Database 11g
Applications
Applications
Connectivity
Oracle Warehouse Builder 11.2
Connectivity
Odbc / Gateways
Odbc / Gateways
HeterogeneousData Integration
In-lineData Quality
SmartData Modeling for Oracle
Files
Files
Connectors
Connectors
CDC Queues / Streams/ Third Party
CDC Queues / Streams/ Third Party
Messages
Messages
SQLLoader/ Ex Table
JDBC
Knowledge Module Framework
Analytics
Analytics
Native Loaders
Native Un-Loaders
Unified Metadata
Unified Metadata
Semi-Structured
Semi-Structured
Service Oriented Architectures
Web Services
Web Services
Web Services for data integration and data quality
45
Leveraging the Oracle DatabaseNew Database
Features exposed in OWB 11.2

• Advanced Compression
• Virtual columns
• New partitioning methods
• Data Pump
• Cube MVs for summaries
• And more

Easy upgrade to OWB 11.2

• Import existing OWB design to OWB 11.2
• Select new feature e.g., Cube MVs
• Regenerate code

46
Integrating with OBI EEHow its done in OWB 11.2

• In OWB, derive the model from the dimensional
  model

• In OWB, generate the metadata for all 3 layers of
  OBI EE

• In Oracle Answers, browse the data

47
Flexible access to non-Oracle sourcesEnabled by
knowledge module framework

• Knowledge modules are snippets of code you can
  run, compile code in non-Oracle environments
• Choose from a library of existing knowledge
  modules (KMs)
• Write your own or edit existing KMs
• Originally developed by Sunopsis, now Oracle Data
  Integrator (ODI)

OWB knowledge modules 1st big step in
merging OWB and ODI
48
Knowledge Modules for flexible access Limited
only by the code you write or find in the library

• Access any new source type relational,
  non-relational, message based and content sources
• Rich support of ERP, CRM and MDM on any database
  platform
• Rich library of Knowledge Modules

49
Viewing and Editing the KM code Increased
Flexibility

• Modify template
• Regenerate the code
• Reuse the template

50
Using KMs in mappingsEasily change the
connectivity methods for a single logical design

• Complete the logical design (or import an
  existing mapping)

Logical view
Execution view

• Define execution units for each type of source
  ( 3 shown here)

• Select the best connectivity method for each
  execution unit (connectivity can be through ODBC,
  Gateways, Knowledge Modules, etc.)

51
ltInsert Picture Heregt
Pricing and Packaging
52
In-Database ETLIncluded in many popular Oracle
licenses

• Database SE, SE1, EE, Oracle Database Machine
• Business Intelligence SE, SE1
• e-Business Suite Intelligence products Oracle HR
  Intelligence, Oracle Financial Data Manager

53
Warehouse Builder Packaging Options rely on
In-Database ETL
Enterprise ETL/ ODI EE
Data Profiling Quality
Create new metadata types Promote from
DevgtQAgtPROD Navigate lineage impact Propagate
metadata changes Schedule jobs using UI Advanced
process flow logic Create nested mappings Slowly
changing dimensions Design in RAC
environment More

• Profile tables, files, applications
• Detect or create data rules
• Audit incoming data
• Correct data

In-Database ETL

Design ETL mappings View HTML Metadata
reports Perform data cleansing Use GUI or
scripting language More
Application Adapters
Data Watch Repair

• Access to data and metadata in
• SAP R/3
• eBusiness Suite
• Peoplesoft
• Siebel

• Integrate with Oracle MDM
• Siebel Universal Customer Master
• Product Information Management Data Hub
• Customer Data Hub

Options licensed independently of each other,
except for Data Watch Repair which requires
Data Profiling Quality.
54
Warehouse Builder Packaging Options rely on
In-Database ETL
Anticipated for OWB 11.2 (subject to change)
Enterprise ETL/ ODI EE
Data Profiling Quality
Create new metadata types Promote from
DevgtQAgtPROD Navigate lineage impact Propagate
metadata changes Schedule jobs using UI Advanced
process flow logic Create nested mappings Slowly
changing dimensions Design in RAC
environment Enhanced SOA support Flexible access
to non-Oracle sources Orphan Management More

• Profile tables, files, applications
• Detect or create data rules
• Audit incoming data
• Correct data

In-Database ETL

Design ETL mappings View HTML Metadata
reports Perform data cleansing Use GUI or
scripting language New improved GUI Integration
with OBI EE More
Application Adapters
Data Watch Repair

• Access to data and metadata in
• SAP R/3
• eBusiness Suite
• Peoplesoft
• Siebel

• Integrate with Oracle MDM
• Siebel Universal Customer Master
• Product Information Management Data Hub
• Customer Data Hub

Options licensed independently of each other,
except for Data Watch Repair which requires
Data Profiling Quality.
55
Common PricingIncludes ODI product and OWB
Enterprise ETL option
DATA INTEGRATOR
ENTERPRISE EDITION

• One license at 23k/cpu
• ODI and OWB EETL option are removed from
  pricelist
• Both are only available in ODI EE
• Two separate installations
• Implement one or both

WAREHOUSE BUILDER
DATA INTEGRATOR
ENTERPRISE ETL
OWB EETL
ODI
Flagship Data
Flagship
Integration Product
ETL Product
Heterogeneous, High-Performance Data Integration
Extensible Metadata Management
Wizards for Slowly Changing Dimensions
Modern Java/SOA Architecture
Interactive Lineage and Impact Analysis
Flexible Knowledge Module framework
56
OWB Product OfferingsThis pricing applies to OWB
10.2 and 11.1 releases
APPLICATION ADAPTERS
ORACLE DATABASE
DATA PROFILING QUALITY
FOR WAREHOUSE BUILDER
2,300/cpu
DBEE17.5k/cpu
23k/cpu
OWB EETL in
OWB EETL
ODPQ
DB
ADAPT
ODI EE
Data quality
Extensible
Reveals
Extensible
Mapping Editor Debugger
algorithms run in Oracle Database
Metadata Framework
business views of application data
Metadata Framework
Profile file, table, application data in place or
stage the data.
Out of the box support for Slowly Changing
Dimensions
Basic dependency management with Process Flows
Natively access SAP R/3 hosted on any database
OWB in
Read from and write to eBusiness Suite,
PeopleSoft, or Siebel
Auto- detect data rules. Audit, auto-correct
incoming data.
Interactive Lineage and Impact Analysis
Robust scripting language APIs

Fuzzy match/ merge and NameAddress cleansing
DATA WATCH REPAIR
ODPQ23k
DWR
Data quality
for Oracle MDM Siebel UCM, PIM, CDH
57
ltInsert Picture Heregt
For More Information ...
58
For More Information
search.oracle.com
Warehouse Builder
or oracle.com/technology/products/warehouse/index.
html
see Training?Oracle By Example go to
Product Discussion Forum to contact us
59
(No Transcript)
60
(No Transcript)