Facts with different grain
3 posters
Page 1 of 1
Facts with different grain
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!
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
Re: Facts with different grain
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
Re: Facts with different grain
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Facts with different grain
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.
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
Re: Facts with different grain
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.
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.
Re: Facts with different grain
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
Re: Facts with different grain
Using it wrong and getting bad results pretty much sums up the primary reasons why you don't mix grains.
Similar topics
» Identify the facts and facts grain
» Facts with same grain: some shared and some different measures
» Facts with different grain from different sources but related
» Modeling forecast at a different grain the facts
» How to model Facts with Conformed Dims on different grain level in BO XI universe
» Facts with same grain: some shared and some different measures
» Facts with different grain from different sources but related
» Modeling forecast at a different grain the facts
» How to model Facts with Conformed Dims on different grain level in BO XI universe
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum