Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

8.1. Aggregating Facts

Up to this point, all fact table designs discussed have fallen into a single category: the transaction fact table. There are two other types of fact tables to consider: those based on a periodic snapshot design and those based on an accumulating snapshot design. Each bears unique consideration when it comes to the construction of aggregates. In addition, this chapter will consider how to handle base schema designs in which the fact table contains no facts at all.

8.1.1. Periodic Snapshots Designs

The periodic snapshot is a fact table design technique that captures the status of a business process at fixed intervals in time. This technique allows analysis of facts that would otherwise be difficult or time consuming to compute. Periodic snapshots typically include at least one fact that is semi-additive; its values cannot be added together across time periods. Dimensional aggregates involving semi-additive facts must not summarize the fact across the non-additive dimension. Schema designs may average the semi-additive fact across periods or sample status at longer intervals, but these derived schemas cannot be further summarized.

8.1.1.1. Transactions

The base schema designs studied to this point contain transaction fact tables. A transaction fact table models a business process as a series of events or activities. Each time the event occurs, a row is added to the transaction fact table. Dimensions associated with the row provide the contextual detail of ...