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

Design Patterns for High Performance Fact Tables and Flexible Measures
247
Figure 8-11
Exclusive facts
Solution
Limit MONTHLY ACCOUNT SNAPSHOT to the common facts (and possibly a
few frequently used specialist facts like INTEREST CHARGED and CHARGES)
and create a small set of custom fact tables, one for each major product family
based on the exclusive fact sets as in Figure 8-11. The core fact table will contain a
row for every account each month, and the custom fact tables—such as
MONTHLY CHECKING FACT and MONTHLY MORTGAGE FACT shown in
Figure 8-12—would contain rows for their account types only. The custom fact
tables will contain the common facts, too—so that BI users don’t have to query
multiple fact tables.
Create a core fact
table for cross-
product analysis
and custom fact
tables for each
exclusive fact set
Figure 8-12
Core and custom
fact tables for
exclusive facts