Transcript Oracle Warehouse Builder - An Overview

Oracle Business Intelligence &
Data Warehousing Overview
Sankar Bala
DW/BI Specialist
[email protected]
469.583.0261
What is intelligent
data?
Business Analytics
• Automated management reporting
Process Controls and Data Integrity
Information Value
• KPIs
Information
Made
Powerful
• Driver based forecasting
• Near real-time view of business
• Actionable projections
• Performance Management / measurable
accountability to results
Data Collection
Enterprise Data Warehouse
• Standardize business logic and rules
• Process controls & data integrity
Operations
Data
Finance
Data
Sales/Inv
Data
Data Made
Meaningful
• Single source of data
• Official source for financial, sales,
operational and management
reporting
Source Systems
• Legacy systems with a long heritage
Raw Data
• Mission critical applications (ERP,
Billing, Merchandising, etc…)
• Data is made meaningful when there are standardized definitions, process controls and a focus on data integrity
• Information is made powerful when this transformed data is used by management to improve business efficiency
Road to Success
Realizing your corporate Strategy will require the integration of
people, process and technology
Legacy
Environment
Business Strategy
People
New BI
Solution
Processes
Technology
Success criteria:
• Business Strategy
•Improve Top Line Sales
•Improve Margin
•Increase customer service
•Increase accountability
• People
•Actionable Information
•Accessibility of Information
•Varying skill levels
• Processes
•Data Quality
•Standard Business Language
•Standard Metrics & KPI’s
• Technology
•Scalability
•Stability
•Flexibility
•Mainstream
Typical BI Environment
Oracle Warehouse Builder
Informatica PowerCenter
Ab Initio Co-Op
IBM Ascential
Oracle OLAP
Hyperion EssBase
Cognos PowerPlay
MS OLAP
OLAP
Engine
ETL Tool
Lineage
Analytic Apps
Transformation
Engine
ETLTool
Oracle Balance Scorecard
Hyperion Scorecard
Cognos Metrics Mgr
Mining
Engine
Query &
Analysis
Database
Transformation
Engine
Reporting
Engine
Oracle 10g
IBM DB2
Enterprise
NCR Teradata
Reporting
MS SQL Server
Oracle BI Platform / XML Publisher
Oracle Data Miner
Business Objects XI
SAS Enterprise Miner
Cognos 8 BI
SPSS Clementine
MicroStrategy 8
Hyperion 9 BI +
Name/Address
Scrubbing
Oracle EPB
Hyperion Planning
Cognos Planning
Business Objects SRC
P
o
r
t
a
l
Oracle Portal
BEA WebLogic
IBM WebSphere
MS Share Point
Typical BI Environment










RDBMS
ETL
Cleansing
Lineage
OLAP
Data Mining
Query & Reporting
Enterprise Reporting
Analytical Applications
Portal
What About ?









GIS
Collaboration
Unstructured Data
Enterprise Security
Identity Management
Application Integration
Metadata Management
Business Activity Monitoring
Business Process Flow Management
Only ORACLE Can !










RDBMS
ETL
Cleansing
Lineage
OLAP
Data Mining
Query & Reporting
Enterprise Reporting
Analytical Applications
Portal









GIS
Collaboration
Unstructured Data
Enterprise Security
Identity Management
Application Integration
Metadata Management
Business Activity Monitoring
Business Process Flow
Management
Simplify
OLAP
Engine
• Eliminate redundancy
ETL Tool
Lineage
Analytic Apps
Transformation
Engine
Mining
Engine
Query &
Analysis
P
o
r
t
a
l
• Reduce number of moving parts
ETLTool
Database
Transformation
Engine
Name/Address
Scrubbing
Reporting
Engine
Enterprise
Reporting
• Develop “best practice” architecture
Simplify
OLAP
Engine
ETL Tool
Analytic Apps
Transformation
Engine
ETLTool
Lineage
Mining
Engine
Query &
Analysis
Database
Transformation
Engine
Name/Address
Scrubbing
Reporting
Engine
P
o
r
t
a
l
Enterprise
Reporting
• Metadata Mgmt • Enterprise Security • Collaboration • Application Integration
• GIS • Identity Mgmt • Business Process Flow Mgmt • Unstructured Data
DW/BI Products
Oracle Business
Intelligence SE-1
Oracle Business
Intelligence SE
Oracle Business
Intelligence EE
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
SE1 Database
ETL
Analytic Server
Query & Analysis
Dashboards
Server Administration
Targeted to SME
Customers
Discoverer
Discoverer OLAP
Discoverer Plus
Discoverer Viewer
Reports
BI Beans
MSFT Office Add-In
Oracle Database
ETL, OLAP, Data Mining
Analytic Server
Query & Analysis
Dashboards
Reporting & Publishing
Sense & Respond
Offline Analytics
MSFT Office Add-In
Server Administration
Oracle10g Warehouse
Builder
What is Oracle Warehouse
Builder?
 Enables the extraction, transformation, and
loading of data to produce quality information
in the Oracle database
 Protects and leverages customer investment
