Oracle 10 g for Data Warehousing Jiangang Luo

Oracle 10 g for Data Warehousing Jiangang Luo Jiangang. Luo@oracle. com

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 DB 2 Oracle Mining Engine Reporting Fragmented Data Fragmented Analysis Marketing Finance

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 • S g in • Executive le highly summarized t da ab • Reporting/Performance ODS e as layer- Dimensional in uc tr as fr • EDW – Data Warehouse “Hub” 3 NF Atomic re tu • Staging Data Area/ODS “Dependent” Data Mart “Independent” Data Mart • OLTP systems Data ADS FDS Data ADS While data warehouse architectural options are debatable…… the need for one is not. ADS

BI /DW Overview

Oracle BI/DW Solution Web 企业 应用程序 DB DW Oracle Warehouse Builder Oracle 10 g ETL OLAP Data Mining BI Oracle Application Server Reports Discoverer Legacy 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 Warehouse Builder Portal Discoverer Portlets Data Miner Data Warehouse

Oracle 10 g 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 Warehouse Builder 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 9 i

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 Oracle 10 g 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 Oracle 9 i Industrial-strength multidimensional calculation engine Multidimensional data types OLAP API to the Oracle 9 i 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

Oracle 10 g 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 Oracle 10 g. DB Data Warehousing ETL OLAP Data Mining Ÿ Ÿ Ÿ Single database Single server Single engine Standard interfaces Simplified environment Fastest conversion of data to information

Key 10 g Manageability Features Ÿ for 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