Dimensional Modeling Basics

Why this skill matters for BI Analysts

Dimensional modeling lets BI Analysts turn messy operational data into clear, fast, and trustworthy analytics. With properly modeled facts and dimensions, you get consistent KPIs, simpler SQL, and dashboards that scale without timeouts. It unlocks tasks like ad-hoc analysis, metric standardization, and cross-domain reporting.

What you will be able to do

• Design star schemas around a business question.
• Define the correct grain for fact tables.
• Choose and manage surrogate keys vs natural keys.
• Implement Slowly Changing Dimensions (SCD) for history tracking.
• Model transaction vs snapshot facts and use conformed dimensions for consistency.
• Map events into facts that analytics tools can query efficiently.

Who this is for

• Aspiring or junior BI Analysts who need a solid modeling foundation.
• Analysts moving from spreadsheet reporting to semantic models and data warehouses.
• Data-savvy PMs or Ops analysts who want reliable, reusable metrics.

Prerequisites

• Basic SQL: SELECT, JOIN, GROUP BY, WHERE.
• Comfort reading entity names and attributes in schemas.
• Understanding of your business processes (orders, subscriptions, tickets, etc.).

Learning path

1. Vocabulary first: Facts, dimensions, grain, surrogate keys, SCD, conformed dimensions.
2. Grain before design: State the exact level (e.g., one row per order line, per day per store).
3. Sketch a star: One central fact with dimension spokes. Keep it denormalized for ease of querying.
4. Keys you control: Use surrogate keys in dimensions and reference them from facts.
5. History handling: Start with SCD Type 1 and Type 2 basics.
6. Fact types: Model transaction facts vs periodic snapshots; know when to use factless facts.
7. Consistency across domains: Define conformed dimensions that multiple facts share (e.g., Date, Customer).

Core concepts in plain language

• Fact table: Events or measurements (e.g., order line items, payments). Numeric measures plus foreign keys to dimensions.
• Dimension table: Descriptive attributes (e.g., product name, category, region) for slicing and filtering.
• Grain: The exact “one row equals …” definition of a fact (e.g., one row per order line).
• Surrogate key: A warehouse-generated key (integer/UUID) that stays stable even if natural identifiers change.
• SCD Type 1 vs Type 2: Type 1 overwrites; Type 2 keeps history with effective dates and a current flag.
• Transaction vs Snapshot facts: Transaction captures every event; Snapshot captures state at regular intervals.
• Conformed dimension: A dimension shared across facts with the same meaning and values (e.g., the same Date or Customer).
• Mapping events to facts: Turning business events into rows with well-defined grain and clean foreign keys.

Worked examples

Example 1: Star schema for online orders

Business question: Track revenue, items sold, and discounts by date, product, and customer.

Grain: one row per order line item.

-- Dimensions (simplified)
CREATE TABLE dim_date (
  date_key INT PRIMARY KEY, -- e.g., 20250101
  date_value DATE,
  year INT, month INT, day INT, weekday_name TEXT
);

CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,
  natural_customer_id TEXT,
  name TEXT, region TEXT,
  scd_start DATE, scd_end DATE, is_current BOOLEAN
);

CREATE TABLE dim_product (
  product_key INT PRIMARY KEY,
  natural_product_id TEXT,
  name TEXT, category TEXT, brand TEXT
);

-- Fact
CREATE TABLE fact_order_line (
  order_line_id BIGINT PRIMARY KEY,
  order_id BIGINT,
  date_key INT REFERENCES dim_date(date_key),
  customer_key INT REFERENCES dim_customer(customer_key),
  product_key INT REFERENCES dim_product(product_key),
  quantity INT,
  unit_price NUMERIC(12,2),
  discount NUMERIC(12,2),
  revenue NUMERIC(12,2)
);

Notes: revenue = quantity * unit_price - discount; use dim_date for calendar reporting; dim_customer is SCD2 to keep region changes.

Example 2: Setting grain correctly

Suppose the marketing team needs daily inventory levels. If you choose a transaction grain (every stock movement), queries for “inventory on day X” get complex. Instead, use a snapshot fact.

Transaction grain statement: one row per inventory movement per product per warehouse.

Snapshot grain statement: one row per day per product per warehouse.

-- Snapshot fact example
CREATE TABLE fact_inventory_snapshot (
  date_key INT,
  product_key INT,
  warehouse_key INT,
  on_hand_qty NUMERIC(12,3),
  PRIMARY KEY(date_key, product_key, warehouse_key)
);

Example 3: SCD Type 2 pattern

Track customer region changes over time.