in the Oracle technology stack through data
and metadata integration
Gartner ETL Magic Quadrant, 1H05
Disclaimer: The Magic Quadrant is copyrighted 11 May 2005 by Gartner, Inc. and is reused with permission. The Magic Quadrant is a graphical
representation of a marketplace at and for a specific time period. It depicts Gartner's analysis of how certain vendors measure against criteria
for that marketplace, as defined by Gartner. Gartner does not endorse any vendor, product or service depicted in the Magic Quadrant, and does not
advise technology users to select only those vendors placed in the "Leaders" quadrant. The Magic Quadrant is intended solely as a research tool,
and is not meant to be a specific guide to action. Gartner disclaims all warranties, express or implied, with respect to this research, including
any warranties of merchantability or fitness for a particular purpose.
Magic Quadrant for Extraction, Transformation and Loading, 1H05, T. Friedman, B.Gassman, May 2005
Sample Customers
Design and Deployment With
Oracle Warehouse Builder
1
Design Target Objects
3
Map & Transform
4
2
•
•
•
•
5
Extract Source
Metadata
Relational
Flat Files
Applications
Mainframe
Generate & Validate
Deploy Code
•
•
•
•
6
Run
Oracle9i/10g
OLAP
Flat file
BI tools
10gR2 themes
 Enabling Quality Information
 Enabling Business Intelligence
 Enabling Expertise capture
Enabling Quality Information
Quality
Monitoring
Information
Profile
Data
Execute
Deploy
Quality
Transformation
Quality
Assessment
Capture
Metadata
Metadata
Management
Process
Flow
Data
Flow
Data
Rules
Quality
Design
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
Data Profiling
 Allow users to analyze data
– discover the structural content
– capture the semantics
–




Identify anomalies and outliers
Automatically derive business rules and maps to clean data
Derive Quality Indices (e.g. 6-sigma)
Auditors monitor quality on an ongoing basis
Integrated as part of ETL
Columns Pattern
Hdate
SSN
Phone
Id
Contact
YYYY-MM-DD
9(3)-9(2)-9(4)
9(3)-9(3)-9(4)
9(5)
[email protected]
Format
Date
US Social Security Number
US Phone Number
<Not Found>
Email
Format % Unique
Compliant
82%
3%
16%
N/A Yes
99% No
Data Profiling
Enabling Business Intelligence
Design
Derivation
BI Defs
Preview
Metadata
Management
Deploy
Generation
Enabling Business Intelligence –
New in 10gR2
 Relational and Dimensional Data Object
Designer
 Business Intelligence Object Derivation
Data Object Design
One Editor
 Dimensions, cubes, tables, views, complex objects, …
 Support for Star, Snowflake, Skip-Level, calculated
measures, …
 One editor for creation, configuration, validation, code
generation, impact analysis, deployment, data viewing
Data Object Editor
Business Intelligence Object
Derivation
 Create and Derive Business
intelligence objects
 Oracle OLAP Cubes &
Dimensions
 OracleBI Discoverer
EUL
 OracleBI Beans Reports
 Included in Lineage and
Impact analysis!
Designer
Relational & OLAP
Catalogs,
10g Scheduler, Workflow
z
Warehouse
Builder
Discoverer
BI Beans
Enabling Expertise Capture
Expertise
BI Defs
Enabling
Business
Intelligence
Information
Enabling
Quality
Information
Enabling Expertise capture –
New in 10gR2
 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
 E.g. “Data-Mover” Expert
–
Offers users the ability to easily copy data from:
 Table-to-Table
 File-to-Table
Expert Editor
Sources & Targets
Targets
Sources





Oracle
–
Tables, Views, MViews,
Queues, External Tables, Table
Functions, Streams, PL/SQL
API’s, Sqlloader…
DB2, Sybase, SQLServer, Informix,
Mainframes, … (Oracle Transparent
Gateways)
ODBC
Flat Files
Applications
–
Oracle Apps
–
Peoplesoft
–
SAP
–
Custom SQL App

Oracle
Tables, Queues, Table Functions,
Streams, PL/SQL API’s, …
DB2, Sybase, SQLServer, Informix,
Mainframes, … (Oracle Transparent
Gateways)
ODBC
Flat Files
Applications
–
Oracle Apps
–
Peoplesoft
–
Custom SQL App
–




Enhanced ERP Integration
 Metadata adapters for:
–
–
Oracle EBusiness
Suite
Peoplesoft
 Enhancements to SAP
adapter:
–
–
–
–
Direct ABAP
processing
ABAP Join Ranking
Variable support
Sub-Process flow
integration
Transportable Modules
 Move large data volumes quickly and easily between Oracle
instances
– Perform fast bulk loads
– Performance & usability gains over traditional
technologies (DB Links, Flat Files,…)
– Technology varies according to source:
 9i source - Transportable Tablespaces
 10g source – Data Pump
Scheduled Process Execution
 Define complex custom calendars to control
