Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Part I Building Your Data Warehouse

1 Introduction to Data Warehousing

• About This Guide
  • Before Using This Guide
  • What This Guide Is Not
• What Is a Data Warehouse?
  • The Key Characteristics of a Data Warehouse
  • Common Oracle Data Warehousing Tasks
• Tasks Illustrated in This Guide
• Tools for Administering the Data Warehouse

2 Setting Up Your Data Warehouse System

• General Steps for Setting Up a Data Warehouse System
• Preparing the Environment
  • Balanced Hardware Configuration
    • How Many CPUs and What Clock Speed Do I Need?
    • How Much Memory Do I Need?
    • How Many Disks Do I Need?
    • How Do I Determine Sufficient I/O Bandwidth?
  • Verifying the Data Warehouse Hardware Configuration
    • About the dd Utility
    • Example: Using the dd Utility
    • About the Orion Utility
• Setting Up a Database for a Data Warehouse
  • How Should I Set the Memory Management Parameters?
  • Example: Setting an Initialization Parameter
  • What Other Initialization Parameter Settings Are Important?
• Accessing Oracle Warehouse Builder
  • Installing the Oracle Warehouse Builder Demonstration

3 Identifying Data Sources and Importing Metadata

• Overview of Data Sources
• General Steps for Importing Metadata from Sources
• About Workspaces, Projects, and Other Devices in Warehouse Builder
• Example: Importing Metadata from Flat Files
  • Specifying Locations for the Flat Files
  • Creating Modules in the Project
  • Starting the Import Metadata Wizard
  • Using the Flat File Sample Wizard
  • Importing the Flat File Data

4 Defining Warehouses in Oracle Warehouse Builder

• General Steps for Defining a Relational Target Warehouse
• Identifying the Warehouse Target Schema
• About Flat File Sources in Warehouse Builder
  • Exercise: Adding External Tables to the Target Module
• About Dimensions
  • Exercise: Understanding Dimensions
    • About Levels
    • Defining Level Attributes
    • Defining Hierarchies
    • Dimension Roles
    • Level Relationships
    • Dimension Example
    • Control Rows
  • Implementing a Dimension
    • Star Schema
    • Binding
• About Cubes
  • Defining a Cube
    • Cube Measures
    • Cube Dimensionality
    • Cube Example
  • Implementing a Cube
    • Relational Implementation of a Cube
    • Binding

Part II Loading Data into Your Data Warehouse

5 Defining ETL Logic

• About Mappings and Operators
• Summary of Steps for Defining Mappings
• Creating a Mapping
  • Types of Operators
• Adding Operators
  • Adding Operators that Bind to Workspace Objects
    • Create Unbound Operator with No Attributes
    • Select from Existing Workspace Object and Bind
• Editing Operators
• Connecting Operators, Groups, and Attributes
  • Connecting Operators
  • Connecting Groups
    • Example: Using the Mapping Editor to Create Staging Area Tables
  • Connecting Attributes
• Setting Operator, Group, and Attribute Properties
• Synchronizing Operators and Workspace Objects
  • Synchronizing an Operator
  • Synchronizing from a Workspace Object to an Operator
    • Synchronizing Operators Based on Workspace Objects
  • Synchronizing from an Operator to a Workspace Object

6 Deploying to Target Schemas and Executing ETL Logic

• About Deployment
  • What is a Control Center?
  • Configuring the Physical Details of Deployment
  • Deployment Actions
  • The Deployment Process
• Deploying Objects
• Starting ETL Jobs
  • Viewing the Data

Part III Reporting on a Data Warehouse

7 SQL for Reporting and Analysis

