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?

    Freitag, 9. März 2018 12:39

Alle Antworten

  • 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.

    Freitag, 9. März 2018 13:42
  • 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


    • Bearbeitet RichardLees Dienstag, 20. März 2018 01:51 refinement
    Dienstag, 20. März 2018 01:50
  • Grain is not cardinality. While it the same cardinality is an indicator for equal grain, it is not a sufficient criteria.

    Dienstag, 20. März 2018 08:51