Fundamentals of Aggregates
Star Schema Basics
Operational Systems and the Data Warehouse
Facts and Dimensions
The Star Schema
Using the Star Schema
Multiple Stars and Conformance
Data Warehouse Architecture
Invisible Aggregates
Improving Performance
The Base Schema and the Aggregate Schema
The Aggregate Navigator
Principles of Aggregation
Other Types of Summarization
Summary
Choosing Aggregates
What Is a Potential Aggregate
Aggregate Fact Tables: A Question of Grain
Aggregate Dimensions Must Conform
Pre-Joined Aggregates Have Grain Too
Enumerating Potential Aggregates
Identifying Potentially Useful Aggregates
Drawing on Initial Design
Where Subject Areas Meet
Query Patterns of an Existing System
Assessing the Value of Potential Aggregates
Number of Aggregates
How Many Rows Are Summarized
Who Will Benefit From the Aggregate
Summary
Designing Aggregates
The Base Schema
Identification of Grain
Conformance Bus
Rollup Dimensions
Hierarchies
Housekeeping Columns
Design Principles for the Aggregate Schema
A Separate Star for Each Aggregation
Naming Conventions
Aggregate Dimension Design
Aggregate Fact Table Design
Pre-Joined Aggregate Design
Documenting the Aggregate Schema
Identify Schema Families
Identify Dimensional Conformance
Documenting Aggregate Dimension Tables
Documenting Aggregate Fact Tables
Pre-Joined Aggregates
Materialized Views and Materialized Query Tables
Summary
Using Aggregates
Which Tables to Use
The Schema Design
Relative Size
Aggregate Portfolio and Availability
Requirements for the Aggregate Navigator
Why an Aggregate Navigator?
Two Views and Query Rewrite
Dynamic Availability
Multiple Front Ends
Multiple Back Ends
Evaluating Aggregate Navigators
Front-End Aggregate Navigators
Back-End Aggregate Navigation
Performance Add-On Technologies and OLAP
Specific Solutions
Living with Materialized Views
Living With Materialized Query Tables
Working Without an Aggregate Navigator
Summary
ETL Part 1: Incorporating Aggregates
The Load Process
The Importance of the Load
Tools of the Load
Incremental Loads and Changed Data Identification
The Top-Level Process
Loading the Base Star Schema
Loading Dimension Tables
Loading Fact Tables
Putting It All Together
Loading the Aggregate Schema
Loading Aggregates Separately from Base Schema Tables
Invalid Aggregates
Materialized Views and Materialized Query Tables
Drop and Rebuild Versus Incremental Load
Real-Time Loads
Real-Time Load of the Base Schema
Real-Time Load and Aggregate Tables
Partitioning the Schema
Summary
ETL Part 2: Loading Aggregates
The Source Data for Aggregate Tables
Changed Data Identification
Elimination of Redundant Processing
Ensuring Conformance
Loading the Base Schema and Aggregates Simultaneously
Loading Aggregate Dimensions
Requirements for the Aggregate Dimension Load Process
Extracting and Preparing the Records
Identifying and Processing New Records
Identifying and Processing Type 1 Changes
Processing Type 2 Changes
Key Mapping
Loading Aggregate Fact Tables
Requirements for Loading Aggregate Fact Tables
Acquire Data and Assemble Facts
Identification of Surrogate Keys
Aggregating Over Time
Dropping and Rebuilding Aggregates
Dropping and Rebuilding Aggregate Dimension Tables
Dropping and Rebuilding Aggregate Fact Tables
Pre-Joined Aggregates
Dropping and Rebuilding a Pre-Joined Aggregate
Incrementally Loading a Pre-Joined Aggregate
Materialized Views and Materialized Query Tables
Defining Attributes for Aggregate Dimensions
Optimizing the Hierarchy
Summary
Aggregates and Your Project
Data Warehouse Implementation
Incremental Implementation of the Data Warehouse
Incorporating Aggregates into the Project
The Aggregate Project
Strategy Stage
Design Stage
Build Stage
Deployment
Management of Aggregates
Maintenance Responsibilities
Ad Hoc Changes to Aggregate Portfolio
An Ongoing Process
Summary
Advanced Aggregate Design
Aggregating Facts
Periodic Snapshots Design
Accumulating Snapshots
Factless Fact Tables
Aggregating Dimensions
Transaction Dimensions
Bridge Tables
Core and Custom Stars
Other Schema Types
Snowflakes and Aggregates
Third Normal Form Schemas and Aggregates
Summary
Related Topics
Aggregates and the Archive Strategy
The Data Warehouse Archive Strategy
Aggregates and Archives
Aggregates and Security
Dimensionally Driven Security and Aggregates
Unrestricted Access to Summary Data
Derived Tables
The Merged Fact Table
The Pivoted Fact Table
The Sliced Fact Table
When Rollups Are Deployed Before Detail
Building the Base Table First
Building the Rollup First
Summary