Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

1.3. Other Types of Summarization

The aggregate tables discussed thus far provide the same results as the base schema, and are leveraged by substituting the names of aggregate tables in SQL queries. Throughout this book, the term aggregate will be reserved for tables that exhibit these characteristics, even if an aggregate navigator is not deployed.

However, there are other ways to summarize the information in a star schema. Although not meeting the requirements for invisibility, these summary tables may provide value to the data warehouse in other ways.

1.3.1. Pre-Joined Aggregates

Like the dimensional aggregates you have seen so far, a pre-joined aggregate summarizes a fact across a set of dimension values. But unlike the aggregate star schemas from Figures 1.8 and 1.9, the pre-joined aggregate places the results in a single table. By doing so, the pre-joined aggregate eliminates the need for the RDBMS to perform a join operation at query time.

An example of a pre-joined aggregate appears in Figure 1.11. This table collects some dimensional attributes from Product, Day, and Salesperson, placing them in a single table with the facts from order_facts.

Like the aggregate schemas from the previous section, this pre-joined aggregate will improve the performance of our sample query by reducing the number of rows that must be consulted. While it also eliminates join processing requirements, this effect is less dramatic. All the major relational databases in use today have native support ...