Dimension or Degenerate Dimension

data-warehousedatabase-designdimensional-modeling

I am taking a course on data warehousing. Currently, I am reading through Ralph Kimball's Data Warehouse Toolkit.

I understand that a degenerate dimension is a dimension key that is stored in a fact table. It does not join to a corresponding dimension table because all of its attributes have already been placed in other analytical dimensions, thus eliminating the need to join to another dimension table.

What I do not understand is when I should make something into a Degenerate Dimension as opposed to having it as a "normal" dimension and linking it to the fact table via a Foreign Key?

Best Answer

These are the hints:

  • The dimension consists only of natural key (e.g. invoice's or order's number) and contains no other attributes (because they've been placed in dimensions of their own).

  • The dimension table grows proportionately with the fact table, e.g. every time you insert new order lines into the fact table, you have to insert new OrderNumber dimension rows.

  • You never use the dimension to filter data when populating a report and it makes no sense to use its values as a row header.

Read Design Tip #46: Another Look At Degenerate Dimensions from the Kimball Group

Related Question