Designing Fact and Dimension Tables

Designing Fact and Dimension Tables

In this lesson, you will learn how to organize your raw business data into two distinct structures: quantitative Fact tables and descriptive Dimension tables.

To build a successful star schema, you must categorize your data based on how it is used. Think of a business process, such as a retail sale. Some data points are measurements of "how many" or "how much," while others are labels that provide "who," "what," and "where" context.

Identifying the Components

1. Fact Tables: These are the heart of your data model. They contain the quantitative metrics (or "measures") resulting from a business process. Because these tables are focused on numbers, they are usually long and narrow, filled with foreign keys linking to dimensions and numerical values like price, quantity, or discount_amount.
2. Dimension Tables: These act as the "nouns" of your data. They contain the descriptive attributes that allow you to filter, group, and label your facts. If you want to see sales by store location or by product category, those descriptors belong in your dimension tables.

Practical Example: Sales Data

Imagine a simple sales_fact table compared to a product_dim table:

-- Fact Table: Quantitative metrics
CREATE TABLE sales_fact (
    sale_id INT,
    product_key INT,       -- Foreign Key
    store_key INT,         -- Foreign Key
    quantity_sold INT,     -- Metric (Fact)
    total_amount DECIMAL   -- Metric (Fact)
);

-- Dimension Table: Descriptive attributes
CREATE TABLE product_dim (
    product_key INT PRIMARY KEY,
    product_name VARCHAR,
    category VARCHAR,
    brand VARCHAR
);

When you design these tables, follow this rule: if the data changes frequently or represents a calculation, it likely belongs in the Fact table. If the data provides context or describes the entities involved in the transaction, it belongs in a Dimension table. By keeping your metrics separate from your descriptive labels, you ensure that your analytics queries are performant and easy for stakeholders to understand.

Key Takeaway: Separate your numerical measurements into Fact tables and your descriptive context into Dimension tables to build a clean, star-schema model.