By: Audrey Gerred

Why I’m a Broken Record About Star Schema in Power BI

The Repetition Is Intentional

I talk about star schema again and again because it’s foundational. It’s not just a best practice — it’s the difference between scalable, performant, governable models and ones that become brittle, slow, and hard to trust. Power BI may not require it, but that doesn’t mean it’s optional.

---

Why Star Schema Is So Important

• Performance: Power BI’s VertiPaq engine uses columnar storage, which means data is stored and compressed by columns rather than rows. This allows for highly efficient scanning, filtering, and aggregation — especially when fact tables are narrow and well-structured. Star schema supports this by keeping fact tables slim and focused, while dimension tables tend to be much wider, which maximizes compression and speeds up query execution.
• Simplicity: Star schema simplifies the model by clearly separating facts from dimensions. But simplicity doesn’t mean fewer tables. If your model represents ten distinct business entities, it’s not simpler to put them into one ‘dimension’ table — it’s confusing.
• Semantic Clarity: Dimensions in a star schema represent real-world business concepts/entities (think customer information, product information, associate information, etc.). This makes it easier for users to understand the model, write DAX, and build reports. It also improves discoverability and trust — users can see what each table represents without guessing. As Mr. Kimball taught us, ‘the data warehouse is only as good as the dimension attributes’. Each entity (e.g., customer, product, profit center) deserves its own dimension to preserve semantic clarity and avoid tangled logic. For example, we shouldn’t find the name of a product that was bought in the same dimension table as the name of who it was sold to and the name of the sales-person that gets commission – these are three distinct business entities.
• Copilot & AI-readiness: Tools like Copilot rely on semantic structure to interpret natural language queries. A well-formed star schema gives Copilot the context it needs to generate accurate measures, filters, and insights. Without it, AI features become less reliable and harder to govern.

---

Why You Shouldn’t Ignore It — Even If Power BI Lets You

Power BI is flexible — but that flexibility can be dangerous. You can build snowflake schemas, flat tables, or spaghetti joins. But here’s what you risk:

• Slow performance due to inefficient joins and poor compression.
• Confusing relationships that make DAX harder to write and debug.
• Poor AI experiences — Copilot struggles with ambiguous or overly complex models.
• Certification risk — Models that don’t follow star schema are harder to endorse confidently.
• Semantic ambiguity — Mixing different business entities (e.g., customer name and profit center) into a single dimension table creates confusion. These entities often belong to different domains, have different grain, and serve different analytical purposes. When they’re lumped together, it becomes unclear what the table represents, which leads to modeling errors, misleading visuals, and broken trust.
• Storage inefficiency — Star schema supports columnar compression, which is most effective when columns contain repetitive, low-cardinality values. When models deviate from star schema — for example, by embedding dimension attributes in fact tables or creating wide, flat structures — compression suffers. This leads to larger memory footprints, slower refreshes, and degraded performance.

---

Is It Still a Star Schema If Only Part of Model Is Set Up With Star Schema?

Not really. A partial star schema is like a half-built bridge — it might work for now, but it’s not structurally sound. If some dimensions are snowflaked, if facts are mixed with dimensions, if many business entities are mixed in one dimension table or if relationships are ambiguous, you lose the benefits of clarity, performance, and semantic alignment.

---

Should a Model Be Certified If It Doesn’t Follow Star Schema?

This is where governance comes in. A certified model should meet a minimum standard of:

• Semantic clarity
• Performance
• Governability
• AI-readiness

If a model doesn’t follow star schema, it should be reviewed carefully. Exceptions might exist, but they should be rare and well-documented.

---

Should Copilot Be Used on Models That Don’t Follow Star Schema?

Technically, it can be used — but practically, it’s risky. Copilot relies on semantic structure to interpret user queries. Without a star schema:

• It may misinterpret relationships.
• It may suggest incorrect measures or filters.
• It may frustrate users with inconsistent results.

If you want Copilot to shine, give it a model that’s built to be understood.

---

Final Thought: Star Schema Is Not Just an Option — It’s a Mindset

Being a broken record about star schema means you’re advocating for clarity, performance, and trust. It’s not dogma — it’s discipline. And in a world of AI-powered analytics, that discipline matters more than ever.