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

228
Chapter 8
How Many
Fact Table Types
There are three fact
table types. They
vary in how they
represent time
Table 8-1
Fact table types
Facts are stored in three types of fact table: transaction fact tables, periodic snap-
shots, and accumulating snapshots that correspond to the three event story types:
discrete, recurring, and evolving. Table 8-1 shows how each type represents time,
and how it is maintained by ETL.
F ACT T ABLE
T YPE BEAM✲ S TORY
C ODE
T YPE T IME T IME
D IMENSION ( S )
Transaction
fact table [TF] Discrete Point in time
or
short interval Transaction date Insert
(and time)
Periodic
snapshot [PS] Recurring Regular
predictable
interval Period
(e.g., Month) or
period end date
(and time)
Accumulating
snapshot [AS] Evolving Multiple mile-
Insert and
stone dates (and update
times)
Irregular
unpredictable
longer interval
ETL
P ROCESSING
Insert
(and update
if period-to-
date)
Transaction Fact Table
Transaction fact
tables store point
in time or short
duration facts
Figure 8-1
Transaction fact
table
Transaction fact (TF) tables are used to store point-in-time events, such as retail
sales purchases, or short duration events, such as phone calls, that are completed
by the time they are loaded into the data warehouse. These discrete events are the
atomic-level details of business processes—the individual transactions captured by
the operational system. Point in time facts have a single time dimension represent-
ing when the facts occurred. For short duration facts, the time dimension usually
represents start time and can be accompanied by a second end time dimension, or
simply a duration fact, if end time will not be used for grouping or filtering. If date
and time of day are significant, each logical time dimension will be split into
physical CALENDAR and CLOCK dimensions as described in Chapter 7. Figure 8-
1 shows a BEAM ✲ transaction fact table SALES FACT [TF] with a granularity of
receipt line item: one record for each different product on a customer’s sales
receipt.