Underlying fact for accumulating snapshot

Hello,

I'm after an accumulating snapshot fact like the following to report on how many articles in what state an author has:

Author: Bob
Draft Count: 2
Published Count: 10
Taken Down Count: 1

Since this is a summary table, it should ideally be built on top of transaction fact tables.

What are the ideal underlying facts in this case? I have come up with two options:

1) There can be one fact per "state":

* draft_creation
- content_id/author_id/other dimensions
- timestamp
* first_publish
* take_down

2) Or one fact to capture all state changes:

content_state_change
- content_id/author_id/other dimensions
- state
- timestamp

I'm not sure how to evaluate the merits of either approach. Any suggestions?

Cheers,
Xavier

What is the time series on your summary fact table? Do you want an accumulating snapshot that (usually) records significant milestones in the life cycle of a particular process (in this case, they could be date first draft received, date first published, date last published etc. plus various counts representing current totals). Or, do you actually want a monthly (say) periodic snapshot that gives you the number of draft, published and taken down articles in that month and further measures for ytd or lifetime counts. The business requirements should be able to guide you in this area - e.g. do users want to be able to compare the number of articles of a particular type published this month against the same month last year?

With regards to the transaction fact table, your first example is an accumulating snapshot, as it contains multiple dates and the same row would be updated as new transactions come in. So, I would go for something like your second example; a fact table that records each change in state of an article.

"in this case, they could be date first draft received, date first published, date last published etc. plus various counts representing current totals"

This one. We don't need to compare to past data, so a periodic snapshot is not required.

"as it contains multiple dates and the same row would be updated as new transactions come in"

I must not have communicated it well, because this isn't the case. Draft creation, first publish, and take down are three *separate* facts, each with a content_id, author_id, and timestamp. There will only be 0 or 1 row for every article.

Either would work but I tend to prefer option 2, a row for each event. It is easier to maintain, since you only insert rows and easy enough to work with when building an aggregate.