Degenerate dimension

What is a degenerate dimension?

A degenerate dimension is a dimension key that stays in the fact table without a separate dimension table. Classic examples are order number, invoice number, claim number, ticket number, or transaction ID.

The term comes from Kimball-style dimensional modelling. The dimension has degenerated down to a key: all the descriptive attributes that would normally belong in a dimension are already captured by other dimensions such as customer, product, date, salesperson, or store. The remaining transaction number has no useful attributes of its own.

You may see the abbreviation DD in dimensional models.

Why does it stay in the fact table?

You could create a dimension table for invoice_number, but if that table contains only invoice_number and no descriptions, it adds a join without adding information.

The number is still useful, so you keep it in the fact table.

• It groups fact rows that belong to the same business transaction. One order with five lines becomes five fact rows with the same order number.

• It lets you count transactions. How many orders did we process this month? That is a distinct count of order number, not a count of order lines.

• It gives analysts a path back to the source system. A suspicious invoice line can be traced to the original invoice.

Example: order_number on a sales fact

Imagine a sales fact table at order-line grain. Each row has foreign keys to customer, product, order date, ship date, salesperson, and store. It also has measures such as quantity, discount, and sales amount.

The order_number column remains in the fact table. It does not point to a separate order dimension, because the useful descriptions of the order are already represented through the other dimensions. The number itself groups lines and supports transaction counts.

An invoice number on an accounting fact works the same way. Several invoice lines share one invoice number. The number belongs in the fact table as a degenerate dimension.

How is it different from a normal dimension?

A normal dimension has a table full of descriptive columns. A Product dimension might include product name, category, brand, package size, and lifecycle status. The fact table stores only the product key.

A degenerate dimension has no such descriptive table. It is the key without the surrounding description.

If you later discover useful attributes around that number, such as order channel, order status, fraud flag, or invoice type, you may need a real dimension or a junk dimension instead. The degenerate dimension is right only when the number itself is the useful thing.

Where does it appear?

Degenerate dimensions are common in transaction fact tables and snapshot fact tables where a business document spans several rows: order lines, invoice lines, claim lines, booking lines, payment lines, or shipment lines.

They also appear when the fact table grain is lower than the business document. The fact row describes one line, but users still need to group by the header number.

What to watch out for

Do not build empty dimensions
If a dimension table would have the same number of rows as the fact table and no useful attributes, it is probably a degenerate dimension.

Do not throw the identifier away
Transaction identifiers are useful for grouping, distinct counts, drill-through, and audit trails.

Do not confuse it with a junk dimension
A junk dimension bundles several low-cardinality flags or indicators. A degenerate dimension is usually a high-cardinality transaction identifier.

Watch cardinality in BI tools
Order numbers and invoice numbers can have many distinct values. They are useful for drill-through and distinct counts, but not always for slicers or report filters shown to end users.