Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Identify the facts and facts grain

3 posters

Go down

Identify the facts and facts grain Empty Identify the facts and facts grain

Post  veskojl 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

Back to top Go down

Identify the facts and facts grain Empty Re: Identify the facts and facts grain

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

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

Back to top Go down

Identify the facts and facts grain Empty Re: Identify the facts and facts grain

Post  veskojl 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

Back to top Go down

Identify the facts and facts grain Empty Re: Identify the facts and facts grain

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

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

Back to top Go down

Identify the facts and facts grain Empty Re: Identify the facts and facts grain

Post  veskojl 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

Back to top Go down

Identify the facts and facts grain Empty Re: Identify the facts and facts grain

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

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

http://aginity.com

Back to top Go down

Identify the facts and facts grain Empty Re: Identify the facts and facts grain

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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