Chapter 3 · 9 levels
Data modelling and the star schema
Fact and dimension tables, grain, keys, relationships with the right cardinality and filter direction, a marked Date table, active and inactive relationships, hierarchies, and the star schema that keeps every measure simple.
StartA report is only as good as the model under it. Two tables that aren’t related make a slicer do nothing; a relationship pointing the wrong way makes a total silently wrong; a missing Date table breaks every year-to-date number. Most “my measure is wrong” questions are really model questions.
In this chapter you build Harbor & Pine’s sales model the way Power BI Desktop’s Model view does it. You tell facts from dimensions, name the grain of each table, and draw the relationships yourself in a practice diagram: which column to which, the cardinality, the direction filters flow, active or inactive. The diagram checks what Power BI checks (a “one” side must have unique keys, two active paths are ambiguous) and then compares your model with the one the report needs.
You also mark the Date table, learn why one relationship has to stay inactive, see why the star beats a snowflake or a spaghetti of tables, and finish by building the whole star schema in the boss level.
Levels
- Facts and dimensions
from Marcus Reed, Warehouse Operations Manager
fact table, dimension table, data model, star schema
- The grain of a table
from Linda Garcia, Finance Controller
grain, granularity, fact table, double counting, data model
- Your first relationship
from Rachel Kim, Category Manager
relationship, cardinality, many to one, cross filter direction, primary key, foreign key
- Relate order lines, orders and customers
from Jason Miller, CRM Manager
relationship, many to one, filter propagation, foreign key, cross filter direction
- Star, snowflake or spaghetti?
from Heather Lewis, Head of Data
star schema, snowflake schema, many to many, bidirectional filter, data model
- Connect and mark the Date table
from Brian Foster, FP&A Analyst
date table, calendar, mark as date table, relationship, time intelligence
- An inactive relationship for signup dates
from Samantha Price, Lifecycle Marketing Manager
inactive relationship, active relationship, ambiguous path, userelationship, role playing dimension
- Hierarchies and drill-down
from Greg Turner, Regional Sales Director
hierarchy, drill down, date table, visual axis, sort by column
- Boss: build the sales star schema
from Heather Lewis, Head of Data
star schema, relationship, date table, mark as date table, inactive relationship, data model