Accumulated Snapshot Fact Table

Accumulated Snapshot Fact Table
When creating a model to describe a process that has a clear beginning, end, and phases in between,
the accumulated snapshot fact table may be a good fit. Ralph Kimball has a very nice summary of
this option in his Kimball data warehouse whitepaper titled, "Kimball Design Tip #37: Modeling a
Pipeline with an Accumulating Snapshot" found here. This post describes the characteristics of an
accumulated snapshot fact table at a high level.

Each fact record represents an instance of an entire process such as a sales purchase, insurance
claim, or academic admission. Typically a series of date role play dimensions are part of the design.
The fact record will contain some foreign keys that are required & other foreign keys which will
initially point to an unknown member and will later be updated. Foreign key updates will occur as
information becomes available.
Measures will be updated as data becomes available. Typically, this update occurs at the same
time as the related foreign key updates. For example; a sales order accumulated snapshot fact table
may have a date role play foreign key called FK_PaidDate. There may also be a measure called
PaidAmount. These two values would typically become available at the same time and cause the
fact to be updated.
The fact grain is equal to the fixed foreign keys. For example; FK_Claim would be fixed
(required) in a accumulated snapshot fact table representing insurance claims. A series of

---

FK_Dates (role play) would be updated as the claim moves through the process of being evaluated
and eventually closed. In this case FK_Claim is the grain of the fact table.
To support such a design we need to evaluate a few options for each foreign key. All measures will
be updateable.
Should the foreign key be part of the fact primary key?
Should the foreign key be included in the logic used to determine if a fact exists when
loading?
Should the foreign key be updatable?
If a foreign key needs to be updated, then it cannot participate in the fact table grain. Likewise, the
foreign key cannot be used to determine if a fact already exists when loading the fact table. Finally,
if this is the case the foreign key does need to be updateable.
The following matrix summarizes the foreign key handling decision based on whether the foreign
key affects the grain of the fact table. In our above example FK_Claim would affect the grain, but
FK_PaidDate would not.

Primary Key
Join On
Update
FKey Does Affect Fact Grain
Yes
Yes
No
FKey Does Not Affect Fact Grain No
No
Yes
In summary, accumulated snapshot fact tables should be considered when modeling a process which
includes predefined states. LeapFrogBI greatly simplifies development tasks by providing a single
foreign key definition checkbox option. Checking the box treats the foreign key as required &
uncheck to designate that it is updatable. The rest is handled behind the scenes without further
developer involvement.

---