Oracle 10g Data Warehousing

Oracle 10g Data Warehousing

Released April 2011
Publisher(s): Digital Press
ISBN: 9780080513287

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Oracle 10g Data Warehousing is a guide to using the Data Warehouse features in the latest version of Oracle —Oracle Database 10g. Written by people on the Oracle development team that designed and implemented the code and by people with industry experience implementing warehouses using Oracle technology, this thoroughly updated and extended edition provides an insider’s view of how the Oracle Database 10g software is best used for your application.

It provides a detailed look at the new features of Oracle Database 10g and other Oracle products and how these are used in the data warehouse. This book will show you how to deploy the Oracle database and correctly use the new Oracle Database 10g features for your data warehouse. It contains walkthroughs and examples on how to use tools such as Oracle Discoverer and Reports to query the warehouse and generate reports that can be deployed over the web and gain better insight into your business.

This how-to guide provides step by step instructions including screen captures to make it easier to design, build and optimize performance of the data warehouse or data mart. It is a ‘must have’ reference for database developers, administrators and IT professionals who want to get to work now with all of the newest features of Oracle Database 10g.

It provides a detailed look at the new features of Oracle Database 10g and other Oracle products and how these are used in the data warehouse, including:
* How to use the Summary Management features, including Materialized Views and query rewrite, to best effect to radically improve query performance
* How to deploy business intelligence to the Web to satisfy today's changing and demanding business requirements
* Using Oracle OLAP and Data Mining options
* How to understand the warehouse hardware environment and how it is used by new features in the database including how to implement a high availability warehouse
environment
* Using the new management infrastructure in Oracle Database 10g and how this helps you to manage your warehouse environment

