.svg){kind=small}
A star schema is a data-modelling approach in which one or more central fact tables are connected to surrounding dimension tables. It is one of the most important concepts for people who move beyond basic Power BI reports and begin building reusable semantic models with several related tables.
Microsoft recommends star schema principles because they align well with how Power BI filters, groups and summarises data. Dimension tables are typically used for filtering and grouping, while fact tables store the events or measurements that need to be aggregated.
Why is it called a star schema?
When shown as a diagram, the central fact table sits in the middle with dimension tables around it. Relationships radiate outward, creating a shape that resembles a star.
A sales model might contain:
- FactSales in the centre
- DimDate
- DimCustomer
- DimProduct
- DimSalesperson
- DimRegion
Each dimension provides a different way to filter, group or describe the sales stored in the fact table.
What is a fact table?
A fact table stores observations, transactions or measurable events.
Examples include:
- sales transactions
- course bookings
- website visits
- stock movements
- support tickets
- budget values
- employee absences
Fact tables often contain:
- keys linking to dimensions
- numeric values such as quantity, cost or revenue
- dates or event identifiers
Microsoft's Power BI guidance notes that fact tables generally contain many rows and can continue to grow over time.
What is a dimension table?
A dimension table describes a business entity or analytical perspective.
Typical dimensions include:
- Customer
- Product
- Date
- Employee
- Supplier
- Location
- Course
A Product dimension might contain one row per product with columns such as Product Name, Category, Brand and Colour. A Customer dimension might contain Customer Name, Sector, Region and Account Type.
Dimension tables are commonly used in report slicers, rows, columns and filters.
Fact table vs dimension table
| Fact table | Dimension table |
|---|---|
| Stores events or measurements | Describes business entities |
| Usually many rows | Usually fewer rows |
| Contains foreign keys to dimensions | Contains a unique key |
| Used mainly for summarisation | Used mainly for filtering and grouping |
| Examples: Sales, Bookings, Transactions | Examples: Customer, Product, Date |
A simple Power BI star schema example
Imagine you want to analyse course bookings.
The central FactBookings table might contain:
- BookingID
- DateKey
- CourseKey
- CustomerKey
- DelegateCount
- BookingValue
The surrounding dimensions could include:
- DimDate: Date, Month, Quarter, Year
- DimCourse: Course Name, Category, Duration, Delivery Format
- DimCustomer: Customer Name, Sector, Region
With those relationships in place, one Total Booking Value measure can be analysed by month, course category, customer sector or combinations of those dimensions without duplicating descriptive data in every booking row.
Why not keep everything in one big table?
A single flat table can work for small reports, especially when the data arrives in a clean spreadsheet. Problems appear as reporting becomes more sophisticated.
A large flat table can:
- repeat the same descriptive text thousands of times
- use more model memory
- make relationships between business entities less clear
- make reusable measures and filtering logic harder to understand
- create ambiguity when multiple fact-like processes are combined
A star schema separates descriptive context from measurable events and creates clearer analytical roles for each table.
What is granularity or grain?
The grain of a fact table defines what one row represents.
This is one of the most important data-modelling decisions.
For example, a sales fact table might contain:
- one row per invoice
- one row per invoice line
- one row per product per day
- one row per product per month
Those are different grains and answer different questions.
Microsoft's star schema guidance stresses that fact tables should load data at a consistent grain. Mixing transaction-level and monthly summary data in the same fact table can produce confusing results.
Why is a Date table important?
Date is one of the most common dimensions in analytical models.
A dedicated Date table can provide:
- day
- week
- month name
- month number
- quarter
- calendar year
- financial year
- weekday/weekend classifications
It also provides a consistent foundation for DAX time-intelligence calculations.
Rather than storing descriptive month and year values repeatedly in every fact row, the fact table links to the Date dimension through a relationship.
How do Power BI relationships work in a star schema?
A typical relationship is one-to-many.
The dimension table sits on the “one” side because its key is unique. The fact table sits on the “many” side because many fact rows can relate to the same dimension member.
For example, one product can appear in thousands of sales rows.
Microsoft explains that the relationship cardinality helps determine which table is playing the dimension role and which is playing the fact role.
Which direction should filters flow?
In a straightforward star schema, filters normally flow from the dimension table to the fact table.
When a report user selects Product Category = “Training”, the Product dimension is filtered and that filter propagates to the related fact rows. Measures then calculate only over the relevant facts.
Beginners sometimes turn on bidirectional filtering to solve an immediate report problem. This can introduce ambiguity and make models harder to understand. It is usually better to understand why the relationship is needed before changing filter direction.
Why is star schema good for DAX?
DAX works most predictably when the semantic model has clear relationships and well-defined table roles.
A measure such as:
Total Sales = SUM(FactSales[SalesAmount])
can then respond naturally when report users filter dimensions such as Date, Product or Customer.
The formula stays simple because the model carries much of the business structure.
This illustrates an important Power BI principle: a good data model can make DAX simpler.
See What Is DAX in Power BI? for more on measures and filter context.
What is a semantic model?
Power BI uses the term semantic model for the structured analytical layer that contains tables, relationships, measures and other metadata used by reports.
A well-designed semantic model translates raw source data into business-friendly analytical structures.
Instead of report authors repeatedly interpreting cryptic database fields, the model can provide meaningful table names, relationships and reusable measures.
Star schema and Power Query
Power Query is often used to shape source data into suitable fact and dimension tables before it is loaded into the semantic model.
For example, you may need to:
- remove unnecessary columns
- correct data types
- create a distinct dimension table
- merge reference data
- append monthly fact files
- standardise keys
Microsoft notes that Power BI semantic models depend on Power Query to import or connect to data, and complex or high-volume scenarios may be better served by building a proper data warehouse upstream.
Star schema and SQL
SQL knowledge helps when analytical data originates in relational databases. Analysts can inspect source tables, understand keys and retrieve the columns needed for facts and dimensions.
Operational database structures are often optimised for transactional processing rather than reporting, so the analytical model does not necessarily copy the source design directly.
See What Is SQL and Why Should You Learn It? for a beginner-friendly introduction.
What is a snowflake schema?
A snowflake schema normalises some dimension data into additional related tables.
For example, Product might relate to Subcategory, which then relates to Category rather than storing the category information directly in one Product dimension.
Microsoft's Power BI guidance generally recommends avoiding unnecessary snowflaking when a denormalised dimension can produce a simpler model for reporting.
The reason is usability. Report authors and users usually find one descriptive dimension easier to understand than a chain of small related tables.
Can a Power BI model have more than one fact table?
Yes. Many real models contain several fact tables.
A training business might have:
- FactBookings
- FactEnquiries
- FactWebsiteSessions
- FactTargets
Some dimensions such as Date, Course or Customer may be shared across several facts.
This approach is sometimes called a fact constellation or galaxy schema, though Power BI practitioners often continue to describe the overall dimensional approach using star schema terminology.
What is a role-playing dimension?
A role-playing dimension is one dimension that has several logical relationships to a fact table.
A Date table could relate to:
- Order Date
- Dispatch Date
- Invoice Date
Each relationship represents a different role played by the same type of dimension.
Power BI models need careful design when one dimension participates in several date roles because only one relationship between the same two tables can normally be active at a time.
What is a surrogate key?
A surrogate key is an artificial identifier used to uniquely identify a dimension row rather than relying directly on a business field such as Product Code.
Surrogate keys are especially important in data warehousing where business identifiers can change or where historical versions of a dimension member need to be preserved.
Not every small Power BI model needs a sophisticated warehouse key strategy, but understanding the concept becomes useful as data architecture grows.
What are slowly changing dimensions?
Dimension attributes can change over time. A customer may move region, an employee may change department or a product may move category.
A slowly changing dimension is a design approach for deciding how those changes should be represented historically.
For example, should last year's sale be reported against the customer's current region or the region they belonged to when the sale occurred?
That question affects the model and should be decided based on the analytical requirement.
Common star schema mistakes in Power BI
- Mixing facts and dimensions in one table. The table's analytical purpose becomes unclear.
- Using inconsistent fact grain. Rows represent different levels of detail.
- Creating many-to-many relationships without understanding why. Results can become difficult to reason about.
- Using bidirectional filtering everywhere. Ambiguous filter paths can develop.
- Skipping a proper Date table. Time analysis becomes harder to control.
- Keeping unnecessary source columns. The model becomes larger and harder to navigate.
- Creating complex DAX to compensate for poor modelling. The formula layer becomes harder to maintain.
- Over-normalising dimensions. The report model becomes more complex for business users.
How do you build a star schema in Power BI?
A practical beginner workflow is:
- Identify the business process you want to analyse.
- Define the grain of the fact table.
- Identify the measurements stored at that grain.
- Identify the dimensions needed to describe and filter those facts.
- Prepare the tables in Power Query or upstream systems.
- Create one-to-many relationships from dimensions to facts.
- Add a suitable Date dimension.
- Create reusable DAX measures.
- Hide technical keys that report users do not need.
- Test filtering and totals using realistic report scenarios.
Do all Power BI reports need a star schema?
No. A report based on one clean table does not need to be split artificially into several tables simply to resemble a star.
Star schema becomes especially valuable when:
- you have several related entities
- descriptive data is repeated heavily
- the model supports several reports
- you need reusable DAX measures
- performance and usability matter
- multiple fact processes need to share common dimensions
The goal is not to maximise the number of tables. It is to create a model that represents the business clearly and performs well.
Frequently asked questions
Is star schema mandatory in Power BI?
No, but Microsoft recommends star schema principles because they align well with Power BI semantic models and can improve usability and performance.
What goes in the centre of a star schema?
A fact table normally sits at the centre. It stores the events or measurements being analysed.
Can a dimension connect to several fact tables?
Yes. Conformed dimensions such as Date, Customer or Product can provide consistent filtering across multiple fact tables when designed appropriately.
Should measures go in fact tables?
Measures can technically be assigned to different tables, but many model designers organise them in the relevant fact table or a dedicated measures table for usability. The best convention depends on the model and organisation.
What is the difference between a star schema and a flat table?
A flat table combines descriptive and transactional information into one structure. A star schema separates facts from dimensions and relates them through keys.
Does star schema make Power BI faster?
A well-designed dimensional model can improve performance and usability, though overall performance also depends on data volume, DAX, storage mode, source systems, visuals and many other factors.
Develop your Power BI data-modelling skills with ExperTrain
Star schema design becomes increasingly important as Power BI reports move beyond a single imported spreadsheet.
ExperTrain's Power BI Desktop Intermediate course develops data modelling, relationships, Power Query and DAX skills, while Power BI Desktop Advanced builds on those foundations for more complex analytical solutions.
If you are new to Power BI, start with Power BI Desktop Introduction.
You can also use the Power BI Glossary for explanations of fact tables, dimensions, relationships, semantic models and DAX.
Further reading
Found this article useful? Add ExperTrain as a Preferred Source on Google to help surface more of our training guides, articles and learning resources.
.png){kind=small}
