Dimensional modelling (Kimball method)

What is dimensional modelling?

Dimensional modelling is a way to design analytical data models by separating facts from context. Facts record what happened: a sale, a shipment, a stock movement, a support ticket. Dimensions describe the event: customer, product, date, store, salesperson, region.

The usual result is a star schema: one fact table in the middle, surrounded by dimension tables. Business users filter and group by dimensions, then calculate measures from the fact table.

Ralph Kimball popularised this approach for data warehouses in the 1990s. The tooling has changed since then. Warehouses moved to the cloud, lakehouses appeared, and Power BI semantic models became a major serving layer. The core idea is still alive because reports still need the same thing: trustworthy numbers cut by clear business context.

Facts, dimensions, and grain

Fact tables
A fact table stores measurable events. Typical columns are keys to dimensions plus numbers such as revenue, quantity, duration, cost, or count. Fact tables are usually narrow but very long.

Dimension tables
A dimension table stores descriptive context. A product dimension might contain SKU, brand, category, size, supplier, and product group. A customer dimension might contain segment, region, account manager, and industry. Dimensions are often wider and easier for business users to recognise.

Grain
Grain says what one row in the fact table means. One order line. One invoice line. One daily stock balance per product per warehouse. One event in a process. If you do not declare the grain before modelling, double counting becomes almost inevitable.

Slowly changing dimensions
Customers move, products change category, salespeople switch teams. A dimensional model has to decide whether old reports use the old value or the new one. Slowly Changing Dimensions give names to those choices.

Conformed dimensions
A conformed dimension is shared across multiple fact tables. The same date, customer, product, or department definition can be used for sales, support, margin, and campaign reporting. That is what lets teams compare numbers across processes without rebuilding the definition each time.

Kimball's four design steps

Kimball's design process is deliberately practical. Before touching tables, answer four questions.

1. Choose the business process. Sales orders, deliveries, invoices, support tickets, stock movements, payments. A fact table usually belongs to one process.

2. Declare the grain. State exactly what one fact row represents. This is the contract for the whole model.

3. Identify the dimensions. Which descriptive context belongs to that grain? Date, customer, product, employee, channel, warehouse, project.

4. Identify the facts. Which measurements are valid at that grain? Amounts, quantities, durations, balances, flags, counts.

The steps sound simple because they are supposed to be used in workshops with business people, not hidden in a data engineer's notebook. The hard part is forcing precision. A model that starts with a vague grain usually ends with vague reports.

Kimball versus Inmon

The classic warehouse debate is Kimball versus Inmon.

Kimball is bottom-up and dimensional. Start with a business process, build a useful star schema, then expand by sharing conformed dimensions across subject areas.

Inmon is top-down and enterprise-first. Start with a normalised enterprise data warehouse, integrate the data centrally, then create data marts for reporting.

Most real platforms now mix the two. A company may use a Data Vault, lakehouse, or normalised integration layer underneath, then serve dimensional star schemas to Power BI and business users. Kimball still dominates the reporting layer because it is easier to query, explain, and secure.

Why it still matters in Power BI and Fabric

Microsoft's own Power BI guidance recommends star schema design for semantic models because it supports performance and usability. Dimension tables filter and group; fact tables summarise. That shape fits how report visuals generate queries and how business users think about analysis.

In Microsoft Fabric, the physical data may live in a warehouse, lakehouse, SQL endpoint, or semantic model. The same modelling principle remains useful: build clean facts and dimensions for the reporting layer instead of exposing the messy source schema directly.

The same is true beyond Microsoft. Modern columnar platforms such as Snowflake, BigQuery, Databricks, and DuckDB can handle wide tables and semi-structured data, but dimensional models still give people shared definitions. Performance is no longer the only reason to model. Consistency is the bigger one.

Where Kimball fits less well

Dimensional modelling is designed for structured business processes that are aggregated over time. It is less natural for every workload.

Machine learning often prefers a wide training table, where each row is already a feature vector. Joins at feature time can create leakage or subtle errors.

Event streams and logs may begin as semi-structured records. A star schema can still be built for reporting, but the raw event layer often stays closer to its source shape.

Graph and relationship-heavy data can be awkward in a star. Ownership structures, fraud networks, and many-to-many relationships may need graph or entity-resolution patterns first.

Exploratory self-service sometimes starts from a flat export because speed matters more than model elegance. That is fine for discovery, but the durable reporting model should still be designed.

What to watch out for

Skipping the grain conversation
This is the most expensive mistake. Decide what one row means before you load data, write DAX, or build relationships.

Copying the source system
An ERP or CRM schema is built for transactions, not reporting. Do not expose the operational model directly and call it a warehouse.

Too many facts in one table
If revenue, stock balance, forecast, and support tickets all sit in one table, the grain is probably mixed. Split by process and reconnect through conformed dimensions.

Dimensions that are not actually conformed
Two customer tables with different keys and different segment definitions are not conformed just because both are called Customer. Agree on the business definition.

Treating Kimball as religion
The method is a tool. Use it where the workload is reporting and analysis. Do not force every data product, AI feature table, or real-time stream into a star when another shape is clearer.