Table of contents

  1. Copyright
    1. Dedication
  2. Related Titles from Digital Press
  3. Foreword
  4. Preface
  5. Acknowledgments
  6. 1. Data Warehousing
    1. 1.1. An Introduction to Oracle Database 10g
      1. 1.1.1. The Economic Climate Influences Technology Spending
      2. 1.1.2. Consolidation
      3. 1.1.3. Consolidation of the Hardware
      4. 1.1.4. Consolidation of Data into a Single Company View
      5. 1.1.5. Consolidation of Applications
      6. 1.1.6. The g in Oracle Database 10g—The Grid
    2. 1.2. What Is a Data Warehouse?
      1. 1.2.1. Why Do You Need a Data Warehouse?
    3. 1.3. A Historical Perspective
      1. 1.3.1. The Rise of the Data Warehouse
        1. The Data Warehouse Is Used to Look beyond the Data to Find Information
        2. The Data Warehouse Requires a Different Database Design
      2. 1.3.2. Data Warehouses Evolved As Separate Systems
        1. The Data Warehouse Is Built from the Operational Systems
      3. 1.3.3. The Data Mart
        1. Independent Data Marts Were Built
        2. Operational Data Stores Appeared To Consolidate Reporting of Recent Information
        3. Incrementally Building The Data Warehouse With Dependent Data Marts
      4. 1.3.4. Reporting, Query, and Analysis Tools Became Browser Based
      5. 1.3.5. OLAP and Data Mining Functionality Are Embedded in the Oracle Database
    4. 1.4. Data Warehousing Features in the Oracle Database 10g
    5. 1.5. Building a Data Warehouse Poses Many Challenges
      1. 1.5.1. Managing the Warehouse
      2. 1.5.2. The Role of Metadata
      3. 1.5.3. Increasing Data Volume
      4. 1.5.4. Higher Availability
      5. 1.5.5. More Users/Better Performance
      6. 1.5.6. New Types Of Applications
    6. 1.6. The Future of Data Warehousing
      1. 1.6.1. Real-Time Data Warehouses
      2. 1.6.2. The Disappearance of the Separate Data Warehouse
    7. 1.7. Summary
  7. 2. Designing a Warehouse
    1. 2.1. Designing a Warehouse
      1. 2.1.1. Don’t Use Entity Relationship (E-R) Modeling
      2. 2.1.2. Dimensional Modeling
      3. 2.1.3. Fact Table
      4. 2.1.4. Dimension Table
      5. 2.1.5. Warehouse Keys
      6. 2.1.6. Normalizing the Data Warehouse
      7. 2.1.7. Data Warehouse or Data Mart
      8. 2.1.8. The Easy Shopping Inc. Example
    2. 2.2. Other Design Considerations
      1. 2.2.1. Design to Manage
      2. 2.2.2. Design for Performance
    3. 2.3. Implementing the Design
      1. 2.3.1. Single Database or Many?
      2. 2.3.2. Naming Conventions
      3. 2.3.3. Database Configuration Assistant
        1. Using a Preconfigured Database
      4. 2.3.4. Which Schema?
      5. 2.3.5. Data Files and Tablespaces
      6. 2.3.6. Creating the Fact and Dimension Tables
      7. 2.3.7. Constraints
      8. 2.3.8. Indexes
      9. 2.3.9. Partitioning
      10. 2.3.10. Materialized Views
      11. 2.3.11. Security
        1. Object Privileges
        2. Role
        3. System Privileges
      12. 2.3.12. Using the Parallel Option
    4. 2.4. Testing the Design
    5. 2.5. The Schema for Easy Shopping Inc.
  8. 3. Architecture of a Data Warehouse
    1. 3.1. Introduction
    2. 3.2. Hardware Configurations for a Warehouse
      1. 3.2.1. Server Architectures
        1. The Single-Processor, Single-Disk Architecture
        2. The Multidisk Architecture
        3. The Multiprocessor Server Architecture
        4. Clustered Servers
        5. Massively Parallel Processor Architectures
      2. 3.2.2. The Oracle Database Architecture
        1. The Oracle Instance and Database
        2. The Scalable Database: Oracle Database 10g RAC
        3. Oracle Database 10g Grid
    3. 3.3. Hardware Components
      1. 3.3.1. Memory
      2. 3.3.2. Processors
      3. 3.3.3. Storage Configurations for a Warehouse
        1. The I/O Subsystem
        2. Striping and RAID
        3. Stripe and Mirror Everything
        4. Shared Storage
    4. 3.4. Automatic Storage Management
      1. 3.4.1. ASM Overview
      2. 3.4.2. Administering ASM
        1. Setting up ASM
        2. Configuring ASM during Installation
        3. Managing ASM from the Command Line
        4. Managing ASM from Enterprise Manager Grid Control
      3. 3.4.3. Using ASM in the Warehouse
    5. 3.5. File Management in Oracle
      1. 3.5.1. Oracle Managed Files
      2. 3.5.2. Bigfiles and Big Databases
    6. 3.6. Summary
  9. 4. Physical Design of the Data Warehouse
    1. 4.1. Introduction
    2. 4.2. Data Partitioning
      1. 4.2.1. How to Partition Data?
      2. 4.2.2. Range Partitioning
      3. 4.2.3. Hash Partitioning
      4. 4.2.4. List Partitioning
      5. 4.2.5. Composite Partitioning
        1. Range-Hash Partitioning
        2. Range-List Partitioning
      6. 4.2.6. Multicolumn Partition Keys
      7. 4.2.7. Choosing the Partitioning Method
      8. 4.2.8. Partitioning Using Oracle Enterprise Manager
      9. 4.2.9. Partition Maintenance Operations
    3. 4.3. Indexing
      1. 4.3.1. B*tree Indexes
      2. 4.3.2. Bitmapped Indexes
      3. 4.3.3. Bitmapped Join Indexes
      4. 4.3.4. Function-based Indexes
      5. 4.3.5. Partitioned Indexes
        1. Global Indexes
        2. Local Indexes
        3. Prefixed Local Indexes
        4. Nonprefixed Local Indexes
      6. 4.3.6. Which Indexes to Create?
        1. Deciding between Local and Global Indexes
        2. Need Help Deciding Which Indexes to Create?
      7. 4.3.7. Using Oracle Enterprise Manager to Create Indexes
    4. 4.4. Index-Organized Tables
      1. 4.4.1. Creating an IOT
      2. 4.4.2. Partitioning and Indexing an IOT
      3. 4.4.3. Using an IOT in a Data Warehouse
    5. 4.5. Data Compression
      1. 4.5.1. Table Compression
      2. 4.5.2. Index Compression
    6. 4.6. Summary
  10. 5. Loading Data into the Warehouse
    1. 5.1. The ETL Process
    2. 5.2. Extracting Data from the Operational Systems
      1. 5.2.1. Identifying Data That Has Changed
      2. 5.2.2. Oracle Change Data Capture
        1. Publishing Change Data
        2. Synchronous CDC
        3. Asynchronous CDC
        4. Subscribing to Change Data
        5. Creating a Subscription
        6. Processing the Change Data
        7. Step 1: Extend the window
        8. Step 2: Select Data from the Subscriber View
        9. Step 3: Purge the Window
        10. Ending the Subscription
        11. Transporting the Changes to the Staging Area
    3. 5.3. Transforming the Data into a Common Representation
      1. 5.3.1. Integrating Data from Multiple Sources
      2. 5.3.2. Cleansing Data
      3. 5.3.3. Deriving New Data
      4. 5.3.4. Generating Warehouse Keys
      5. 5.3.5. Choosing the Optimal Place to Perform the Transformations
    4. 5.4. Loading the Warehouse
      1. 5.4.1. Using SQL*Loader to Load the Warehouse
        1. Using Oracle Enterprise Manager Load Wizard
        2. The Control File
      2. 5.4.2. The Data File
        1. SQL*Loader Modes of Operation
        2. Data Load Options
        3. Scheduling the Load Operation
        4. Monitoring Progress of the Load Operation
        5. Inspecting the SQL*Loader Log
        6. Optimizing SQL*Loader Performance
        7. SQL*Loader Direct Path Load of a Single Partition
        8. Step 1: Create a Tablespace
        9. Step 2: Add a Partition
        10. Step 3: Disable All Referential Integrity Constraints and Triggers
        11. Step 4: Load the Data
        12. Step 5: Inspect the Log
        13. Step 6: Reenable All Constraints and Triggers, Rebuild Indexes
        14. SQL*Loader Parallel Direct Path Load
        15. Step 1: Disable All Constraints and Triggers
        16. Step 2: Drop all Indexes
        17. Step 3: Load the Data
        18. Step 4: Inspect the Log
        19. Step 5: Reenable All Constraints and Triggers, Recreate All Indexes
        20. Transformations Using SQL*Loader
        21. SQL*Loader Postload Operations
        22. Step 1: Inspect the Logs
        23. Step 2: Process the Load Exceptions
        24. Step 3: Reenable Data Integrity Constraints
        25. Step 4: Handle Constraint Violations
        26. Step 5: Enabling Constraints without Validation
        27. Check for Unusable Indexes
        28. Rebuild unusable indexes
      3. 5.4.3. Loading the Warehouse Using Data Pump
        1. Data Pump Import/Export—impdp and expdp
        2. Specifying the Location of the Datafile and Log Files for Data Pump Tools
        3. Moving Data between Databases
        4. Improved Job Monitoring and Control
      4. 5.4.4. Loading the Warehouse Using External Tables
        1. Creating an External Table
        2. Accessing Data Stored in an External Table
        3. Loading Data From an External Table
        4. Loading Data in Parallel Using External Tables
        5. Using Data Pump External Tables to Move and Load Data
      5. 5.4.5. Loading the Warehouse Using Transportable Tablespaces
        1. Step 1: Create a Tablespace in the OLTP System
        2. Step 2: Move the Data for April 2004 into a Table in the Newly Created Tablespace
        3. Step 3: Alter the Tablespace So That It Is Read-Only
        4. Step 4: EXPORT the Metadata
        5. Step 5: Convert the Datafiles (Optional)
        6. Step 6: Transport the Tablespace
        7. Step 7: Import the Metadata
        8. Step 8: Alter the Tablespace to Read/Write
      6. 5.4.6. Loading the Dimensions Using SQL MERGE
    5. 5.5. Transformations inside the Oracle Database
      1. 5.5.1. Transformations That Cleanse Data and Derive New Data
        1. Processing With More Power: The REGEXP Functions
        2. Regular Expression Basics and Searching
        3. Regular Expressions and Substrings
        4. Regular Expressions to Manipulate Data
      2. 5.5.2. Validating Data Using a Dimension
      3. 5.5.3. Looking up the Warehouse Key
      4. 5.5.4. Table Functions
      5. 5.5.5. Transformations That Split One Data Source into Multiple Targets
      6. 5.5.6. Moving Data from a Staging Table into the Fact Table
        1. Step 1: Create a New Tablespace for the Jan Purchases and the Jan Purchases Index
        2. Step 2: Add a Partition to the Purchases Table
        3. Step 3: Move the table into the new partition
        4. Moving Data Using Exchange Partition
        5. Moving Data Between Tables Using Direct Path Insert
        6. Creating a New Table Using Create Table As Select
    6. 5.6. Postload Operations
      1. 5.6.1. Step 1: Gather Optimizer Statistics for the Tables
      2. 5.6.2. Step 2: Verify the Dimensions
      3. 5.6.3. Step 3: Refresh the materialized views
      4. 5.6.4. Step 4: Gather Optimizer Statistics for the Materialized Views
      5. 5.6.5. Step 5: Back up the Database Table, or Partition
      6. 5.6.6. Step 6: Publish the Data
    7. 5.7. Using Tools for the ETL process
    8. 5.8. Summary
  11. 6. Querying the Data Warehouse
    1. 6.1. Introduction
    2. 6.2. The Query Optimizer
      1. 6.2.1. EXPLAIN PLAN
      2. 6.2.2. Join Method Basics
        1. Nested Loops Join
        2. Sort-Merge Join
        3. Hash Join
        4. How to Pick the Join Method?
      3. 6.2.3. Star Transformation
      4. 6.2.4. Partition Pruning
        1. Range Partitioning
        2. Hash Partitioning
        3. List Partitioning
        4. Composite Partitioning
      5. 6.2.5. Partition-Wise Join
    3. 6.3. Parallel Execution
      1. 6.3.1. SQL Statements That Can Be Parallelized
      2. 6.3.2. Setting up Parallel Execution
      3. 6.3.3. Hardware Requirements for Parallel Execution
    4. 6.4. SQL Features for Querying the Data Warehouse
      1. 6.4.1. SQL Extensions for Aggregation
        1. CUBE
        2. ROLLUP
        3. GROUPING SETS
        4. GROUPING and GROUPING_ID Functions
        5. User-Defined Aggregates
      2. 6.4.2. SQL Functions for Analysis
        1. Ranking Functions
        2. PARTITION BY Clause
        3. Relative Ranking Functions
        4. WIDTH_BUCKET Function
        5. Period-over-Period Comparison Functions—LAG and LEAD
        6. Window Aggregate Functions
        7. Specifying a Logical Window
        8. Converting Sparse Data into Dense Form
        9. OUTER JOIN
        10. Partition Outer Join
        11. Reporting Aggregates
        12. First and Last Functions
        13. Inverse Percentile
        14. Hypothetical RANK and Distribution Functions
        15. Statistical Analysis Functions
        16. DBMS_STATS_FUNC Package
        17. CASE Expression
        18. WITH Clause
      3. 6.4.3. The SQL Model Clause
        1. A Simple Calculation
        2. More about RULES
        3. Cell Referencing
        4. UPDATE versus UPSERT
        5. Rule Ordering
        6. Iteration
        7. Some Examples of the SQL Model Clause
    5. 6.5. Summary
  12. 7. Summary Management
    1. 7.1. Summary Tables
      1. 7.1.1. Why Do You Need Summary Management?
      2. 7.1.2. Summary Management with Oracle
    2. 7.2. Creating a Materialized View
      1. 7.2.1. Naming the Materialized View
      2. 7.2.2. The Physical Storage for the Materialized View
      3. 7.2.3. When Should the Materialized View Be Populated with Data?
      4. 7.2.4. How Should the Materialized View Be Refreshed?
      5. 7.2.5. When should the Materialized View be refreshed?
      6. 7.2.6. Enabling the Materialized View for Query Rewrite
      7. 7.2.7. Specifying the Contents of the Materialized View
      8. 7.2.8. Creating a Materialized View in Enterprise Manager
      9. 7.2.9. Using Summary Management with Existing Summary Tables
      10. 7.2.10. Partitioning the Materialized View
      11. 7.2.11. Indexing the Materialized View
      12. 7.2.12. Security of Materialized Views
    3. 7.3. Refresh
      1. 7.3.1. Using the DBMS_MVIEW Refresh Procedures
      2. 7.3.2. Using Enterprise Manager for Refresh
      3. 7.3.3. Fast Refresh
        1. Fast Refresh Using Materialized View Logs
        2. Materialized View Log Options
        3. Using Oracle Enterprise Manager to Define Materialized View Logs
        4. Other Requirements for Fast Refresh
      4. 7.3.4. Partition Change Tracking
        1. When and How to Perform PCT Refresh
        2. Creating a Materialized View That Supports PCT Refresh
        3. Join Dependency Expression
        4. Partition Key
        5. Partition Marker
      5. 7.3.5. Refresh Performance
        1. Optimizer Statistics
        2. Set the Parameter atomic_refresh to FALSE
        3. Use PCT Refresh and Partitioned Materialized Views
        4. Use Parallel Execution
        5. Build and Refresh Multiple Materialized Views Using REFRESH_ALL
        6. Enable Query Rewrite during Refresh
      6. 7.3.6. Nested Materialized Views
        1. Why Use Nested Materialized Views?
        2. Refreshing Nested Materialized Views
    4. 7.4. EXPLAIN_MVIEW Utility
      1. 7.4.1. Running EXPLAIN_MVIEW procedure
      2. 7.4.2. Using Oracle Enterprise Manager to run EXPLAIN_MVIEW
    5. 7.5. TUNE_MVIEW Utility
    6. 7.6. Summary
  13. 8. Dimensions
    1. 8.1. Concepts
    2. 8.2. Creating a Dimension
      1. 8.2.1. Defining a Dimension with a Single Hierarchy
      2. 8.2.2. Defining a Dimension with Multiple Hierarchies
      3. 8.2.3. Defining a Dimension with Attributes
      4. 8.2.4. Defining a Dimension with Normalized Tables
      5. 8.2.5. Creating Dimensions with Oracle Enterprise Manager
    3. 8.3. Describing a Dimension
    4. 8.4. Validating a Dimension
    5. 8.5. Summary
  14. 9. Query Rewrite
    1. 9.1. Setting up Query Rewrite
      1. 9.1.1. How Can We Tell If a Query Was Rewritten?
    2. 9.2. Types of Query Rewrite
      1. 9.2.1. SQL Text Match
      2. 9.2.2. Aggregate Rollup
      3. 9.2.3. Join-back
      4. 9.2.4. Computing Other Aggregates in the Query
      5. 9.2.5. Filtered Data
      6. 9.2.6. Rewrite Using Materialized Views with No Aggregation
      7. 9.2.7. Rewrite Using Dimensions
        1. Using the HIERARCHY Clause
        2. Using the ATTRIBUTE Clause
      8. 9.2.8. Rewrite Using Constraints
        1. Using the NOVALIDATE and RELY Clauses on Constraints
    3. 9.3. Query Rewrite Integrity Modes
      1. 9.3.1. Comparing ENFORCED and TRUSTED Modes
      2. 9.3.2. Comparing TRUSTED and STALE_TOLERATED Modes
    4. 9.4. Query Rewrite and Partition Change Tracking
      1. 9.4.1. Query Rewrite with PCT Using Partition Key
      2. 9.4.2. Query Rewrite Using PCT with Partition Marker
    5. 9.5. Troubleshooting Query Rewrite with EXPLAIN_REWRITE
    6. 9.6. Advanced Query Rewrite Techniques
      1. 9.6.1. Optimizer Hints for Query Rewrite
        1. Forcing an Error When Query Rewrite Is Not Possible
      2. 9.6.2. Query Rewrite and Bind Variables
      3. 9.6.3. Query Rewrite with Complex SQL Constructs
        1. Set Operators
        2. Subqueries in the FROM Clause
        3. Multiple Occurrences of a Table in the FROM Clause
        4. Grouping Sets
        5. Analytical Functions
      4. 9.6.4. Query Rewrite Using Nested Materialized Views
      5. 9.6.5. Rewrite Equivalences
      6. 9.6.6. Using Query Rewrite during Refresh
    7. 9.7. Summary
  15. 10. Tuning Query Performance
    1. 10.1. Monitoring Performance
      1. 10.1.1. SQL Tuning Sets
        1. Creating a SQL Tuning Set in Oracle Enterprise Manager
        2. Creating a SQL Tuning Set Using the DBMS_SQLTUNE Package
    2. 10.2. Advisor Central
    3. 10.3. SQL Access Advisor
      1. 10.3.1. SQL Access Advisor Wizard
        1. Choosing a Workload Source
        2. Specifying Tuning Parameters
        3. Generating Recommendations
        4. Viewing the Results of the Analysis
        5. Recommendations View
        6. SQL Statement View
        7. Implementing the Recommendations
      2. 10.3.2. DBMS_ADVISOR PL/SQL Package
        1. Step 1: Creating a Workload Named MY_WORKLOAD
        2. Step 2: Specifying Workload Parameters for Filtering
        3. Step 3: Load Workload Statements
        4. Step 4: Create a SQL Access Advisor Task and Set Parameters
        5. Step 5: Create a Link between Workload and Task
        6. Step 6: Execute the Task to Generate Recommendations
      3. 10.3.3. Templates
        1. Step1: Defining a Template
        2. Step2: Create a Task Using the Template
      4. 10.3.4. Quick_Tune
    4. 10.4. SQL Tuning Advisor
      1. 10.4.1. SQL Tuning Advisor in Enterprise Manager
        1. Profiles
      2. 10.4.2. The DBMS_SQLTUNE PL/SQL Package
    5. 10.5. Memory Advisor
      1. 10.5.1. Tuning PGA Memory
        1. Monitoring PGA Memory Usage
        2. Tuning PGA_AGGREGATE_TARGET
      2. 10.5.2. SGA Memory Advisor
        1. Shared Pool Advice
        2. Buffer Cache Advice
    6. 10.6. Troubleshooting Parallel Execution
      1. 10.6.1. Using EXPLAIN PLAN to Display Parallel Plans
      2. 10.6.2. Problems Due to Resource Constraints
    7. 10.7. Plan Stability
      1. 10.7.1. Creating an Outline
      2. 10.7.2. Using an Outline
    8. 10.8. Summary
  16. 11. Managing the Warehouse
    1. 11.1. What Has to Be Managed
    2. 11.2. Managing Using Oracle Enterprise Manager
      1. 11.2.1. The Enterprise Manager Console
      2. 11.2.2. Overview of Enterprise Manager
      3. 11.2.3. Enterprise Manager Database Control
        1. Home
        2. Performance
        3. Administration
        4. Maintenance
      4. 11.2.4. Enterprise Manager Grid Control
      5. 11.2.5. Enterprise Manager Administrators
      6. 11.2.6. Creating and Using Groups
      7. 11.2.7. Scheduling Jobs
        1. The Oracle Database 10g Scheduler
        2. Enterprise Manager Job System
    3. 11.3. Monitoring the Warehouse
      1. 11.3.1. Automatic Workload Repository (AWR)
      2. 11.3.2. Automatic Database Diagnostic Monitor (ADDM)
      3. 11.3.3. Using Alerts
    4. 11.4. Reorganizing the Warehouse
      1. 11.4.1. Why Reorganize?
      2. 11.4.2. Partition Maintenance
        1. Rolling Window Partition Maintenance
        2. Exchanging a Partition
        3. Merging Partitions
        4. Splitting Partitions
        5. Coalescing Hash Partitions
        6. Truncating Partitions
        7. Moving Partitions
        8. Partitions Facilitate Management
      3. 11.4.3. Index Changes
      4. 11.4.4. Online Redefinition of Tables
        1. Online Redefinition via Enterprise Manager
        2. Online Redefinition Using the DBMS_REDEFINITION Package
        3. Step 1: Create an Interim Table
        4. Step 2: Start the Redefinition Process
        5. Step 3: Create the Dependant Objects
        6. Step 4: Synchronize the Tables
        7. Step 5: Finish the Redefinition
        8. Step 6: Gather Statistics on the Redefined Table
      5. 11.4.5. Online Segment Shrink
    5. 11.5. Refreshing the Warehouse
    6. 11.6. Gathering Optimizer Statistics
      1. 11.6.1. Automatic Statistics Collection
      2. 11.6.2. Manual Statistics Collection
      3. 11.6.3. Collecting System Statistics
      4. 11.6.4. Dynamic Sampling
    7. 11.7. Parallel Management Tasks
    8. 11.8. Maintaining Security
      1. 11.8.1. Virtual Private Database
    9. 11.9. Monitoring Space Usage
      1. 11.9.1. Automated Space Management
      2. 11.9.2. Resumable Space Allocation
    10. 11.10. Other Management Issues
      1. 11.10.1. Building a Test System
      2. 11.10.2. Testing New Software
      3. 11.10.3. Timing Data Loads
      4. 11.10.4. Evaluating/Practicing Management Tasks
      5. 11.10.5. Determine Query Response Times
    11. 11.11. Summary
  17. 12. Backup and Recovery
    1. 12.1. Strategy
      1. 12.1.1. Methods of Performing a Backup and Recovery
        1. Backup
          1. Logical Backup
          2. Physical Backup
          3. Incremental Backups
          4. Block Change Tracking
        2. Restore and Recovery
      2. 12.1.2. Simplifying Recovery with Flash Recovery Area
        1. Setting up the Flash Recovery Area
    2. 12.2. Backup
      1. 12.2.1. Creating a Backup Configuration
      2. 12.2.2. Full Backups
        1. Predefined Backup Strategy
      3. 12.2.3. Incremental Backups
      4. 12.2.4. Tablespace Backups
      5. 12.2.5. Backup File Sizes
    3. 12.3. The Recovery Catalog
      1. 12.3.1. Creating the Recovery Catalog
      2. 12.3.2. Registering the EASYDW database with RMAN
    4. 12.4. Restore and Recover
    5. 12.5. Summary
  18. 13. Oracle Warehousing Tools
    1. 13.1. Which Tool
    2. 13.2. Oracle Warehouse Builder
      1. 13.2.1. Setting up Warehouse Builder
      2. 13.2.2. Oracle Warehouse Builder Client
      3. 13.2.3. Data Sources and Targets
        1. Oracle Database Source
        2. Oracle Database Target
        3. File Data Source
      4. 13.2.4. Defining the Tables in Our Data Warehouse
      5. 13.2.5. Creating Dimensions
      6. 13.2.6. Creating a Cube
      7. 13.2.7. Defining Source to Target Mappings
        1. Mapping a Source to a Target
        2. Computing a Value
        3. Joining Tables to Obtain Data
        4. Key Lookup
        5. Filtering Data
        6. Using Data Generators
      8. 13.2.8. Validating the Design
      9. 13.2.9. Generating the Design
      10. 13.2.10. Deploying the Design
        1. Configuring the Physical Design
    3. 13.3. Oracle Discoverer
      1. 13.3.1. Why Discoverer?
        1. Query Using Discoverer Viewer
        2. Dynamic Reports with Graphs
        3. Drilling on the Data
      2. 13.3.2. Setting up the Environment
        1. End-User Layer
        2. Business Area
        3. Restricting the Visible Columns
        4. Folders
        5. Changing Item Details
        6. Creating New Items
        7. Creating Joins
        8. Hierarchies
        9. Item Classes—List of Values
        10. Summaries
        11. Security Issues
      3. 13.3.3. Query Using Discoverer Plus
    4. 13.4. Oracle Reports 10g
      1. 13.4.1. Creating a Report Using the Report Builder
      2. 13.4.2. More Oracle Reports Examples
        1. Matrix Report
        2. Conditional Report
      3. 13.4.3. Publishing the Report
    5. 13.5. Summary
  19. 14. Data Warehousing and the Web
    1. 14.1. Overview
      1. 14.1.1. Internet and Intranet
      2. 14.1.2. Oracle Software for the Data Warehouse
    2. 14.2. Oracle Application Server 10g
      1. 14.2.1. Why Set up a Portal?
      2. 14.2.2. OracleAS Portal
      3. 14.2.3. Getting Started with Oracle Application Server 10g
    3. 14.3. Publishing Data on the Web
      1. 14.3.1. Discoverer
        1. Creating a Public Connection
      2. 14.3.2. Publishing a Portlet
      3. 14.3.3. Embedding a Static Report
    4. 14.4. Oracle Personalization
    5. 14.5. The Data Warehouse and E-Business Intelligence
  20. 15. OLAP
    1. 15.1. Why Do We Need the Oracle OLAP Option?
      1. 15.1.1. OLAP Applications
      2. 15.1.2. ROLAP and MOLAP
      3. 15.1.3. Oracle OLAP
    2. 15.2. Oracle OLAP Architecture
    3. 15.3. Analytic Workspaces
      1. 15.3.1. The Multidimensional Model
      2. 15.3.2. Creating Analytic Workspaces
    4. 15.4. The OLAP Catalog
      1. 15.4.1. Defining OLAP Metadata for a Relational Schema
        1. Creating OLAP Metadata in Oracle Enterprise Manager
      2. 15.4.2. OLAP Metadata Views and Validation
    5. 15.5. The Analytic Workspace Manager
      1. 15.5.1. The Create Analytic Workspace Wizard
        1. Setting Storage Options for the Analytic Workspace
      2. 15.5.2. Refreshing the Analytic Workspace
      3. 15.5.3. Creating an Aggregation Plan
      4. 15.5.4. Analytic Workspace Enablers
        1. Enabling Analytic Workspace for OLAP API and BI Beans
        2. Enabling Analytic Workspace for Discoverer
    6. 15.6. Querying Analytic Workspaces
      1. 15.6.1. OLAP DML
        1. Attaching to an Analytic Workspace
        2. Standard Form Entities
        3. Reporting and Aggregating Data with OLAP DML
        4. Defining Formulas and Custom Measures
        5. Forecasting Using OLAP DML
      2. 15.6.2. DBMS_AW package
      3. 15.6.3. SQL Access to Analytic Workspaces
      4. 15.6.4. OLAP API and BI Beans
    7. 15.7. Summary
  21. 16. Oracle Data Mining
    1. 16.1. Oracle Database 10g Data Mining Option
    2. 16.2. Oracle Data Mining Techniques
      1. 16.2.1. Association Rules
      2. 16.2.2. Clustering
        1. Clustering Algorithms
      3. 16.2.3. Feature Extraction
      4. 16.2.4. Classification
        1. Testing a Classification Model
        2. Computing Lift
        3. Classification Algorithms
      5. 16.2.5. Regression
      6. 16.2.6. The PMML Standard
    3. 16.3. Preparing Data for Oracle Data Mining
      1. 16.3.1. Data Format
      2. 16.3.2. Data Preparation
        1. Binning or Discretization
        2. Normalizing
        3. Attribute Importance
    4. 16.4. Using Oracle Data Mining Interfaces
      1. 16.4.1. Installation and Configuration
      2. 16.4.2. Data Mining Analysis Flow
      3. 16.4.3. An Example Using the Java API
        1. Building a Model
        2. Applying a Model
      4. 16.4.4. An Example Using the PL/SQL Procedures
        1. Data Preparation
        2. Building a Model
        3. Applying a Model
    5. 16.5. Summary
  22. 17. High Availability and a Data Warehouse
    1. 17.1. Introduction
    2. 17.2. What Is a Highly Available System?
      1. 17.2.1. Characteristics of a highly available system
        1. Reliability
        2. Recoverability
        3. Continuous Operation
      2. 17.2.2. Role of Operational Best Practices
    3. 17.3. Overview of Oracle Database 10g High Availability Features
    4. 17.4. Protecting against Hardware/Software Failures
      1. 17.4.1. Real-Application Clusters (RAC)
      2. 17.4.2. Reliable Storage
        1. Automatic Storage Management
        2. Integration with HARD-Compliant Storage
      3. 17.4.3. Failure Detection and Monitoring
        1. Automatic Proactive Notifications
      4. 17.4.4. Resource Management
    5. 17.5. Protecting against Data Loss
      1. 17.5.1. Recovering from Media Failure
      2. 17.5.2. Recovery from Human Errors with Flashback
        1. Flashback Table
        2. Flashback Drop
        3. Flashback Query
        4. Flashback Database
      3. 17.5.3. Disaster Recovery Using Data Guard
        1. Data Guard Concepts
        2. Physical Standby
        3. Logical Standby
        4. Logical Standby Databases and Data Warehouse
        5. Data Guard Configuration
        6. Data Guard Broker
        7. Using Oracle Enterprise Manager to Configure Data Guard
        8. Data Guard Protection Modes
        9. Maximum Protection Mode
        10. Maximum Performance Mode
        11. Maximum Availability Mode
        12. Switching Roles From Primary to Standby Database
      4. 17.5.4. Oracle Maximum Availability Architecture
      5. 17.5.5. Protecting Metadata
    6. 17.6. Managing Planned Downtime
      1. 17.6.1. Dynamic Instance Reconfiguration
      2. 17.6.2. Online Maintenance
      3. 17.6.3. Online Redefinition
      4. 17.6.4. Rolling Upgrades
        1. Using RAC
        2. Using Data Guard
    7. 17.7. Information Lifecycle Management
    8. 17.8. Summary
  23. A. The Schema for Easy Shopping Inc.
    1. A.1. Creating the Tablespaces and Data Files
    2. A.2. Creating the Tables, Constraints, and Indexes
    3. A.3. Defining Security
    4. A.4. Final Steps
  24. B. Product Information

Product information

  • Title: Oracle 10g Data Warehousing
  • Author(s):
  • Release date: April 2011
  • Publisher(s): Digital Press
  • ISBN: 9780080513287