Today we will look at a different type of fact table, the accumulating fact.
Accumulating Fact Tables
Accumulating Fact tables are used to show the activity of progress through a well-defined business process and are most often used to research the time between milestones. These fact tables are updated as the business process unfolds and each milestone is completed. The most common examples can be found in order and insurance processing. Here a single fact table may contain the important business processes milestones upon which the organization wishes to measure performance.
A different version of this same accumulating fact table may include the calculated lag time between the milestone dates as shown below.
The lag (number of days) can be calculated from the claim date (total time to complete the milestone) or the lag can be calculated as the number of days since the last milestone was completed (days spent in this stage). The important part is to be consistent in calculating the lag within the snapshot fact. Capturing of this lag data can make analysis on this process easier.
There are other options you could use instead of an accumulating snapshot fact table to capture this information.
Remember that Accumulating snapshot tables are helpful when:
Finally, although you are capturing this information in an accumulating fact table you may want to also capture this information in a dimension as a type 2 SCD.