Degenerate dimension: A column of the key section of the
fact table that does not have the associated dimension
table but used for reporting and analysis, such column is
called degenerate dimension or line item dimension.
For ex, we have a fact table with customer_id, product_id,
branch_id, employee_id, bill_no, date in key section and
price, quantity, amount in measure section. In this fact
table, bill_no from key section is a single value, it has
no associated dimension table. Instead of cteating a
seperate 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.
According to Ralph Kimbal, 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 wishes to maintain system specific identifiers such as order numbers, invoice numbers and the like without forcing their inclusion in their own dimension. The decision to use degenerate dimensions is often based on the desire to provide a direct reference back to a transactional system without the overhead of maintaining a separate dimension table.
These are some date elements in the operational system
which are neither fact nor strictly dimension attributes.
These are useful for some kind of analysis.
These are kepts as attributes in fact table called
degenerate dimension is a data dimension that is stored in
the fact table rather than a separate dimension table. This
eliminates the need to join to a dimension table. You can
use the data in the degenerate dimension to limit or 'slice
and dice' your fact table measure
When a Fact table has dimension value stored it is called
degenerated dimension.Since a fact table doesn't store
textual items ,it should be stored in varchar datatype.
Why we are going?
Instead of maintaining a separate dimension table with
single values ,for eg.customer_policy_details='paid' ,we
can store it in fact table ,for performance.