Dimensional Modeling: Facts vs. Dimensions

Why it exists

Raw operational data is optimized for writing transactions, not for business analysis. Dimensional modeling solves this by restructuring data specifically for fast, aggregated queries, making it possible to derive insights from massive datasets.

The mental model

Think of a business event, like a single ticket sale. The dimensional model separates the measurable numbers from that event into a central "fact table" and the descriptive context into surrounding "dimension tables". The fact table holds metrics like price_paid and quantity. Dimension tables describe the who (customer), what (event), and where (venue). This is often called a star schema.

How it works

The Kimball methodology, a popular approach, involves four steps. First, identify a core business process, which is a measurable event like 'sales', not a department. Second, declare the grain, the most atomic level of detail, like a single line item on an invoice. Third, identify the dimensions, which are the descriptive text attributes (e.g., venue name, event date). Fourth, identify the facts, which are the numeric measurements from the process (e.g., sale amount, tickets sold).

When to use it

Use dimensional modeling when building a data warehouse or data mart for business intelligence and analytics (OLAP). It's perfect for systems like Amazon Redshift, where the goal is to power dashboards that let users "slice and dice" data, such as viewing sales by region over time.

When not to use it

This model is not for online transaction processing (OLTP) systems, like an e-commerce checkout service. Those systems require a highly normalized structure to handle frequent, single-record writes and updates with maximum data integrity.

One canonical example

For an event ticketing system, the business process is "ticket sales". The fact table contains one row per sale line item (the grain) with metrics like sale_price and num_tickets. Dimension tables would include a venue dimension (with venue_name, city), an event dimension (event_name, artist), and a date dimension. An analyst can then join these to ask, "What were the total sales for artist X in New York during Q3?".