Search code examples
data-warehousebusiness-intelligence

Dimension Tables: is it worth creating a dimension with only 2 values?


Is it worth creating a dimension that has only 2 values?

The values are literally "Yes" and "No".

Or should I combine it with other values and turn it into a junk dimension?

Edit:

Not sure why this was closed as it's not opinion based, it's based on the number of records, etc.

Query performance does not seem to be much impacted if you are joining with a table that has just a couple rows, but rather than having a bunch of joins with tables that will likely contain no more than 2-3 values, it's better to just create a junk dimension and do 1 join.

Seems the best solution for my problem was to just turn it into a junk dimension.


Solution

  • Short answer, Yes. It is worth creating a dimension containing all the possible values for an attribute.

    The purpose of a junk dimension is to combine those very tiny dimensions into a larger table to eliminate unnecessary joins. Even if this single Yes/No value is the only attribute with such a small set, it is still worth creating a junk dimension because...

    Warehouses grow. You will eventually find other attributes that are great candidates for including in the junk dimension (sex/gender, marital status, deceased, etc). Start your warehouse on a good path by doing it right the first time.

    It might seem like overkill, but the system doesn't care much about joining to a two row table. It is much easier to add new attributes to an existing junk dimension versus rewriting your architecture when needed later. Also, once you publish the design for use, your users will have queries leveraging those attributes. That makes change extremely difficult, if not impossible.