4.1 Summary tables

A common technique used in data warehouses is to precompute and store results of frequent queries. This is especially beneficial when the queries involve aggregation, because the result is usually much smaller than the detailed data used to produce the result. An example of such a query is a monthly sales report for the business. As multiple users are interested in the total sales of each product for each month, the data would be selected, joined, sorted, and aggregated over and over again for each user. Rather than wasting resources reexecuting the same query repeatedly, the result could be precomputed and saved in a table. Such precomputed results are often called summaries or summary tables.

Table 4.1 shows a summary containing the total number of items sold for each month of the year. Summary tables are usually much smaller than the tables containing the detail data. In this example, 17 rows of detailed sales transaction data is summarized into 10 rows. Depending on the data, the reduction in size and therefore the improvement in query performance can be quite significant.