Skip to main content

Dimensional Modeling

Dimensional modeling organizes data around measurable business processes. Instead of copying an application's tables into dashboards, identify an event such as a purchase, declare the grain of one analytical row, attach numeric facts, and describe the event with dimensions. The result favors understandable queries and stable metric definitions over normalization for transactional updates.

Prerequisites​

Complete Pipeline Observability and revisit Grouping and Aggregation if grouping grain is unfamiliar.

Learning goals​

  • Move from a business question to process, grain, facts, and dimensions.
  • Separate additive measures from descriptive context.
  • Recognize when a normalized operational model and an analytical model serve different jobs.
  • State assumptions before publishing a metric.

Worked example​

For the local event process, declare the grain as one row per unique event_id. amount_cents is a fact; customer region is dimensional context. The query answers a defined question—net recorded amount by the customer's current region—without implying revenue recognition or causality.

SELECT d.region, COUNT(*) AS event_count, SUM(f.amount_cents)::INTEGER AS amount_cents
FROM fct_event AS f
JOIN dim_customer AS d USING (customer_key)
GROUP BY d.region
ORDER BY d.region;

It returns North with four events and 2,800 cents, and South with two events and 1,750 cents. The negative event is retained; silently removing it would change the metric. A dimensional model does not decide whether that event is a refund, correction, or error—the contract must.

Start design with a short bus-matrix-style inventory: rows are processes, columns are reusable dimensions. Shared definitions such as customer and calendar can become conformed dimensions, but only when their keys, history, and semantics genuinely match across processes.

Limitations and common mistakes​

  • Choosing columns before declaring the grain creates ambiguous counts and joins.
  • Calling every number a measure ignores that identifiers and balances are often non-additive.
  • Denormalization can improve usability but duplicates descriptions and requires governed history.
  • A star schema does not repair invalid, late, or duplicated source data.
  • “Revenue” is a business definition, not a synonym for summing every amount field.

Practice​

Write a four-line specification for a shipment process: business process, row grain, two candidate facts, and three dimensions. Mark which facts are additive across time.

Next steps​

Continue with Facts, Dimensions, and Grain to make keys and additivity precise.

Primary sources​

Checked 2026-10-15: Kimball Group dimensional modeling techniques, fact-table structure, and Enterprise Data Warehouse Bus Architecture.