Transcript Analytic Extensions to SQL in Oracle9i

Oracle10g for
Data Warehousing
Jiangang Luo
[email protected]
Gartner Data Warehouse
Magic Quadrant
http://mediaproducts.gartner.com/reprints/oracle/121302.html
Oracle Terabyte DW Customers
Retail
Communications
Financial Services
Consumer Packaged Goods /
Manufacturing.
Every major platform, Every major architecture
Questions for today’s
Enterprise
•
•
•
•
•
Do you have the information you need to make timely, optimized
decisions for improving your revenue and profits?
Can you analyze and drill down on information to make precise
decisions for optimizing your day-to-day business operations?
Do you have a personalized, single point of access to all your
intelligence?
Do you have multiple departmental data marts across your
enterprise?
How do you ensure users can only access information pertinent to
their role or job duties?
Business Intelligence: The Old
Way
ETL
Processing
Independent
Data Marts
OLAP
Engine
Sales
SQL
Server
DB2
Mining
Engine
Marketing
Finance
Oracle
Reporting
Fragmented Data
Fragmented Analysis
Business Intelligence: The Best
Way
Enterprise Data
Warehouse
Marketing
OLAP
Oracle
Data Mart
Finance
Data Mining
ETL
Consolidate Data
Consolidate Analysis
Sales
The Evolving Approach to Warehouse
Architecture
Traditional Warehouse Infrastructure
Enterprise Warehouse Infrastructure
OLTP
•Executive
highly
summarized
•Reporting/Performance
ODS
layer- Dimensional
•EDW –
Data Warehouse
“Hub”
3NF Atomic
•Staging
Data
Area/ODS
“Dependent”
Data Mart
“Independent”
Data Mart
Data
Data
Data
Data
Data
Data
•OLTP
systems
ADS
ADS
ADS
FDS
FDS
ADS
While data warehouse architectural options are
debatable…… the need for one is not.
ADS
ADS
BI /DW Overview
Oracle BI/DW Solution
Web
企业
应用程序
DB
DW
Oracle
Warehouse
Builder
Oracle 10g
ETL
OLAP
Data
Mining
Reports
Reports
BI
Oracle
Application
Server
Discoverer
Discoverer
Legacy
BI Beans
BI Beans
Oracle Datawarehousing
Customer
Busines
Dictiona
ry
External
Data
Time
Reports
Discoverer
Revenue
Data Model
Product
Channel
OLAP
JDeveloper
(BI Beans)
Cubes
ETL Scripts
Data
Portal
Discoverer
Portlets
Warehouse
Builder
Data Miner
Data Warehouse
Oracle10g for Business
Intelligence
 A scalable, full-featured data engine, running on
any hw platform, providing enterprise-strength
security and reliability
–
not a server running on proprietary or special-purpose
hardware
 A single platform delivering all analytic capabilities
–
not a collection of special-purpose analytic engines
with separate repositories
 An integral component of a company's information
architecture
–
not an island of data and analytical results
Platform for Business Intelligence:
ETL
Data Warehousing
Warehouse Builder
ETL
OLAP
Data Mining
Oracle9i
Extensible framework for
designing and deploying
DW’s
Transformation Engine
Integrated in Oracle DB
Scalable (parallel)
Extensible (Java, PL/SQL)
Efficient (no data staging)
Oracle Data Mining
Data Warehousing
 Data Mining embedded in
Oracle Database
–
Simplifies process, eliminates data
movement, and delivers performance
and scalability
 Enhances applications with
predictions and insights
–
Available inside the database
 Java-based API
–
ETL
OLAP
Data Mining
Oracle10g
For developing business intelligence applications
Platform for Business Intelligence:
OLAP
Data Warehousing
 What is the Oracle OLAP?
–
ETL
–
–
OLAP
 Why do I need the OLAP?
–
Data Mining
Oracle9i
Industrial-strength
multidimensional calculation
engine
Multidimensional data types
OLAP API to the Oracle9i
Database
Complements relational
technology by enhancing the
Database's calculation
capabilities
 Multidimensional queries
 Planning functions
 What-if analysis
Oracle OLAP
 Full set of OLAP capabilities
 All storage and processing in
the Oracle database
–
–
Multidimensional structures (dimensions,
cubes) stored natively in the database
No exterior file storage or separate
olap process (unlike competitive products)
 SQL access to multidimensional
objects & calculations
 BI Beans for rapid development of
internet applications
OLAP
Business Problem
Replication and Fragmentation
Data
Replication
Data Warehouse
Data Warehouse
Oracle10g changes this …
Data Warehouse
OLAP
Engine
Data
Integration
Engine
Data
Warehouse
Engine
Mining
Engine






Multiple databases
Multiple servers
Multiple engines
Proprietary interfaces
Complex environment
Slow conversion of
data to information
Into this …
Data Warehouse
Oracle10gDB
Data Warehousing
ETL
OLAP
Data Mining






Single database
Single server
Single engine
Standard interfaces
Simplified environment
Fastest conversion of
data to information
Key 10g Manageability
Features
 for
Workload
Repository
DW/BI
–
Collects and maintains key system metrics: performance measures,
SQL workload, feature usage, …
 Automatic SQL Tuning
–
–
Re-optimizes poor performing queries in background
Applies plan improvements to subsequent executions
 Self-Tuning Memory
–
–
No more parameters for shared_pool, large_pool, …
Two parameters only: PGA, SGA
 Automated Storage Management
–
–
Removes need to manage storage at the “file” level
Simplified management at “disk group” level