CODEWITHSUNDEEP
data-warehousedimensional-modeling
Bazooka
Bazooka

Reputation: 1458

Low cardinality Dimensions in Datawarehouse

I've a bunch of columns in my fact tables that have a very low cardinality (~8). Each of these columns store keys that refer to a master table. I'm wondering whether to import each of these individual master tables as dimension or do I store the values directly in the fact table. Master tables have no additional attributes except the value I'm trying to store. What are the pros and cons of each approach ?

Upvotes: 1

Views: 1848

Answers (1)

Marek Grzenkowicz
Marek Grzenkowicz

Reputation: 17383

This seems to be a classic example of a junk dimension that combines together a number of miscellaneous, low-cardinality flags and indicators (instead of putting each of them in a separate dimension table).

Disadvantages of other approaches:

  • Putting every low cardinality attribute in a separate, dedicated dimension could result in an overly complex model with excessive number of dimension tables (centipede fact tables).
  • Storing the attributes directly in the fact table is allowed but reserved only for degenerate dimensions, i.e. values like order or invoice numbers, retail point-of-sale transaction numbers - high-cardinality values that don't have any additional attributes describing them.
    Low-cardinality flags are not DDs, because even though they may consist of a sole key now, they may easily have additional attributes in the future, e.g. multiple descriptive captions for reports - short for mobile users and long for desktop users.

Details: Design Tip #113 Creating, Using, and Maintaining Junk Dimensions

Upvotes: 2

Related Questions