Search code examples
data-warehousedimensional-modeling

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 ?

Solution

  • 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