Back to all blogs
    6 min read

    📐 Dimensional Modeling: Facts & Dimensions

    datawarehousedimensionalmodelingfactdimension

    📐 Dimensional Modeling (Core)

    Analogy

    Look at a grocery store receipt.

    • It tells you Who bought it (Customer), Where (Store), When (Date), and What (Product). These are the Dimensions (the context).
    • It also tells you the Quantity (2 apples) and Price ($5). These are the Facts (the numbers).

    Dimensional Modeling is just organizing a database exactly like that receipt.

    Dimensional Modeling is a way of designing a database so that it is incredibly easy for business users to understand, and incredibly fast for analytical queries to run.

    Instead of spreading data across 50 confusing tables (like in a normal OLTP database), we split data into just two types of tables: Fact Tables and Dimension Tables.

    🔢 1. Facts (The Numbers)

    A Fact is a measurable, numerical piece of data. It is the "performance" you want to analyze.

    • Examples: Sales amount, Discount given, Quantity sold, Website clicks.

    The Fact Table

    A Fact Table sits at the center of the database. It contains:

    1. The Numbers (The actual facts/measures).
    2. The Keys (Foreign keys that link to the Dimension tables).
    Example: Sales Fact Table
| Date_Key | Store_Key | Product_Key | Quantity | Total_Amount |
|----------|-----------|-------------|----------|--------------|
| 20240621 | S-101     | P-505       | 3        | $15.00       |

    The Concept of "Grain"

    The Grain is the level of detail in a Fact Table. It answers the question: "What does one single row in this table represent?"

    • Low Grain (Very detailed): One row = One item scanned at the register.
    • High Grain (Summarized): One row = Total sales for the whole store for the whole day.
    Warning

    You must always define the Grain before you build the table!

    📊 Types of Facts

    Not all numbers can be added together easily.

    TypeWhat is it?ExampleCan you SUM it?
    AdditiveNumbers that make sense to add across all dimensions.Sales Amount✅ Yes (Total sales for the year)
    Semi-additiveNumbers you can add across some dimensions, but not time.Bank Account Balance⚠️ No (You can't add Monday's balance to Tuesday's balance to get a total).
    Non-additiveNumbers you can never add.Profit Margin (%), Temperature❌ No (You can't add 10% margin + 20% margin to get 30%). You must average them instead.

    🏗️ Types of Fact Tables

    Depending on how you record events, Fact Tables come in three flavors:

    1. Transaction Fact Table: Records a single event at a single point in time. (e.g., A customer buys a shirt. One row is created).
    2. Periodic Snapshot Fact Table: Takes a "picture" of the data at regular intervals. (e.g., A bank records your account balance at the end of every month).
    3. Accumulating Snapshot Fact Table: Records a process that has a clear beginning and end, and updates the row as it moves. (e.g., An order processing pipeline: Ordered -> Packed -> Shipped -> Delivered. The same row gets updated with timestamps as the package moves).

    🏷️ 2. Dimensions (The Context)

    If Facts are the numbers, Dimensions are the words that describe the numbers. They answer the Who, What, Where, When, and Why.

    • Examples: Customer Name, Product Category, Store Location, Date.

    The Dimension Table

    Dimension Tables are wide tables filled with text (attributes). They provide the filtering and grouping for your reports.

    Example: Product Dimension Table
| Product_Key | Product_Name | Category | Brand | Weight | Color |
|-------------|--------------|----------|-------|--------|-------|
| P-505       | Apple        | Fruit    | FarmX | 150g   | Red   |

    Role in Analysis

    When a manager asks: "Show me the Total Sales for Apples in Mumbai during June."

    • Total Sales comes from the Fact Table.
    • Apples (Product), Mumbai (Location), and June (Date) come from the Dimension Tables.

    🧪 Practice Drill

    text
    // Try answering these:
// Q1. What are the two main types of tables in Dimensional Modeling?
// Q2. Is a "Customer's Email Address" a Fact or a Dimension?
// Q3. Is "Discount Amount ($)" a Fact or a Dimension?
// Q4. A manager wants to know the daily temperature of a warehouse. Is Temperature an Additive, Semi-additive, or Non-additive fact? Why?
// Q5. Match the definition to the correct concept:
// | Definition | Concept |
// |---|---|
// | A table that updates a single row as an order moves from 'Packed' to 'Shipped'. | ? |
// | The rule that defines what one single row in a fact table represents. | ? |
// | A table that records a single event at an exact moment in time, like a credit card swipe. | ? |
    💡 Click for Solutions

    A1. Fact Tables and Dimension Tables.

    A2. Dimension. It is descriptive context (the "Who").

    A3. Fact. It is a measurable number.

    A4. Non-additive. You cannot add Monday's temperature (30°C) to Tuesday's temperature (32°C) and say the total temperature is 62°C. It makes no logical sense. You have to average it.

    A5.

    DefinitionConcept
    Updates a single row from 'Packed' to 'Shipped'.Accumulating Snapshot Fact Table
    What one single row represents.Grain
    Records a single event like a card swipe.Transaction Fact Table

    ← Previous Topic | Next Topic → Next Topic