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.