Identify the facts and facts grain

View previous topic View next topic Go down

Identify the facts and facts grain

Post  veskojl on Wed Mar 28, 2012 7:55 am

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?

veskojl

Posts : 11
Join date : 2011-07-21

View user profile

Back to top Go down

Re: Identify the facts and facts grain

Post  BoxesAndLines on Wed Mar 28, 2012 9:43 am

veskojl wrote: The measures are simply counting the posts, comments, visitors, votes given/received etc.

You answered your own question.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Identify the facts and facts grain

Post  veskojl on Wed Mar 28, 2012 11:25 am

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

View user profile

Back to top Go down

Re: Identify the facts and facts grain

Post  BoxesAndLines on Wed Mar 28, 2012 11:42 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Identify the facts and facts grain

Post  veskojl on Wed Mar 28, 2012 12:11 pm

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.

veskojl

Posts : 11
Join date : 2011-07-21

View user profile

Back to top Go down

Re: Identify the facts and facts grain

Post  ngalemmo on Wed Mar 28, 2012 2:46 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Identify the facts and facts grain

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