1

I always wonder if the FactInternetSale table of the AdventureworksDW is a accumulating snapshot table. It has a ShipDateKey in it.

enter image description here

According to the AdventureWorks OLTP documentation, it says that the ShipDate of the SalesOrderHeader is the date that "the order was shipped to customer". I interpret this line as, when the order is shipped, the ship date will be updated.

enter image description here

That also means the rows in the DW FactInternetSale will also need to be updated as well. The ship date marks the an important milestone of an order and this is clearly the behavior of an accumulating snapshot fact table.

So should this table be considered an accumulating snapshot fact table? If so then is there any problem that there is no real transaction fact table?

In the Kimball's data warehouse toolkit book, in this kind of problem, he separates the Order transaction fact table and the Shipping Fact table very strictly, with the Order Transaction Fact table only contains only the information which is recorded when the order is made, and will not be updated. The dates in the Order Transaction Fact table are always expected date, not the real date. The shipping fact table contains the true ship date of an item. After that there is an accumulating snapshot fact table that contains all the important milestones of an order. Not only the ship date, but also other important milestones... By having dates of important milestones, we of course can know the current status of the order.

In my personal opinion, I consider that the Order Fact Table that does not contain the current status of it is totally useless. What is the point of knowing the total amount of orders but cannot know how much is from fulfilled (shipped) ones and how much is from unfulfilled ones? In my experience, users (data analysts) will always just use the accumulating snapshot table to do their job all the time, as the search predicate of "current status" is never absent in their query.

In my real world, I usually design this Order (information) fact table as a accumulating snapshot straightforwardly, skipping the transaction fact table (like what Kimball does, strictly separates things), as I feel that is very time-consuming and have no use. The transaction fact tables are usually just the actions done on the order (for example: shipping).

How do you think about this?

Pblade
  • 133
  • 1
  • 9

1 Answers1

0

No, it's not an accumulating snapshot fact table

NickW
  • 8,430
  • 2
  • 6
  • 19
  • Can you explain why? I just read the description from Microsoft again, and it seems that there is only "shipped" order in that table. If so, it means that the data will go to table only if it has reached the "cool state", and it is written only one, never updated, so it should be a transaction fact table. It is only that it contain some date of important mile stone. But in reality, the organization tends to demand the data come as fast as possible, so the milestone date will not immediately available but need to updated later, I consider this clearly the behaviour of accumulating snapshot fact – Pblade Jun 28 '21 at 04:58
  • AdventureWorksDW is not a real-world implementation for an actual business, it is a constructed example to demonstrate MS features/capabilities. You seem to be over-thinking things – NickW Jun 28 '21 at 06:40