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

260
Chapter 8
How Many
Summary
Transaction fact (TF) tables record the atomic-level, point-in-time facts associated with discrete
events.
Periodic snapshots (PS) provide additional atomic-level facts by sampling continuous business
processes and new aggregated facts by summarizing atomic transactional facts at regular
intervals.
Accumulating snapshots (AS) bring together the milestone events of a business process and
combine their transactional facts to provide additional performance measures
Both periodic and accumulating snapshots provide high performance access to measures that
would be impossible or impractical to calculate at query time, from atomic transaction fact
tables alone.
Apart from its type, the most important definition of a fact table is its granularity, which must
precisely state, in business terms or dimensionally (GD), the meaning and uniqueness of each
fact table row.
Event timelines are used to visually model the milestone events and duration measures of
evolving events that can be implemented as accumulating snapshots.
Accumulating snapshots that need to be sourced from multiple operational systems or contain
repeating milestones (with 1:M or M:M relationships) should be developed incrementally—by
first implementing transaction fact tables for their individual milestone events.
Fact additivity describes any restrictions on how a fact can be summed to produce a meaningful
value. Fully additive (FA) facts can be summed with no restrictions, using any combination of
available dimensions. Semi-additive (SA) facts must not be summed across their non-additive
(NA) dimension(s). Non-additive (NA) facts must not be summed.
Fact tables can be optimized by appropriate downsizing, indexing, partitioning and aggregation.
Cross-process analysis should be handled by drilling-across multiple fact tables one at a time
using multi-pass SQL or by building derived fact (DF) tables that merge commonly compared
fact tables.