process execution
–
E.g.
 Working Days
 Holidays
 Standard iCal format
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
Impact Analysis on UDO’s
New Segmentation of OWB
Functionality
Enterprise ETL
Data Quality
Enable Large Scale,
Complex Deployments
Convert Data to
Quality Information
on an ongoing basis
ERP/CRM
Connectivity
Extract from common
ERP/CRM (SAP etc.)
Core ETL Features
Easily load Data into Oracle Database
What are the Core ETL Features?
OWB 10gR2 Core ETL =
OWB 10gR1 Functionality
– SAP connector
+ Enhancements
+ New Features
Core ETL Features
Easily load Data into Oracle Database
Core ETL Functionality








Import Metadata. source Non-Oracle and Flat-files
Target Oracle (relational & Dimensional) and Flat-Files
Support for Oracle OLAP and Relational Partitioning
Debugging, Mapping (Aggregation, De-duplication, Joining,
etc.
Name & Address Cleansing Interface to Partners
Basic Process Flows
Execution on RAC/Grid
HTML metadata reports, Scripting and SQL views
Core ETL New Features
 Experts
 Non-Oracle Target support
 Graphical Data Object Editor
–







Relational, Dimensional, EUL
Remote Control Center (Runtime Service)
Data Viewers (Relational & Dimensional) in maps
Fiscal Time Dimension incl. Data loaders
Integrated OMB*Plus Client
Asynchronous Job Deployment & execution
User-defined data types
Runtime metadata transformations
Core ETL New Features
 Usability
–
–
–
–
–
–
–
–
–
–
New Common Editor Framework
Modules as source &/or target
Security Management
Printing
Location Management
Install
Simplified/unified Repository
“Right-mouse-click” Deployment
Unified Browser Reporting environment
BI Definition Derivation
Core ETL Enhancements
UI Look & Feel Improvements
10g Release 1
10g Release 2
Core ETL Enhancements
 Dimensional
 Calculated Measure Generation
 Advanced Cube Aggregation
- MV’s for ROLAP, AGGMAPS for MOLAP
 Oracle OLAP 10g R2 via AWXML
- Sparsity
- Compressed Cube
- Partitioning
 Dimension Roles
 Dimension Import
 Surrogate Key Management
 Auto and manual binding for relational dimensions
Core ETL Enhancements
 Data Quality
 Improved Match/Merge functionality
 Parallelized Name & Address cleansing
 Other
 Advanced Aggregation Options
 Physical Storage Data Object properties
 Scripting support for advanced deployment
(upgrade, replace)
 Materialized views
 Multi-File support for SQL*Loader
 Forced ‘Single Flavor Codegen’ (enables
Analytic SQL expressions)
Other New OWB Functionality
shipped as Database Options
Enterprise ETL
Option
Enable Large Scale,
Complex Deployments
Data Quality
Option
Convert Data to
Quality Information
on an ongoing basis
ERP/CRM
Connectors
Extract from common
ERP/CRM (SAP etc.)
Core ETL Features
Easily load Data into Oracle Database
Enterprise ETL Option
 Only available with Enterprise Edition of database
Enterprise ETL
Option
Enable Large Scale,
Complex Deployments
Core ETL Features
Easily load Data into Oracle Database
Enterprise ETL Option
Includes
 Improves performance & scalability of ETL jobs
 Interactive Lineage & Impact Analysis
 User-defined Objects, Relationships and modules
 Advanced Process Flow Functionality
 Change propagation
 XML file as Target
 Pluggable Mappings
 Support large Business Intelligence deployments
–
–
Business Intelligence Beans Generation
Discoverer EUL direct deployment
Data Quality Option
 Only available with Enterprise Edition of database
Enterprise ETL
Option
Enable Large Scale,
Complex Deployments
Data Quality
Option
Convert Data to
Quality Information
on an ongoing basis
Core ETL Features
Easily load Data into Oracle Database
Data Quality Option




Discover Data Anomalies
Identify Data Rules
Auto-correction of Data Anomalies
Audit ETL jobs (and take action if data quality is too low)
CRM/ERP Connector Option
 Only available with Enterprise Edition of database
Enterprise ETL
Option
Enable Large Scale,
Complex Deployments
Data Quality
Option
Convert Data to
Quality Information
on an ongoing basis
ERP/CRM
Connectors
Extract from common
ERP/CRM (SAP etc.)
Core ETL Features
Easily load Data into Oracle Database
CRM/ERP Connector Option
 Extraction from ERP/CRM sources
 Oracle E-Business Suite
 Peoplesoft
 SAP
Summary: OWB 10gR2
Enabling Quality Information
 Data Profiling
 New Data Sources and
Targets
 Enhanced ERP Integration
 Transportable Modules
 Scheduled Process
Execution
 User-defined Objects & Icons
Enabling Business Intelligence
 Relational and Dimensional Data
Object Designer
 Business Intelligence Object
Derivation
Enabling Expertise Capture
 Experts
More Information
 http://www.oracle.com/technology/products/owb
– Collateral
– Software
– Component Exchange
– Discussion forum
– SDK
– Customer Testimonials
– Partner Information
Demo
1. Data Sources
2. Data Targets
3. Mappings
4. Process Flows
5. Configuration
6. Metadata Integration
7. Experts
8. Data Quality (Data Profile)
9. BI Integration
10. Scalability/Performance