Context
Let’s use the previous example of a fact table: Order_facts
, in which information concerning orders is compiled. The grain of this fact table is actually a line of the order: each row of the table concerns a (PRODUCT
) ordered by a given (CUSTOMER
) from a given (SALESPERSON
) on a given (DAY
). The overall order encompasses all of the products ordered by the customer (a company), possibly from different salespersons.
How should we proceed if we want to add dimensional attributes that do not concern any of the identified dimension tables, such as an order number order_id
or a line number for an order (for a given product) order_line_id
?
Definition
A degenerate dimension is a dimension represented in a fact table by a unique attribute.
To resolve the previous problem, the solution (see image above) is to use a degenerate dimension, i.e. a dimension stored in the fact table in the form of a unique attribute. This is an attribute which can be used as a data dimension without a dimension table. Sometimes, it also defines the grain of the fact table. Dimensions characterised by a binary value: Vrai/Faux
or Oui/Non
are often created in the form of degenerate dimensions.
Reading Degenerate dimension: Star Schema. Christopher Adamson. pages 43, 57, 96
Reading Degenerate dimension: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. pages 62, 94