Star schema

What is a star schema?

A star schema is a data model with one central fact table and one or more dimension tables attached to it. Drawn out, it looks like a star: the fact table in the middle, dimensions around it as the points of the star. Since Ralph Kimball formalised the approach in the 1990s, it has been the standard for BI reporting.

The idea is simple. The fact table contains measurable events (orders, transactions, clicks, stock readings). The dimension tables describe the context of those events (customers, products, dates, stores). In a report you pick a dimension to filter or group by, and a measure on the fact table to calculate.

Compare it to a till receipt. The receipt itself is the fact: three packets of coffee sold on 17 April. The companion slip with customer details, product specs, and store info are the dimensions.

Why is a star schema so important?

Performance
BI engines such as VertiPaq (the core of Power BI) are explicitly tuned for star schemas. They compress columns extremely well and execute joins between a fact and several dimensions at high speed. Rewrite the same model in a normalised layout and performance often drops by an order of magnitude.

Simplicity for users
Business users immediately recognise what to drag where. Numbers come from the fact table, categories from dimensions. No long debates about which of fifteen tables really describes the customer.

Reusable dimensions
One customer dimension feeds dozens of fact tables. One date dimension works in every model you will ever build. That prevents everyone inventing their own customer definition.

DAX-friendly
DAX was designed with a star schema in mind. Time intelligence functions, CALCULATE patterns, and filter context all work best when facts and dimensions are neatly separated.

What sits in a fact table?

A fact table holds rows that record events. Each row is one event: one sales line, one click, one reading.

Keys to dimensions
One foreign key per related dimension: CustomerId, ProductId, DateId, StoreId. These keys are the steering wheel to navigate to dimensions.

Numeric measures
Quantities, amounts, durations, counts. The numbers you sum, average, or ratio.

No descriptions
Fact tables do not hold customer names or product descriptions. Those live in dimensions. That keeps the fact table narrow and fast.

What sits in a dimension table?

A dimension table describes a perspective, say a customer or a product. One row per unique item.

Surrogate key
An internal ID, separate from the business key in the source. That is what makes Slowly Changing Dimensions possible and absorbs changes in source systems.

Attributes
All descriptive columns: name, category, region, segment. Prefer liberal denormalisation over a cascade of extra tables.

Hierarchies
Often built in, for example Country > Region > City > Store. Very useful for drill-downs in reports.

Star schema versus snowflake schema

A snowflake schema is an extended variant where dimensions themselves split into smaller dimensions (for example Product > Category > Subcategory as three tables). Technically purer, but rarely worthwhile in practice:

Lower performance
More joins mean slower queries, especially in VertiPaq.

More complex DAX
Filter context has to travel through more tables. Time intelligence and cross-filter patterns become harder to reason about.

Harder for users
Business analysts get lost in the extra tables.

Rule of thumb: always denormalise to a star unless you have a very good reason not to.

How do you build a star schema in Power BI or Fabric?

1. Start from the use case. Which questions must be answered? Those questions define the facts and dimensions.

2. Identify fact tables. Every physical event (sale, log, reading) becomes its own fact table at its own grain.

3. Build conformed dimensions. One customer, one product, one date table for the whole model. That is what Kimball called conformed dimensions.

4. Denormalise. All customer attributes in one customer table, all product attributes in one product table. No extra lookup tables unless truly needed.

5. Set relationships correctly. Single-direction filters from dimension to fact, cardinality 1-to-many. Avoid bi-directional filters, they almost always signal a model that is broken elsewhere.

6. Keep fact tables visible, not hidden. They can be wide (many rows), but their structure stays clear: keys plus measures.

Pitfalls

Everything in one big table
New Power BI users often load a single wide table from Excel or SQL with customer, product, date, and sales intertwined. It works on small data, but falls apart as it grows. Invest early in a real dimensional model.

Descriptions in the fact table
ProductName or CustomerSegment in the fact table pushes up the memory footprint and slows the model. Move those columns to the dimension and link via the surrogate key.

No dedicated date table
A real date table with every day from start to end is non-negotiable for time intelligence. Do not let it be derived from the fact table itself.

Too many relationships
A fact table tied to thirty dimensions is unreadable. Consider splitting it into smaller facts per subject area, each with a limited dimension set.

Snowflake out of convenience
Keeping dimensions split because the source is normalised is not a good reason. Flatten the dimension in Power Query or in your data warehouse layer.