• Use of SQL Analytic Capabilities to Answer Business Queries
  • How to Add Totals to Reports Using the ROLLUP Function
    • When to Use the ROLLUP Function
    • Example: Using the ROLLUP Function
  • How to Separate Totals at Different Levels Using the CUBE Function
    • When to Use the CUBE Function
    • Example: Using the CUBE Function
  • How to Add Subtotals Using the GROUPING Function
    • When to Use the GROUPING Function
    • Example: Using the GROUPING Function
  • How to Combine Aggregates Using the GROUPING SETS Function
    • When to Use the GROUPING SETS Function
    • Example: Using the GROUPING SETS Function
  • How to Calculate Rankings Using the RANK Function
    • When to Use the RANK Function
    • Example: Using the RANK Function
  • How to Calculate Relative Contributions to a Total
    • Example: Calculating Relative Contributions to a Total
  • How to Perform Interrow Calculations with Window Functions
    • Example: Performing Interrow Calculations
  • How to Calculate a Moving Average Using a Window Function
    • Example: Calculating a Moving Average
• Use of Partition Outer Join to Handle Sparse Data
  • When to Use Partition Outer Join
  • Example: Using Partition Outer Join
• Use of the WITH Clause to Simplify Business Queries
  • When to Use the WITH Clause
  • Example: Using the WITH Clause

Part IV Managing a Data Warehouse

8 Refreshing a Data Warehouse

• About Refreshing Your Data Warehouse
  • Example: Refreshing Your Data Warehouse
• Using Rolling Windows to Offload Data
  • Example: Using a Rolling Window

9 Optimizing Data Warehouse Operations

• Avoiding System Overload
  • Monitoring System Performance
    • Monitoring Parallel Execution Performance
    • Monitoring I/O
  • Using Database Resource Manager
• Optimizing the Use of Indexes and Materialized Views
  • Example: Optimizing Indexes and Materialized Views Using the SQL Access Advisor
• Optimizing Storage Requirements
  • Using Data Compression to Improve Storage

10 Eliminating Performance Bottlenecks

• Verifying That SQL Runs Efficiently
  • Analyzing Optimizer Statistics
  • Analyzing an Execution Plan
    • Example: Analyzing Explain Plan Output
  • Using Hints to Improve Data Warehouse Performance
    • Example: Using Hints to Improve Data Warehouse Performance
  • Using Advisors to Verify SQL Performance
• Improving Performance by Minimizing Resource Use
  • Improving Performance: Partitioning
    • Improving Performance: Partition Pruning
    • Improving Performance: Partitionwise Joins
    • Example: Evaluating Partitioning with the SQL Access Advisor
  • Improving Performance: Query Rewrite and Materialized Views
  • Improving Performance: Indexes
  • Improving Performance: Compression
    • Improving Performance: DBMS_COMPRESSION Package
    • Improving Performance: table_compress clause of CREATE TABLE and ALTER TABLE
• Using Resources Optimally
  • Optimizing Performance with Parallel Execution
    • How Parallel Execution Works
    • Setting the Degree of Parallelism
    • Example: Setting the Degree of Parallelism
  • About Wait Events

11 Backing up and Recovering a Data Warehouse

• How Should I Handle Backup and Recovery for a Data Warehouse?
• Strategies and Best Practices for Backup and Recovery
  • Best Practice A: Use ARCHIVELOG Mode
    • Is Downtime Acceptable?
  • Best Practice B: Use RMAN
  • Best Practice C: Use Read-Only Tablespaces
  • Best Practice D: Plan for NOLOGGING Operations
    • Extraction, Transformation, and Loading
    • Incremental Backup
  • Best Practice E: Not All Tablespaces Are Equally Important

12 Securing a Data Warehouse

• Overview of Data Warehouse Security
  • Why Is Security Necessary for a Data Warehouse?
• Using Roles and Privileges for Data Warehouse Security
• Using a Virtual Private Database in Data Warehouses
  • How a Virtual Private Database Works
• Overview of Oracle Label Security
  • How Oracle Label Security Works
  • How Data Warehouses Benefit from Labels
• Overview of Fine-Grained Auditing in Data Warehouses
• Overview of Transparent Data Encryption in Data Warehouses

Index