Some methodologies age badly and others stand the test of time. Ralph Kimball dimensional modeling clearly belongs to the second group. Decades after it was proposed, it remains the foundation of practically every data warehouse and semantic model that works well, from SQL Server to Microsoft Fabric. The reason is simple: it aligns the data model with the way the business thinks.
Anyone who works with data knows the symptom of a poorly designed model: slow reports, metrics that do not match between departments, and every new question demanding hours of work. A good dimensional model solves this at the source. Let us see how, in practice.
Facts and dimensions: the central idea
All dimensional modeling rests on a simple distinction. Facts represent measurable business events, such as a sale, an order or a payment. Dimensions represent the context of those events, the who, what, when and where. The fact table stores the numbers (quantity, value); the dimensions store the attributes you filter and group by (customer, product, date, store).
This separation is not technical bureaucracy: it is what lets a business user answer "sales by region and by month" without writing a single line of code, dragging dimensions and measures intuitively.
The most important decision: the grain
If one decision defines the success of a model, it is this: define the grain of the fact table before anything else. The grain is the level of detail of each row. One row per sale? Per line item? Per day and product?
The golden rule is to always choose the most atomic level available. Storing at the sale-line level gives you flexibility to aggregate in every possible way later. Choosing an already-aggregated level saves space in the short term, but sooner or later someone will ask for a detail that no longer exists, and the rebuild is costly and risky.
The ingredients of a solid model
Beyond the grain, a few patterns separate a robust model from a fragile one:
- Surrogate keys: numeric keys owned by the warehouse, independent of business keys, that make joins fast and resilient to changes in the source.
- Denormalized dimensions: unlike the transactional model, here it pays to flatten dimensions (the product with its category and brand in the same table), favoring simplicity and read performance.
- A complete date table: perhaps the most underrated dimension. Having a calendar dimension with year, quarter, month, day of week and holidays unlocks all time analysis.
- Star schema: facts at the center, dimensions around them. Simple to understand, fast to query.
In practice: from transactional to analytical
Imagine an e-commerce database, normalized in detail to support the application: orders, order lines, products, categories, customers, addresses, all in separate tables linked by keys. It is excellent for recording data and terrible for analyzing it.
When you turn it into a dimensional model, that web collapses into something simple: a "sales" fact table at the order-line level, and a handful of dimensions (customer, product, date, channel). Suddenly, a question that required several joins and an analyst becomes a report that any manager builds in seconds.
Kimball in the lakehouse era
You might think that with Fabric, Databricks and lakehouses, dimensional modeling no longer matters. The opposite is true. The tools have changed, with Delta Lake, Direct Lake and distributed processing, but the need for a model the business understands remains intact. The Gold layer of a Medallion architecture is typically a Kimball dimensional model.
The lesson that endures is this: technology speeds things up, but it does not replace good design. Investing time in modeling is still what separates a warehouse that people use and trust from one that nobody opens. And in your project: was the grain of the fact table a deliberate decision, or did it happen by accident?
