What is a Degenerate Dimension in SQL Server Analysis Services? In what scenarios do you use it?

A Degenerate Dimension is a Dimension which is derived out of a Fact Table and it appears to the end user as a separate/distinct Dimension, its data is actually stored in the Fact Table. It's a Dimension table which does not have an underlying physical table of its own.

Degenerate Dimensions are commonly used when the Fact Table contains/represents Transactional data like Order Details, etc. and each Order has an Order Number associated with it, which forms the unique value in the Degenerate Dimension.

Example:-

Degenerate Dimensions having unique Order Numbers can be used to identify the various items sold as part of a particular order.

Here are some highlights of Degenerate Dimension:

It is derived from the Fact Table and does not have an underlying physical Dimension Table of its own.

It is also called as a Fact Dimension.

Since these dimensions are built on top of Fact Table, these are usually very large dimensions.

The attribute of a Degenerate Dimension is not a Foreign Key in the Fact Table.