Facts with different grain

View previous topic View next topic Go down

Facts with different grain

Post  Guest on Mon Apr 30, 2012 11:15 am

My understanding of a fact table is that it collects facts of the same grain. We currently have a main fact table and a second that differs from the first by just one additional foreign key. We will probably have reports that don't need the additional dimension but need facts from both tables.
I can think of several possible ways to deal with this situation in the star model, e.g.
1. combining both fact tables in one report (so it won't be a simple star join),
2. aggregation/copying the facts from the second fact table into the first (redundancy),
3. having facts with different grain in one fact table (some records will have one empty foreign key),
...
What would be the usual way to solve this? Are there good arguments against any one of those options? Thanks for sharing your experiences!

Guest
Guest


Back to top Go down

Re: Facts with different grain

Post  TheNJDevil on Mon Apr 30, 2012 11:30 am

I would usually go with #3 except instead of empty foreign key, use something in the dimension that is a little more meaningful.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Facts with different grain

Post  BoxesAndLines on Mon Apr 30, 2012 3:43 pm

Adding a dimension doesn't necessarily affect the grain. Do both fact tables have the same row count? If so, just set the inapplicable dimension to the default value. Otherwise, create an aggregate fact table which is what it seems like you've done.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Facts with different grain

Post  Guest on Wed May 02, 2012 10:57 am

Thanks for your comments. Yes, the new dimension does affect the grain. Here's a simple example to illustrate the situation.
Suppose we have a database collecting football data. Most facts are collected, say, per team and matchday, such as points gained, gate money, matches won..., but in some (rare) cases, we'd also like to capture facts per player, e.g. goals scored. The standard query would list points and goals per team with no reference to players. (this is not our example but one that I hope is easier to understand)
#3 would result in some (most) facts having no "player" foreign key.

Guest
Guest


Back to top Go down

Re: Facts with different grain

Post  ngalemmo on Wed May 02, 2012 11:40 am

If the new dimension affects the grain, then option 3 is out. NEVER create a fact table with multiple grains.

If there is utility in doing so, creating an aggregate fact, with the higher grain facts summarized to the lower grain of the aggregation, is more desirable than performing a fact join. It performs better and queries are simpler. But if creation of an aggregation cannot be justified based on usage and value, joining facts is a viable option.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Facts with different grain

Post  Guest on Wed May 09, 2012 11:43 am

Thanks, that answers my question. I can think of a couple of reasons for "NEVER" mixing grain, e.g. that the result will be harder to understand and that users might attempt to use the fact table in a wrong way. Are there more fundamental reasons?

Guest
Guest


Back to top Go down

Re: Facts with different grain

Post  ngalemmo on Wed May 09, 2012 12:41 pm

Using it wrong and getting bad results pretty much sums up the primary reasons why you don't mix grains.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Facts with different 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