A dimension table provides the descriptive attributes that give meaning to the measurements stored in fact tables — who, what, where, and when. This guide explains dimension table design, the attributes they contain, hierarchies, and how they enable the filtering, grouping, and drill-down that BI analysis requires.
A dimension table provides the descriptive context for the measurements stored in a fact table. Where the fact table records what happened and how much (revenue, quantity, duration), the dimension table records who, what, where, and when — the attributes that allow measurements to be filtered, grouped, and compared. Without dimension tables, fact table measurements are numbers without meaning.
In a sales star schema, the fact table has a customer_key, product_key, date_key, location_key, and revenue measure. The dimension tables answer the questions: who is customer 12345? (customer dimension — name, segment, region, acquisition date) What is product 8901? (product dimension — name, category, brand, unit cost) When was this? (date dimension — day of week, month, quarter, fiscal period) Where? (location dimension — city, state, country, sales territory).
What a Dimension Table Contains
**Surrogate key:** A system-generated integer primary key. Not the source system's business identifier — a warehouse-controlled integer that is stable regardless of source system changes.
**Natural key:** The business identifier from the source system — the account ID from Salesforce, the SKU from the product catalog. The natural key is stored for source reconciliation and as the basis for slowly changing dimension management.
**Descriptive attributes:** The full set of attributes by which the dimension entity might be analyzed. A customer dimension might contain: company name, company size (headcount tier), industry, vertical, geographic region, country, city, customer segment (small business, mid-market, enterprise), acquisition channel, first order date, CSM owner name, contract tier.
The width of dimension tables is a feature, not a liability. A wide customer dimension with 50 attributes enables 50 different ways of filtering, grouping, and comparing — by segment, by industry, by region, by acquisition channel, in any combination. This is the analytical flexibility that dimensional modeling provides.
**Effective date columns (for SCD Type 2):** When the dimension tracks historical changes (Type 2 slowly changing dimension), each row has an effective_start_date and effective_end_date defining the period during which that row's attribute values were the current state. The current row has an effective_end_date of NULL or a far-future date (9999-12-31 is a common convention).
**Is_current flag:** A boolean indicating whether this row is the current active record for the entity. Simplifies queries that want only current state — filter on is_current = true rather than constructing the date logic.
Dimension Hierarchies
Dimensions frequently contain hierarchies — levels of aggregation that allow drill-down analysis. Product hierarchies are common:
Product subcategory → Product category → Product department → All products
Storing all hierarchy levels as columns in the product dimension table means that a Tableau visualization can start at Department level and drill down to Subcategory without any additional joins. The drill-down path is within the same dimension table row.
Some dimensions have multiple hierarchies sharing the same lowest level:
- Product has a category hierarchy (subcategory-category-department)
- Product also has a brand hierarchy (product-brand-manufacturer)
- Product also has a price tier hierarchy (product-tier-premium/standard/budget)
Multiple hierarchies within the same dimension are stored as separate column sets in the same row — the product row has category, brand hierarchy, and price tier columns all present.
Degenerate Dimensions
Not every dimension attribute warrants its own dimension table. A degenerate dimension is an attribute that appears directly in the fact table because it has no descriptive attributes that justify a separate dimension table.
Order number is the canonical example. An order number uniquely identifies a transaction but has no descriptive attributes — there is no "order dimension" with meaningful columns beyond the order ID itself. The order number lives as a column in the fact table (a degenerate dimension) rather than in a separate join table.
Degenerate dimensions serve as drill-through keys — when an analyst wants to look up the source system record for a specific transaction, the order number in the fact table provides the link.
Junk Dimensions
Junk dimensions collect low-cardinality flag and indicator columns that would otherwise clutter the fact table. An order transaction might have several boolean flags: is_gift_order, is_rush_shipment, is_loyalty_redemption, discount_applied. Storing each as a separate column in the fact table adds width without adding meaningful dimension keys.
A junk dimension combines these flags into a single dimension table. Every combination of flag values (true/true/false/true, true/false/false/false, etc.) gets its own row in the junk dimension with a single surrogate key. The fact table stores one junk_dimension_key instead of four boolean columns. The number of rows in a junk dimension is bounded by the number of possible flag combinations — manageable for a small number of low-cardinality flags.
Our data architecture services designs dimensional models — customer, product, date, location dimensions and specialized types — that produce the analytical flexibility your BI layer requires. Contact us to discuss your data warehouse modeling requirements.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →