Degenerate dimension

We have discussed the use of junk dimensions in data modelling and in this post we are going to explore Degenerate dimension. Lets start with the definition.

Degenerate Dimension

A high cardinality attribute column in the fact table which does not have any other content except its natural key and is required as a dimension for analysis or drill-down purpose, is called a degenerate dimension. As this degenerate dimension is constructed from a fact table item and is placed in the fact table, it is also known as fact dimension. It helps to reduce duplicate data by placing high cardinality dimension key in the fact table.

Degenerate dimension keys are significantly unique and are nearly as large as fact table. Moving this attribute in a single column dimension table requires a considerably large dimension table, and you would also need to join the surrogate key of the fact table to the dimension table which leads to performance issues, specially when fact table is reasonably large.

Degenerate dimension does not have its associated dimension table, but still it can be used to group related rows of fact table. For example, order number in purchase order fact table, dealing with multiple line items, can be a good candidate for degenerate dimension. Purchase order fact table can be grouped with order number to get the list of all associated items.

Another example could be a bug identification number in a defect data system which records the details of bugs occurred throughout various systems. Bug id can be used to find all related fact table rows, if needed. A free form comment text field can also be a good candidate for degenerate dimension or fact dimension.

Below is an example from AdventureWorksDW2014 database table. This is a customized view from FactInternetSales table to show an example of degenerate dimension along with other analytic dimensions and measures. In below image, we can see that attribute SalesOrderNumber is marked as degenerate dimension (fact dimension), as it does not have any other attribute except its natural key and the values in the column is almost unique throughout the table. SalesOrderNumber attribute can be a good candidate which can be degenerated as a fact dimension.

Degenerate dimensions mostly occur in transaction level fact tables. If we have a dimension table which is growing along with fact table in the system, it can be reviewed for a degenerate dimension candidate.

Thanks for reading, and if you like, do rate and share this post on your social wall. Please share your valuable comments and suggestions on this post in comment section.