Analytic Extensions to SQL in Oracle9i

1
(No Transcript)
2
Oracle10g for Data Warehousing
Jiangang Luo Jiangang.Luo_at_oracle.com
3
Gartner Data Warehouse Magic Quadrant
http//mediaproducts.gartner.com/reprints/oracle/1
21302.html
4
Oracle Terabyte DW Customers


Consumer Packaged Goods /
Retail Communications
Financial Services Manufacturing.






Every major platform, Every major architecture
5
Questions for todays 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?

6
Business Intelligence The Old Way
Independent Data Marts
ETL Processing
OLAPEngine
Sales
SQL Server
Marketing
MiningEngine
DB2
Finance
Oracle
Reporting
Fragmented Data Fragmented Analysis
7
Business Intelligence The Best Way
Enterprise Data Warehouse
Oracle Data Mart
Consolidate Data Consolidate Analysis
8
The Evolving Approach to Warehouse Architecture
Traditional Warehouse Infrastructure
Enterprise Warehouse Infrastructure

• Executive highly summarized

• Reporting/Performance layer- Dimensional

• Single database infrastructure

• EDW 3NF - Atomic

• Staging Area/ODS

• OLTP systems

While data warehouse architectural options
are debatable the need for one is not.
9
BI /DW Overview
10
Oracle BI/DW Solution
DW

BI
Reports
Reports
Oracle 10g
Oracle Warehouse Builder
Oracle Application Server
Discoverer
Discoverer
BI Beans
BI Beans
11
Oracle Datawarehousing
Busines
External Data
Warehouse Builder
Data Warehouse
12
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

13
Platform for Business IntelligenceETL
Oracle9i
14
Oracle10g ETL InfrastructureAddressing the
entire spectrum
Extract
Load
Transform
Insert
Data Pump
Transportable Tablespaces
Change Data Capture
Distributed Queries
SQLLoader
External Tables
Table Functions
Multi-Table Insert
MERGE
New functionality Enhanced functionality
15
Oracle Data Mining

• 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
• For developing business intelligence applications

Oracle10g
16
Platform for Business IntelligenceOLAP

• What is the Oracle OLAP?
• Industrial-strength multidimensional calculation
  engine
• Multidimensional data types
• OLAP API to the Oracle9i Database
• Why do I need the OLAP?
• Complements relational technology by enhancing
  the Database's calculation capabilities
• Multidimensional queries
• Planning functions
• What-if analysis

Oracle9i
17
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 separateolap process
  (unlike competitive products)
• SQL access to multidimensionalobjects
  calculations
• BI Beans for rapid development ofinternet
  applications

OLAP
18
Business ProblemReplication and Fragmentation
19
Oracle10g changes this
Data Warehouse

• Multiple databases
• Multiple servers
• Multiple engines
• Proprietary interfaces
• Complex environment
• Slow conversion of data to information

20
Into this
Data Warehouse

• Single database
• Single server
• Single engine
• Standard interfaces
• Simplified environment
• Fastest conversion ofdata to information

21
Key 10g Manageability Featuresfor DW/BI

• Workload Repository
• 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

22
Oracle Database 10gDW Major Feature Summary

• Reduced Total Cost of Ownership
• Manageability
• Workload Repository
• Automatic SQL Tuning
• Self-Tuning Global Memory
• ASM

• ULDB support
• Database size extended to Exabytes (BIGFILES)
• Unlimited size LOBs
• Hash Partitioned Global Indexes
• ASM removes file system limits

• More Value From Your Data
• Many New OLAP Features
• New Data Mining algorithms
• Stand-alone Data Mining Tool
• Advanced Statistics

• SQL Model Clause
• Frequent Item Sets
• Partition Outer Join

• Intelligence When You Need It
• Cross Platform Transportable Tablespaces
• Data Pump

• Async Change Data Capture
• Enhancements to MERGE