My first Publication Agile-Data-Warehouse-Design-eBook | Page 273

Design Patterns for High Performance Fact Tables and Flexible Measures
253
Lost dimension aggregates are the easiest aggregate type to build, because no
dimensional joins are needed; for example, a lost aggregate can be built by:
CREATE MATERIALIZED VIEW Daily_Product_Sales AS
SELECT Date_Key, Product_Key, SUM(Revenue)
FROM Sales_Fact
GROUP BY Date_Key, Product_Key
At least one of the lost dimensions must be a granularity dimension (GD)—part of
the fact table granularity—for the aggregate table to be smaller than its base fact
table. Choose which dimensions to drop wisely, so that the aggregate is suffi-
ciently smaller but can still be used to answer a broad range of queries.
Shrunken Dimension Aggregate Pattern
Shrunken dimension aggregates are created by summarizing a fact table using one
or more shrunken or rollup (RU) dimensions instead of the base dimensions.
Figure 8-15 shows a shrunken dimension aggregate that was formed by rolling up
Dates into Months, and Stores into Regions.
Shrunken
aggregates use
rollup dimensions
Figure 8-15
Shrunken
dimension
aggregate
Notice that the Customer dimension has been dropped. This is not uncommon for
shrunken dimension aggregates—because dropping the most granular dimension
is often needed to significantly reduce the aggregate’s size. Sales by Month, Region,
Product, and Customer would contain nearly as many rows as the base fact table—
thereby negating its performance benefits.
Aggregates can
shrink and lose
dimensions
Shrunken dimension aggregates are more complex to build, requiring additional
rollup dimensions and dimensional joins, and more difficult to maintain using
incremental refresh. But they can be designed to satisfy a broad range of queries.
Materialized views can be used to build aggregates and their matching rollup
dimensions. When building rollup dimensions, you can reuse their base dimension
key to create rollup keys if you carefully select the first or last key value that
matches the rollup dimension granularity. For example, you can use the DATE
KEY of the last day of each month as the MONTH Dimension’s MONTH KEY, or
use the STORE KEY of the first store in a region as the REGION Dimension’s
REGION KEY. The actual surrogate key value selected does not matter, as long as
it is used consistently in the rollup dimension.
Materialized views
can be used to build
shrunken
aggregates and
their matching rollup
dimensions