1

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?

1

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

0

My understanding is you use a Degenerate Dimension when it has an outside reference to something that people may want to search on, which could be an operational system or outside the organisation reference. It is useful for the business people to see, usually because it links to something they know (another system or outside reference), so it is included. It could also be something that enables them to measure something that other measures do not allow. A good example of this is the idea of a basket in retail - business people are interested in what items are purchased together, which is hard to find out otherwise.

When should you make one? When in consultation with the business people who will be using the data warehouse, they tell you that they will need an extra code or an extra piece of information for them to be able to query, which appears extraneous to you and doesn't fit into the dimensions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.