Fact table vs dimension table — the foundation of reporting

1. Two kinds of tables

Fact table — events that happened, with numbers. Many rows, grows every day.

Dimension tables — descriptions of the things in the facts. Few rows, change rarely.

2. Keys connect them

ProductID is unique in the Products table (a primary key) and repeats in the Sales table (a foreign key). One product → many sales: a one-to-many relationship.

3. Why not one big sheet?

If every sales row also stores Product name, Category, Store name, City and Region:

• the file is many times bigger,
• one category rename means changing thousands of rows (and missing some),
• typos create fake categories ("Electronic" vs "Electronics").

Split tables: change a category once in Products, and every report updates.

4. Grain — what one fact row means

Always be able to finish the sentence: "One row in this table = one ___."

• one invoice line (product per invoice) — common for retail,
• one invoice (total only) — can't analyse by product,
• one day per store — summary, can't see individual orders.

Mixing grains (some rows are invoices, some are daily totals) gives wrong totals. Choose the finest grain you need.

5. The star schema

              Products
                  │
   Calendar ── Sales (fact) ── Stores
                  │
               Targets*

Facts in the middle, dimensions around it — a star. Excel's Data Model (Module 4) and Power BI work best this way.

*Targets are another fact table at a different grain (region × month), joined through shared dimensions.

6. Don't forget the Calendar dimension

A Date table with one row per day and columns like Month, Quarter, Financial Year (Apr–Mar), Day name. It lets you report by FY and makes time intelligence (YTD, last year) work in Module 4.

7. Before the Data Model: XLOOKUP still works

Even without Power Pivot, keep dimensions as separate Tables and bring in what you need with XLOOKUP (Foundations Module 4) or a Power Query merge (Module 3).

Common mistakes

Duplicate keys in a dimension table (two rows for P02 → double counting). Keys with extra spaces ("S01 " ≠ "S01"). Mixing grains in one fact table.