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
    • Example: Using 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

• 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
  • Launching 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
• Instructions 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
  • Connecting Attributes
  • Connecting Groups
    • Example: Using the Mapping Editor to Create Staging Area Tables
• Setting Mapping Properties
  • Target Load Order
    • Reset to Default
• 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 Improving Loading Performance

• Improved Performance via Partition Exchange Loading
  • About Partition Exchange Loading
  • Configuring a Mapping for Partition Exchange Loading
  • Direct and Indirect Partition Exchange Loading
    • Using Indirect Partition Exchange Loading
    • Example: Using Direct Partition Exchange Loading to Publish Fact Tables
  • Using Partition Exchange Loading Effectively
  • Configuring Targets in a Mapping
    • Step 1: Create All Partitions
    • Step 2: Create All Indexes Using the LOCAL Option
    • Step 3: Primary/Unique Keys Use "USING INDEX" Option
  • Restrictions for Using Partition Exchange Loading in Warehouse Builder

7 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
• About Schedules
• Process for Defining and Using Schedules
• Deploying Objects
• Starting ETL Jobs
  • Viewing the Data

Part III Analyzing and Reporting on a Data Warehouse

8 Analyzing Metadata Dependencies

• Introduction to the Metadata Dependency Manager
• Usage Scenario
• What are Lineage and Impact Analysis Diagrams?
• Generating an LIA Diagram
• Modifying the Display of an LIA Diagram
  • Using Groups in an LIA Diagram
  • Displaying an Object's Attributes
• Modifying Objects in the Dependency Manager

9 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

10 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

11 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

12 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 Consumption
  • 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
• 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

13 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

14 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 Virtual Private Database in Data Warehouses
  • How 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