Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

1.1. Star Schema Basics

A star schema is a set of tables in a relational database that has been designed according to the principles of dimensional modeling. Ralph Kimball popularized this approach to data warehouse design in the 1990s. Through his work and writings, Kimball established standard terminology and best practices that are now used around the world to design and build data warehouse systems. With coauthor Margy Ross, he provides a detailed treatment of these principles in The Data Warehouse Toolkit, Second Edition (Wiley, 2002).

To follow the examples throughout this book, you must understand the fundamental principles of dimensional modeling. In particular, the reader must have a basic grasp of the following concepts:

• The differences between data warehouse systems and operational systems

• How facts and dimensions support the measurement of a business process

• The tables of a star schema (fact tables and dimension tables) and their purposes

• The purpose of surrogate keys in dimension tables

• The grain of a fact table

• The additivity of facts

• How a star schema is queried

• Drilling across multiple fact tables

• Conformed dimensions and the warehouse bus

• The basic architecture of a data warehouse, including ETL software and BI software

If you are familiar with these topics, you may wish to skip to the section "Invisible Aggregates," later in this chapter.

For everyone else, this section will bring you up-to-speed. Although not a substitute for Kimball and Ross's book, this overview provides ...