Power BI Data Modeling: Build a Solid Star Schema
Star Schema Modeling is an intermediate-level course of 5 lessons (about 1 h 35 min), with 15 exercises and 15 quiz questions. Prerequisites: Power Query: Import and Transform, Database Modeling. The course needs a Windows virtual machine with Power BI, planned for a later phase of the platform.
- Power BI
- Intermediate
- 5 lessons
- 1 h 35 min
- 15 exercises
- Windows virtual machine (phase 2)
The course in brief
A good report rests on a good model. This course teaches you to turn a flat table into a star schema, create reliable relationships, build a date table and handle several fact tables. You finish with the readability rules and storage modes that make a model fast and easy to use.
What you will be able to do
- Identify facts, dimensions and granularity in a dataset
- Build fact and dimension tables with Power Query
- Create relationships with the right cardinality and the right filter direction
- Create, mark and use a date table, including with an inactive relationship
- Model several fact tables with conformed dimensions
- Apply readability and performance best practices
Syllabus: 5 lessons
- Explain the limits of a single flat table
- Distinguish a fact table from a dimension table
- Define the grain of a fact table
- 02
Building Fact and Dimension Tables with Power Query
- Create dimensions from a flat table using references
- Guarantee the uniqueness of dimension keys
- Slim down the fact table to keep only keys and values
- 03
Relationships: Cardinality, Filter Direction, Active Relationships
- Create and edit relationships in Model view
- Choose the right cardinality and filter direction
- Understand inactive relationships
- 04
The Date Table
- Create a complete date table in DAX or Power Query
- Mark the table as a date table and sort months correctly
- Handle several dates in a fact table
- 05
Best Practices: Multiple Facts, Readability and Storage Modes
- Add a second fact table with a different grain
- Make a model readable for report authors
- Know the storage modes and performance levers
Final project
Star Model for Sales and Targets
From ventes_plat.csv and objectifs_mensuels.csv, build a complete Power BI model: FaitVentes, FaitObjectifs, DimMagasin, DimProduit and a 2026 date table. The model must make it possible to compare sales and targets by store, region and month, and to analyze sales by order date or delivery date.
Deliverables
- The model's .pbix file
- A screenshot of Model view showing the star
- A control page with: sales by region, sales vs targets by store and by month, sales by order month and delivery month
- A half-page note describing the grain of each fact table and the relationship choices
Assessment criteria
- Unique-key dimensions and fact tables reduced to keys and values
- Unidirectional many-to-one relationships, one inactive relationship on the delivery date
- Continuous, marked date table, sorted months
- Exact figures: sales 415, targets 495, rate 83.8%, February 185 (order) and 145 (delivery)
- Readable model: hidden keys, grouped measures, hierarchies
Frequently asked questions
Why use a star schema in Power BI?
The star schema simplifies DAX measures, avoids filtering ambiguities and offers the best performance. It is the model recommended by Microsoft for Power BI.
Do you always need to create a date table?
Yes, as soon as you analyze over time. A continuous date table marked as such is essential for time intelligence functions.
When should you use a bidirectional relationship?
Rarely. Single direction is the rule; bidirectional can create ambiguities and slow down the model. It's preferable to enable it occasionally within a measure using CROSSFILTER.
What is the difference between Import and DirectQuery?
Import copies the data into the model for maximum performance; DirectQuery queries the source at every interaction for always up-to-date data.
This course, told for your job
Say what you want to be able to do: “Star Schema Modeling” takes its place in your path, with examples from your field. The concepts stay those of the course.