Breakdown of an 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 whitepaper titled, ”Kimball Design Tip #37: Modeling A Pipeline With An Accumulating Snapshot”. 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.
Wrapping Up
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.
In summary, accumulated snapshot fact tables should be considered when modeling a process which includes predefined states. LeapFrogBI’s DWA platform 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.
