Loading Accumulating Snapshot Fact Tables

Often management looks for bottlenecks in corporate processes so that they can be streamlined or used as a measurement of success for the organization. In order to do achieve these goals we need to measure time between two or more related events. The easiest way to report on this time-series process is to use accumulating snapshot facts.  Accumulating snapshot facts are updatable fact records used to measure time between two or more related events. The most common example of this type of fact can be seen in order processing. Let’s take a look!

Order processing consists on many serialized processes. First there is the order, followed by the product creation or packaging of the items contained within the order. Once the items are packed, they are sent to QA for a double check of the order before sending the package to shipping, where the carrier will pickup and deliver the product to the customer. In this process we have many areas where the process could get held up (abandon cart, missing inventory, slow QA process, carrier not picking up packages on time, etc.), so we measure the time it takes in each stage to ensure our process is optimized.

In the above example I would want to capture the date and time something enters into each new process. Therefore, I might capture the following date information: CART_START_DT, ORDER_DT, PACKAGING_PACK_DT, PACKAGING_QA_DT, SHIPPING_RECEIVED_DT, and CARRIER_PICKUP_DT. Since my OLTP systems capture this data I could load this data directly into an accumulating snapshot fact table but I may lose detail information if the process loops (i.e. QA finds an issue and sends it back to packaging for further processing). Because of this possibility I like to insert this data into a transactional fact table first, then use the transactional fact to load the accumulating snapshot fact table.

Here is how I would design my (factless) transaction fact:

Here is some sample data for this transaction table:

INSERT INTO FACT_ORDER_TRXN VALUES (1, 1, 20190521, 1534);
INSERT INTO FACT_ORDER_TRXN VALUES (1, 2, 20190521, 1541);
INSERT INTO FACT_ORDER_TRXN VALUES (1, 3, 20190521, 1822);
INSERT INTO FACT_ORDER_TRXN VALUES (1, 4, 20190521, 1857);
INSERT INTO FACT_ORDER_TRXN VALUES (1, 3, 20190521, 1907);
INSERT INTO FACT_ORDER_TRXN VALUES (1, 4, 20190521, 1928);
INSERT INTO FACT_ORDER_TRXN VALUES (1, 5, 20190521, 1930);
INSERT INTO FACT_ORDER_TRXN VALUES (1, 6, 20190522, 0917);
COMMIT;

Here is the SQL I would use to load the accumulating snapshot fact from the transaction fact:

Once you have the information loaded into an accumulating snapshot fact you can now quickly find the number of abandoned carts and the average number of minutes to complete each step. You can also create weekly or monthly summary facts from this accumulating snapshot fact so you can see the change over time.

I hope this helps you understand ease in which you can load accumulating snapshot facts and the power of information these facts contain.