In this lesson, you will learn how to handle transactional identifiers—like order numbers—directly within your fact tables without creating unnecessary dimension tables.
The Problem of "Lone" Identifiers
When you build a sales schema, you naturally have dimensions for Customer, Product, and Date. These tables store descriptive attributes like addresses or categories. However, you often encounter business keys that don't belong to any descriptive dimension, such as an Order_Number or Invoice_ID.
If you were to create a dedicated Dim_Order table, it might only contain a single column: the Order_Number itself. This creates a "useless" dimension table that adds complexity and join overhead without providing any extra descriptive value. This is where a Degenerate Dimension comes in.
What is a Degenerate Dimension?
A degenerate dimension is simply a dimension key that resides directly inside the fact table. It is "degenerate" because it lacks a corresponding dimension table. By placing the Order_Number inside the Fact_Sales table, you keep your schema clean while still allowing users to filter or group data by that specific transaction.
Implementation Example
Consider a transaction fact table. Instead of joining to a separate dimension table, you store the identifier as an attribute:
CREATE TABLE Fact_Sales (
Sales_Key SERIAL PRIMARY KEY,
Date_Key INT,
Product_Key INT,
Customer_Key INT,
Order_Number VARCHAR(20), -- This is the Degenerate Dimension
Quantity INT,
Total_Amount DECIMAL(10, 2)
);
By embedding the Order_Number here, your analytics tool can easily perform a query like:
SELECT SUM(Total_Amount) FROM Fact_Sales WHERE Order_Number = 'INV-1001';
This keeps your model efficient because you avoid an extra join. If you ever find that your Order_Number needs descriptive attributes later (like Shipping_Method or Billing_Address), you can easily extract those columns into a new Dim_Order table at that time. Until then, keeping it in the fact table is a clean and performant design choice.
Key Takeaway: A degenerate dimension is a transactional identifier stored directly in a fact table to provide filtering capabilities without the overhead of an empty or redundant dimension table.