Identify the facts and facts grain
3 posters
Page 1 of 1
Identify the facts and facts grain
I'm modeling a DWH for a web forum, but have some difficulties identifying the facts and their grain.
As on a typical forum the User can post a topic, other users can comment it, can vote for/against, can just view the topic etc. The measures are simply counting the posts, comments, visitors, votes given/received etc.
Firstly I thought making a post or commenting is the fact, but there isn't any measure attached to this, so I begin to think that Posts and Comments are like dimensions and the real facts are page hit, voting, but in this case Post and Comments dimensions will be grow large.
My question is what are the facts/grain here? Anyone with similar experience?
As on a typical forum the User can post a topic, other users can comment it, can vote for/against, can just view the topic etc. The measures are simply counting the posts, comments, visitors, votes given/received etc.
Firstly I thought making a post or commenting is the fact, but there isn't any measure attached to this, so I begin to think that Posts and Comments are like dimensions and the real facts are page hit, voting, but in this case Post and Comments dimensions will be grow large.
My question is what are the facts/grain here? Anyone with similar experience?
veskojl- Posts : 11
Join date : 2011-07-21
Re: Identify the facts and facts grain
veskojl wrote: The measures are simply counting the posts, comments, visitors, votes given/received etc.
You answered your own question.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Identify the facts and facts grain
It's not so obvious for me whether posting, commenting, voting, viewing are from the same grain and which of them are facts and which could be used as dimensions - post, comment, vote, view.
veskojl- Posts : 11
Join date : 2011-07-21
Re: Identify the facts and facts grain
I don't know the business requirements of what you are trying to accomplish, but based on a forum this is what I see.
Posting - Factless fact
Member - Dimension
Forum - Dimension
Date - Dimension
Is a comment not a post? If not, how is it different?
Viewing is a different fact that posting as is voting but would share the same dimensions as above.
For each posting insert one row into the Posting fact. There is only 1 default metric which you should set to 1 for all rows.
What do you vote on, a post or a thread? Same for question for view.
Posting - Factless fact
Member - Dimension
Forum - Dimension
Date - Dimension
Is a comment not a post? If not, how is it different?
Viewing is a different fact that posting as is voting but would share the same dimensions as above.
For each posting insert one row into the Posting fact. There is only 1 default metric which you should set to 1 for all rows.
What do you vote on, a post or a thread? Same for question for view.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Identify the facts and facts grain
As you know comments are always attached to some post and you can have posts without any comments. Things are complicated by the fact you can vote on post/comment/poll. I was thinking of allocating post->comment->vote, so the grain will be one row per vote;comment;post;user;date;forum, but what will happen with the post without votes/comments?
Maybe several facts are the key here, but it's hard for me to identify them.
Thanks for your thoughts , BoxesAndLines.
Maybe several facts are the key here, but it's hard for me to identify them.
Thanks for your thoughts , BoxesAndLines.
veskojl- Posts : 11
Join date : 2011-07-21
Re: Identify the facts and facts grain
Dimensionality drives the number of fact tables. If you define the grain as some action, then you can do it in one fact. Actions such as: user creates a new thread (posts), user responds to a post (comment), user votes on a topic: then your dimensions are date/timestamp, user, thread/topic, and action (post, comment or vote). As these are events, it is common to not have measures.
Similar topics
» How to model Facts with Conformed Dims on different grain level in BO XI universe
» Facts with different grain
» Facts with different grain from different sources but related
» Modeling forecast at a different grain the facts
» Facts with same grain: some shared and some different measures
» Facts with different grain
» Facts with different grain from different sources but related
» Modeling forecast at a different grain the facts
» Facts with same grain: some shared and some different measures
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|