-- Query current dimension row
SELECT *
FROM dim_customer
WHERE natural_customer_id = 'C-123'
  AND is_current = TRUE;

-- Join fact to the correct historical dimension row by date
SELECT f.order_id, d.region
FROM fact_order_line f
JOIN dim_customer d
  ON f.customer_key = d.customer_key
JOIN dim_date dd
  ON f.date_key = dd.date_key
WHERE d.natural_customer_id = 'C-123'
  AND dd.date_value BETWEEN d.scd_start AND COALESCE(d.scd_end, '9999-12-31');

Tips: maintain scd_start, scd_end, is_current; ensure your ETL/ELT updates the previous row’s scd_end when a change is detected.

Example 4: Conformed dimensions across domains

Sales facts and Support tickets both need Customer. Create one dim_customer used by both fact tables so “region” and “segment” mean the same everywhere.

-- Both facts reference the same dim_customer
-- fact_order_line(customer_key) and fact_support_ticket(customer_key)

Benefit: users can compare revenue and tickets by the exact same customer segments.

Example 5: Mapping events to facts

Event: "Ride Completed" in a ride-hailing app.

• Grain: one row per completed ride.
• Dimensions: date (ride end), rider, driver, city, product tier.
• Measures: fare, distance_km, duration_min, driver_earnings.

CREATE TABLE fact_ride (
  ride_id BIGINT PRIMARY KEY,
  end_date_key INT,
  rider_key INT,
  driver_key INT,
  city_key INT,
  product_tier_key INT,
  distance_km NUMERIC(8,2),
  duration_min NUMERIC(8,2),
  fare NUMERIC(12,2),
  driver_earnings NUMERIC(12,2)
);

Drills and exercises

• [ ] Write a one-sentence grain for “monthly active users by country”.
• [ ] List three dimensions for a payments fact and two measures.
• [ ] Decide SCD Type 1 vs Type 2 for “Customer’s marketing opt-in status” and explain why.
• [ ] For product returns, choose transaction vs snapshot and justify.
• [ ] Identify two conformed dimensions you would standardize across Sales and Marketing.

Mini project: Retail subscription analytics

Goal: Model subscriptions for a retail brand offering monthly boxes.

1. Identify processes: subscription signed, renewal, cancellation, shipment.
2. Define facts: fact_subscription_event (transaction), fact_mrr_snapshot (monthly snapshot).
3. Dimensions: dim_date, dim_customer (SCD2), dim_plan, dim_geography.
4. State grains: one row per subscription event; one row per month per subscription for MRR snapshot.
5. Keys: surrogate keys in all dimensions; facts store foreign keys.
6. Validation: ensure snapshot has no duplicate primary keys for a month; verify revenue rollups match finance reports within tolerance.

Common mistakes and debugging tips

• Skipping the grain statement: Always write “one row equals …” before modeling. It prevents double counting.
• Using natural keys in facts: Prefer surrogate keys to avoid key drift and joins that break.
• Over-normalizing dimensions: Keep dimensions wide and denormalized for ease of use.
• Mixing transaction and snapshot rows: Separate fact tables to keep logic simple.
• Ignoring conformance: If Customer means different things across facts, cross-domain reports will be inconsistent.

Subskills

• Fact And Dimension Tables — Identify measures and descriptive attributes to build stars.
• Star Schema Basics — Organize around a central fact with dimension spokes for fast, simple queries.
• Grain Definition For Facts — State the exact “one row equals …” to avoid duplicate or missing counts.
• Surrogate Keys And Natural Keys — Use warehouse-controlled keys to stabilize joins.
• Slowly Changing Dimensions Basics — Choose Type 1 vs Type 2 to manage history.
• Snapshot Versus Transaction Facts — Model state over time vs individual events.
• Conformed Dimensions Across Domains — Share the same dimensions across facts for consistent metrics.
• Mapping Events To Facts For BI — Turn business events into analyzable fact rows with clean foreign keys.

Practical projects

• E-commerce sales mart: fact_order_line with dim_date, dim_customer (SCD2), dim_product; build top-line revenue and AOV.
• Support analytics mart: fact_ticket (factless for counts), dim_agent, dim_customer; track tickets by SLA and region.
• Marketing attribution mart: fact_touchpoint (transaction) + conformed dim_campaign and dim_date; report assisted conversions.

Next steps

• Study the subskills below in order. Practice by writing the grain before any schema.
• Complete the drills and the mini project to reinforce concepts.
• Take the skill exam to check mastery. Everyone can take it; logged-in users get saved progress.