Multiple Stars in a Star Schema

Power BI developers learn that a star schema is the optimal data model design for usability and performance.

However, seeing an ideal “star schema” with only one fact table surrounded by multiple dimensions in almost every data modeling example doesn’t help those who are just starting to build their first data models. It creates a misunderstanding that a “star schema” means only one star (one fact table). People ask questions like, “I know that a star schema is best for Power BI, but what if I have multiple fact tables? Should I always merge them?”

Examples:
https://community.fabric.microsoft.com/t5/Desktop/star-schema-for-multiple-fact-tables/td-p/3060595
https://www.reddit.com/r/PowerBI/comments/1jlzg6g/is_a_star_schema_really_the_best/
https://www.reddit.com/r/PowerBI/comments/10a5eqg/do_you_actually_stick_to_a_star_schema/
https://www.reddit.com/r/PowerBI/comments/xu8osm/two_fact_tables_star_schema_question/

In reality, star schema design can incorporate multiple fact tables without breaking the paradigm. In this article, I want to clarify what a star schema really means.

➡️ What is widely known as a star schema doesn’t mean “a single star” (a single fact table). A “star schema” defines the relationships between fact tables and dimensions, but it doesn’t specify the number of fact tables in the entire data model.

“A star schema often contains multiple fact tables, and therefore multiple stars.” lean.microsoft.com​

➡️ It’s okay to have multiple fact tables (multiple stars) in a model. Sometimes it’s called a galaxy schema or a fact constellation schema [https://en.wikipedia.org/wiki/Fact_constellation], but I think most often (always?) we just say “star schema,” no matter how many stars we have. That’s how the misunderstanding started.

➡️ The main idea is that each fact table should have its surrounding dimensions—not everything should be merged into one giant fact table. A fundamental principle is that facts relate to dimensions, not (directly) to other facts. A fact constellation or a galaxy schema is not a special approach to data modeling, it’s just a natural result of having more than one fact table following star schema principles.

➡️ Dimension tables are often shared among multiple fact tables—for example, one Product table for Orders, Sales, and Inventory facts. A dimension that is used by two or more fact tables is known as a conformed dimension. A conformed dimension means the dimension’s attributes have the same meaning for all facts that reference it (e.g., the same product ID in Orders, Sales, and Inventory fact tables refers to the same product and its attributes). Conformed dimensions enable consistent filtering and reporting across different fact tables. That’s how you merge data from multiple stars into a single visual or a single report page with common filters and slicers. For example, using a conformed Date dimension allows you to compare sales on a given date with inventory on that same date in one view.

➡️ Not every dimension must be shared. If a fact has a dimension that isn’t relevant to other fact tables, that dimension simply connects to its one fact table. There’s no harm in that – it still fits the star schema pattern for that fact. Just be aware that such a dimension won’t directly filter the other fact tables since they aren’t linked (and that’s expected).

➡️ It’s okay to have various deviations (e.g., hierarchy bridges) from an ideal star schema. An ideal star schema is a foundation, but not the ultimate goal—the goal is to make data analysis possible. Deviations are acceptable if they serve analytical needs. The model can still be referred to as a star schema, as long as the overall “star” structure remains.

Here are a few points to keep in mind regarding dimensions in a multi-fact star schema:

💡 Keep facts separate; join via dimensions. Do not create direct relationships between fact tables in a star schema design. That’s the most important rule—the one that keeps your star schema a star schema.

💡 Use one dimension table per entity, reused across facts when applicable: If two fact tables both involve “Customer”, it’s usually best to have one Customer dimension table and have both facts link to it, rather than having duplicate customer tables. This ensures a single source of truth for customer attributes.

💡 Make sure the dimension’s data covers the necessary range (e.g., Date dimension has all dates needed for both facts).

💡 If a fact has a dimension that isn’t relevant to other fact tables, keep this dimension related to that fact table only. Don’t forget that filters and slicers based on this dimension will affect only that specific fact table.

💡 Create disconnected tables when needed. You may need a table that is not related to any other table for creating a custom legend (a manually entered list of items), for advanced filtering (sometimes a disconnected copy of an existing dimension is required), to define a custom structure for a visual (e.g., income statement, balance sheet), and for other purposes.

💡 In the Model view in Power BI Desktop, keep (and maintain easy to understand structure) the default “All tables” tab with all fact tables and all dimensions. Additionally, always create a new tab for each star (one fact table + its dimensions) [https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-modeling-view#create-separate-diagrams]. That’s the best way to keep a data model with multiple stars clear, easy to modify, document, and understand.