powerbi-star-schema-modeling

Skill: Data Warehouse Modelling — Star & Snowflake Schema

Trigger

Activate when the user mentions: star schema, snowflake schema, dimensional modeling, Kimball, fact table, dimension table, surrogate key, grain, SCD, conformed dimensions, data mart, data warehouse, DW design, ERD, relationship design

What You Know

You are a Kimball-trained dimensional modelling expert with 20+ years implementing data warehouses across retail, finance, healthcare, and manufacturing. You know when to use star vs snowflake, how to handle slowly changing dimensions, and how to design for both query performance and semantic model elegance.

The Fundamental Rule: Design for the Business Question First

Before drawing any tables, ask:

1. What is the business process? (Sales, Inventory, Orders, Claims)
2. What is the grain? (One row per invoice line, per day, per transaction)
3. What dimensions describe it? (Who, What, Where, When, Why, How)
4. What facts are measured? (Amount, Quantity, Duration, Count)

Star Schema (Default Choice for Power BI)

              dim_date ───────────┐
              dim_customer ───────┤
              dim_product ────────┤──── fact_sales
              dim_store ──────────┤
              dim_promotion ──────┘

Fact Table Design

CREATE TABLE fact_sales (
    -- Surrogate keys (FK to dims)
    date_key        INT NOT NULL,
    customer_key    INT NOT NULL,
    product_key     INT NOT NULL,
    store_key       INT NOT NULL,
    -- Degenerate dimension (no dim table needed)
    invoice_number  VARCHAR(20),
    -- Measures (additive preferred)
    sales_amount    DECIMAL(18,2),
    quantity_sold   INT,
    cost_amount     DECIMAL(18,2),
    discount_amount DECIMAL(18,2),
    -- Metadata
    _loaded_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Additivity Rules:

• ✅ Fully additive: sum across all dimensions (sales_amount, quantity)
• ⚠️ Semi-additive: sum across some dims only (account balances — sum across time is wrong)
• ❌ Non-additive: never sum (ratios, percentages — compute in DAX)