Two Facts or one Fact


  • Hey guys
    I am very new to DW things, so I am very confused what the differences are between 1 fact or 2 facts. I have 1 fact table (ReviewRating) and probably another fact (ReviewSentimentScore). But is it not better to combine these and make 1 fact in terms of performance?

    Friday, March 9, 2018 12:39 PM

All replies

  • Without knowing your entire domain, it is impossible to guess.  It depends on many variables.  Either way would likely work.

    The questions you need to ask:

    • Are they the same grain?
    • ReviewSentimentScore does not have meaning without joining to ReviewRating?
    • If there a 1-1 relationship between ReviewRating and ReviewSentimentScore?

    If those are all true, then you can likely combine them into a single fact table.  If any of them are false, then you must have 2 fact tables.

    Friday, March 9, 2018 1:42 PM
  • The simple answer is If the two fact tables have exactly the same cardinality (exactly the same combination of dimension keys) then they would probably suit one fact table (measure group).

    Having said that, there are a few deviations from this, such as if one was much smaller than the other and/or required much greater performance, you might consider splitting them into two facts. That way, one will be cached without the other soaking up space and you can have different aggregations, update schedules etc.

    Hope that helps


    • Edited by RichardLees Tuesday, March 20, 2018 1:51 AM refinement
    Tuesday, March 20, 2018 1:50 AM
  • Grain is not cardinality. While it the same cardinality is an indicator for equal grain, it is not a sufficient criteria.

    Tuesday, March 20, 2018 8:51 AM