My first Publication Agile-Data-Warehouse-Design-eBook | 页面 251

Design Patterns for High Performance Fact Tables and Flexible Measures
231
Consequences
Many end-of-period measures are complex and time consuming to calculate from
raw transactional facts. If the necessary measures are already available from a
reliable operational system it is often better to load a periodic snapshot directly
from an additional source rather than attempt to reproduce the operational busi-
ness logic with ETL processing by loading from a transaction fact table.
For case studies describing the use of periodic snapshots, see:
Data Warehouse Design Solutions, Christopher Adamson, Michael Venerable
(Wiley, 1998) Chapter 6, “Inventory and Capacity”, and Chapter 8, “Budgets and
Spends”
The Data Warehouse Toolkit, Second Edition, Ralph Kimball, Margy Ross (Wiley,
2002) Chapter 3, “Inventory”, and Chapter 15, “Insurance”
Accumulating Snapshots
Accumulating snapshots (AS) are used to store evolving events: longer running
events that represent business processes with multiple milestone dates and facts
that change over time. They are so named because each evolving event accumulates
additional fact and dimension information over time, typically taking days, weeks,
or months to become complete. Accumulating
Unlike transaction fact tables, and most periodic snapshots, accumulating snap-
shots are designed specifically to be updated. Facts are inserted into an accumulat-
ing snapshot shortly after events begin and are updated whenever event statuses
change. This leaves the fact table containing the final status of every completed
event and the current status of all open events. Accumulating
Figure 8-3 shows an accumulating snapshot for library book lending. It contains
examples of books that have been borrowed and returned (completed events),
books that are overdue (evolved events), and books that just been borrowed (new
events). LENDING FACT has multiple time dimensions—like all accumulating
snapshots—representing the milestones that a book loan can go through. Only two
of these (LOAN DATE and DUE DATE) are available when a loan is created. Accumulating
snapshots store
evolving events
snapshots are
updated with
ongoing event
activity
snapshots have
multiple milestone
time dimensions
Figure 8-3
Accumulating
snapshot fact table