Underlying fact for accumulating snapshot

View previous topic View next topic Go down

Underlying fact for accumulating snapshot

Post  xshay on Wed Jul 06, 2011 1:33 am

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

xshay

Posts : 2
Join date : 2011-07-06

View user profile

Back to top Go down

Re: Underlying fact for accumulating snapshot

Post  Dave Jermy on Wed Jul 06, 2011 7:00 am

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.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Underlying fact for accumulating snapshot

Post  xshay on Wed Jul 06, 2011 7:21 pm

"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.

xshay

Posts : 2
Join date : 2011-07-06

View user profile

Back to top Go down

Re: Underlying fact for accumulating snapshot

Post  ngalemmo on Thu Jul 07, 2011 3:17 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Underlying fact for accumulating snapshot

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum