M365Con 2027 – free online conference · 11–22 Jan 2027 · 120 sessions m365con.net

Star schema

Concept

Data model design with a central fact table surrounded by dimension tables; the recommended structure for fast, simple Power BI models.

✦ CONCEPTStar schemaM365.FM
Title graphic: Star schema (Concept)

Relationships

Star schema is closely related to Semantic model, Power BI, Microsoft Fabric and DAX.

Star schema is one of the topics of Markus Ehrenmueller-Jensen.

Description

A star schema, also called Sternschema or dimensional modelling, is a data model design in which a central fact table is surrounded by dimension tables. The fact table holds the events or measurements, such as sales, while the dimension tables describe them, for example by date, product or customer. It is the recommended structure for fast, simple Power BI models, and Microsoft documents it in its Power BI guidance on star schema design.

The pattern is used to organize the tables of a semantic model. Instead of one wide, flat table or a highly normalized web of tables, the model separates what happened (facts) from the context in which it happened (dimensions). Relationships run from each dimension to the fact table. One of the episodes recommends keeping these relationships one-to-many and single-direction, and using surrogate keys rather than natural keys that are only "unique-ish". This predictable layout is what lets filters flow cleanly from a slicer on a dimension to the measures calculated over the fact table.

The design is closely tied to DAX. When the model has a clear star structure, measures are easier to write and to reason about, and calculations behave consistently because filter propagation follows the relationships. The episode "Reduce Power BI Premium Costs with Better Data Models" pairs the star schema with DAX discipline: pushing transformations to Power Query (M) instead of calculated columns, favoring columnar operations over row iterators, and building clean base measures that are layered with CALCULATE. A related episode on Microsoft Fabric lakehouse performance lists neglecting star schema design, in favor of a flat or highly normalized model, as a mistake that increases query complexity and hampers engine optimization in Power BI and the Fabric lakehouse.

In practice, decisions around the star schema usually concern how to shape source data before it reaches the model. The episode "Power Query Folding and Execution Order in Power BI" lists simplifying complex data models to a star schema among its optimization techniques and claims it can improve performance by 5-10 times, alongside query folding, removing unused columns and incremental refresh. That figure is the episode's own statement and not a guaranteed result. Typical situations include rebuilding a model that has grown into tangled relationships, splitting descriptive attributes out of a large table into dimensions, and adding a dedicated date dimension.

The episodes frame the model, not the visuals, as the place where report quality is decided. "Build a Power BI Star Schema for Faster Reports" argues that the real work in Power BI happens underneath, in how data is shaped and modeled, and that the model is the engine: without the right one, even a good-looking report falls apart. "Fix Power BI Models That Fail to Deliver Results" names poorly designed schemas that confuse users and inefficient models that struggle with large datasets as common causes of disappointing models, and suggests starting with the basics of fact and dimension tables and how to create a star schema. It also notes that a well-designed model makes DAX calculations simpler and improves report performance.

There is a cost angle as well. "Reduce Power BI Premium Costs with Better Data Models" states that tangled relationships quietly burden Power BI Premium capacity and team time, and that moving from a junk-drawer model to a star schema with lean fact tables and descriptive dimensions is an architectural fix. The star schema is therefore a narrower concept within the broader topic of the semantic model, and it applies across Power BI and Microsoft Fabric.

Overview compiled with AI from M365.FM episodes and the facts on this page. You can improve it with the edit button below.

TypeConcept
KindArchitecture
Also known asSternschema, Dimensional modelling
DocumentationStar schema – Microsoft Learn learn.microsoft.com

Sessions on this topic (1)

Event sessions on Star schema:

Key points about Star schema

Key points about Star schema from the episodes, each with its source episode:

Episodes about Star schema

Episodes in which Star schema is discussed:

Timeline of episodes about Star schema

Articles about Star schema (2)

Articles in which Star schema is a topic:

Frequently asked questions

Questions marked with AI were written with AI from the facts on this page.

What is a star schema in Power BI? (AI)

A star schema is a data model design with a central fact table surrounded by dimension tables. The fact table holds events such as sales, and the dimensions describe them by date, product or customer. It is the recommended structure for fast, simple Power BI models.

Why do the M365.FM episodes recommend a star schema? (AI)

The episodes say the model is the engine of a Power BI solution and that a clear fact-and-dimension structure simplifies DAX calculations and improves report performance. One episode also links tangled relationships to extra load on Premium capacity and cost.

How should relationships be set up in a star schema? (AI)

The episode "Reduce Power BI Premium Costs with Better Data Models" recommends one-to-many, single-direction relationships from dimensions to the fact table, using surrogate keys instead of "unique-ish" natural keys.

Does a star schema improve Power BI performance?

The show notes claim that 80 percent of users building star schema models report improved performance and analysis efficiency. It also makes the solution easier to maintain. · From the episode: Build a Power BI Star Schema for Faster Reports

How should a team adopt a star schema approach?

Define the scenario, owner and expected result, validate with a pilot, then document the decision and review it as needs change. This keeps the approach understandable and supportable. · From the episode: Build a Power BI Star Schema for Faster Reports

What is the data model fix for Power BI?

Move from a junk-drawer model to a star schema with lean fact tables and descriptive dimensions for product, customer and date. Relationships should be one-to-many and single-direction. · From the episode: Reduce Power BI Premium Costs with Better Data Models

Why use a star schema in Power BI?

A star schema keeps the data model simple, which makes it faster and easier to use. It is the structure the episode recommends as a foundation for good data modeling. · From the episode: Fix Power BI Models That Fail to Deliver Results

What other practices improve Lakehouse reporting performance?

Choose a star schema for the data model, use materialized views to speed up report loading, and schedule data refreshes in off-peak hours so workloads do not compete and users get consistent performance. · From the episode: Optimize Microsoft Fabric Lakehouse Performance

Relationship map of Star schema: linked topics

Related topics in brief

Topics linked to Star schema, each briefly explained:

All relationships of Star schema, grouped by type of relationship:

Related to

All 4 topics related to Star schema:

People working on this topic

People who work on Star schema:

Glossary

Community wiki

Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.

Experts and companies for this topic

Community members with expertise in Star schema:

Sources

Maintained by Mirko Peters (Microsoft MVP) · Last updated: