We previously encountered materialized views in “Materializing and Updating Timelines”: in a relational data model, they are table-like objects whose contents are the results of a query. A materialized view is an actual copy of the query results, written to disk, whereas a virtual view is just a shortcut for writing queries. When you read from a virtual view, the SQL engine expands it into the view’s underlying query on the fly and then processes the expanded query.
When the underlying data changes, a materialized view needs to be updated accordingly. Some databases can do that automatically, and there are also systems such as Materialize that specialize in materialized view maintenance. We will return to this topic in “Maintaining materialized views”. Performing such updates means more work on writes, but materialized views can improve read performance in workloads that repeatedly need to perform the same queries.
Materialized aggregates are a type of materialized view that can be useful in data warehouses. As discussed earlier, data warehouse queries often involve an aggregate function, such as COUNT, SUM, AVG, MIN, or MAX in SQL. If the same aggregates are used by many queries, crunching through the raw data every time can be wasteful. Why not cache some of the counts or sums that queries use most often? A data cube (also known as an OLAP cube) does this by creating a grid of aggregates grouped by different dimensions. Figure 4-10 shows an example.
Diagram showing a two-dimensional data cube with date keys along one axis and product keys along the other, illustrating the aggregation of sales data by summing net prices across various combinations.
Figure 4-10. Two dimensions of a data cube, aggregating data by summing
Imagine for now that each fact has foreign keys to only two dimension tables; in Figure 4-10, these are date_key and product_sk. You can now draw a two-dimensional table, with dates along one axis and products along the other. Each cell contains the aggregate (e.g., SUM) of an attribute (e.g., net_price) of all facts with that date–product combination. Then, you can apply the same aggregate along each row or column and get a summary that has been reduced by one dimension (the sales by product regardless of date, or the sales by date regardless of product).
In general, facts often have more than two dimensions. In Figure 3-5, there are five dimensions: date, product, store, promotion, and customer. It’s a lot harder to imagine what a five-dimensional hypercube would look like, but the principle remains the same: each cell contains the sales for a particular date–product–store–promotion–customer combination. These values can then repeatedly be summarized along each of the dimensions.
The advantage of a materialized data cube is that certain queries become very fast because they have effectively been precomputed. For example, if you want to know the total sales per store yesterday, you just need to look at the totals along the appropriate dimension—no need to scan millions of rows.
The disadvantage is that a data cube doesn’t have the same flexibility as querying the raw data. For example, there is no way of calculating which proportion of sales comes from items that cost more than $100, because price isn’t one of the dimensions. Most data warehouses therefore try to keep as much raw data as possible and use aggregates such as data cubes only as a performance boost for certain queries.