What is a degenerate dimension?
A degenerate dimension is dimension without attributes. It is a transaction-based number which resides in the fact table. There may be more than one degenerate dimension inside a fact table.
According to Ralph Kimball, in a data warehouse, a degenerate dimension is a dimension which is derived from the fact table and doesn’t have its own dimension table. Degenerate dimensions are often used when a fact table’s grain represents transactional level data and one wish to maintain system specific identifiers such as order numbers, invoice numbers (Bill No) and the like without forcing their inclusion in their own dimension.
A degenerate dimension is data that is dimensional in nature but stored in a fact table.
OLTP transaction numbers, such as bill numbers, courier tracking #, order number, invoice number, application received acknowledgement, and ticket number, usually produce dimensions without any attributes and are represented as degenerate dimensions in the fact table.
It would be correct to say that all information that the Bill Number# represents is stored in all other dimensions. Therefore, Bill Number# dimension has no attributes of its own; and therefore it cannot be made a separate dimension.
The Bill Number# should be placed inside the fact table right after the dimensional foreign keys and right before the numeric facts.
The best way to identify a missing or a badly designed degenerate dimension is to review your dimensional design and look for any dimension table that has equal or nearly the same number of rows as the fact table.
In other words, if, for every row that you insert in the fact table you also have to pre-insert another row in any other dimension table, then you have missed a degenerate dimension.
Posts
