oracle10g for data warehousing jiangang luo [email protected]
TRANSCRIPT
Every major platform, Every major architecture
Oracle Terabyte DW Customers Consumer Packaged Goods /
Retail Communications Financial Services Manufacturing.
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
ETLProcessing
Marketing
Finance
Sales
IndependentData Marts OLAP
Engine
MiningEngine
SQLServer
DB2
Oracle Reporting
Fragmented DataFragmented Analysis
Business Intelligence: The Best Way
Consolidate Data Consolidate Analysis
OracleData Mart
Enterprise Data Warehouse
Marketing
Finance
Sales
OLAP
Data Mining
ETL
The Evolving Approach to Warehouse Architecture
OLTP
ODS
Data Warehouse
“Hub”
“Dependent”
Data Mart“Independent”
Data Mart ADS ADS ADS FDS FDS ADS ADS ADS
Data
Data
Data
Data
Data
Data
Data
•Staging Area/ODS
•EDW – 3NF - Atomic
•Reporting/Performance layer- Dimensional
•Executive highly summarized
•OLTP systems
Traditional Warehouse Infrastructure Enterprise Warehouse Infrastructure
•Single database infrastructure
While data warehouse architectural options areWhile data warehouse architectural options aredebatable…… the need for one is not.debatable…… the need for one is not.
ETL OLAP DataMining
Web
企业应用程序
DB
Legacy
Oracle Warehouse
Builder
Oracle 10g
Oracle Application
Server
Reports
Discoverer
BI Beans
Oracle BI/DW Solution
BIDW
BI Beans
Discoverer
Reports
Oracle Datawarehousing
Warehouse Builder
Busines External Data
Data ModelDictiona
ry
ETL Scripts
Reports
Discoverer
OLAPOLAPJDeveloper(BI Beans)
PortalPortalDiscoverer Portlets
Data MinerData Warehouse
Revenue
Customer Time
Product Channel
Cubes
Data
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
ETL
OLAP
Data Mining
Oracle9Oracle9ii
Transformation EngineIntegrated in Oracle DBScalable (parallel)Extensible (Java, PL/SQL)Efficient (no data staging)
Warehouse BuilderExtensible framework for designing and deploying DW’s
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
Oracle Data MiningData Warehousing
ETL
OLAP
Data Mining
Oracle10gOracle10g
Platform for Business Intelligence:OLAP
Data Warehousing
ETL
OLAP
Data Mining
Oracle9Oracle9ii
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
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 multidimensionalobjects & calculations
BI Beans for rapid development ofinternet applications
Oracle OLAP
OLAP
Oracle10g changes this …
Data WarehouseData Warehouse
Data Data WarehouseWarehouse
EngineEngine
DataIntegration
Engine
OLAPEngine
MiningEngine
Multiple databases Multiple servers Multiple engines Proprietary interfaces Complex environment Slow conversion of
data to information
Into this …
Data WarehouseData Warehouse
Single database Single server Single engine Standard interfaces Simplified environment Fastest conversion of
data to information
Data Warehousing
ETL
OLAP
Data Mining
Oracle10gDBOracle10gDB
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
Key 10g Manageability Featuresfor DW/BI