[e]

Reporting Flows From States

Here's the problem: you have a snapshot fact table, and you want to report flows. The snapshot records the status and activity of each item each day, and you want to report status-changes, so it should be straight-forward query to compare today's status and yesterday's status. But not so simple...

 
• Two status changes in the same day will not be recorded anywhere in your snapshot table.
   
• Joining two days in a snapshot table makes a big ugly join: fact->dimension->fact; and if the dimension is slowly-changing (type-2), then the join is: fact->dimension->dimension->fact.
   
• Sometimes you want to report how many items entered a certain state for the first time; to do this from the snapshot table requires digging through the entire history.
  

Alternatives:

(1) Join items across days, look for status changes. (Doesn't scale?)

(2) Compare gross amounts. If a state change is irreversible, then you can compare today's total to yesterday's total to find the increase.

(3) Store events in the DB.