Facts with different grain

[Guest]

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!

[TheNJDevil]

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

[BoxesAndLines]

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.

[Guest]

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.

[ngalemmo]

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.

[Guest]

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?

[ngalemmo]

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

[Sponsored content]