Tutorial corner

Informatica,ETL,oracle,sql/plsql

Degenerate dimension in Data warehouse with Example

Spread the love

A degenerate dimension is a dimension which is derived from the fact table and doesn’t have its own dimension table. In below article we will discuss degenerate dimension in details.

Kimball definition of Degenerate Dimension

According to Ralph Kimball, in a data warehouse, a degenerate dimension is a dimension key in the fact table that does not have its own dimension table, because all the interesting attributes have been placed in analytic dimensions. The term “degenerate dimension” was originated by Ralph Kimball.

Check here for Junk Dimension in Data warehouse

What is Degenerate Dimension ?

A Degenerate dimension is a Dimension which has only a single attribute. This dimension is typically represented as a single field in a fact table.The data items thar are not facts and data items that do not fit into the existing dimensions are termed as Degenerate Dimensions.

Example of Degenerate Dimension

In below Fact Table with customer_id, product_id, bill_no, date in key section and price, quantity in measure section. In this fact table, bill_no from key section is a single value, it has no associated dimension table. Instead of creating a  separate dimension table for that single value, we can include it in fact table to improve performance. So here the column, bill_no is a degenerate dimension or line item dimension.

Degenerate Dimension in data warehouse

Degenerate Dimension

Degenerate Dimensions are the fastest way to group similar transactions.Degenerate Dimensions are used when fact tables represent transaction data.They can be used as primary key for the fact table but they cannot act as foreign keys.

Check here for Conformed Dimension in Data warehouse

Pros and Cons of the Degenerate Dimension

Pros

  • Avoid a expensive join between two tables with a one-to-many relationship but which is close of a one-to-one relationship
  • By degenerating a date dimension attribute, you avoid the need of a join between the event dimension and a time dimension (which enable the drill through the time hierarchy)

Cons:

  • Increase the size of the fact table

By moving the dimensional attribute in the fact table, you increase the need of storage and you may use a partial degeneration

Check here for Role Playing Dimension in Data warehouse

Partial degeneration

To  overcome the increase of capacity need when you degenerate a complete fact dimension, you can made a partial degeneration of the fact dimension by choosing to degenerate only a set of attribute with a high analytic value (i.e. often used in reporting).

While it is generally not considered good dimensional modelling practice to create a dimension with a potential one-to-one relationship with the fact table, in this situation it’s a reasonable trade off. This design trade off works because the reference dimension should very seldom actually join back to the fact table.

Sources:

https://en.wikipedia.org/

So in above data warehouse tutorial ,we have gone through some basic concept of Degenerate dimension , along with importance and its advantages.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme