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.

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 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.

What We Do

We provide SMBs with custom data analytics services.

Built to Spec
We design custom solutions.

Low Cost per User
Empower as many report users as you like.

You Own It
Everything from the data solution to reports is yours to keep.

No upfront costs and no long-term commitment.

Available 24/7
We monitor and maintain everything 7 days a week.

Unlimited access to a team of data experts.

Find out how 
our clients
are getting

10x ROIwithin 6 months


Become Part of the LeapFrogBI Community

Join our newsletter to receive our latest insights and articles on automated, customized reporting in the healthcare industry.


Have any questions? Reach out to us, we would be happy to answer.