4.2 Semantic Star Schema & Relationships
Key Takeaways
- A star schema with narrow fact tables and conformed dimension tables is the recommended model shape for Power BI/Fabric semantic models and the highest-yield modeling topic on DP-600.
- Relationship cardinality is one-to-many (1:*) for most dimension-to-fact links; many-to-many should be resolved with a bridge table when possible.
- Single-direction cross-filtering flows from the one side to the many side; bidirectional filtering is powerful but can create ambiguity and performance problems.
- A bridge (factless or junction) table resolves true many-to-many relationships, such as customers belonging to multiple segments.
- Only one active relationship can exist on a given filter path; additional relationships are inactive and activated in DAX with USERELATIONSHIP.
Why the Star Schema Dominates Model Questions
Microsoft repeatedly recommends the star schema as the optimal shape for semantic models, and DP-600 reflects that. A star schema separates measurable events into fact tables (sales, events, transactions) and descriptive context into dimension tables (date, product, customer). Fact tables are long and narrow; dimensions are short and wide.
The exam rewards recognizing when a model is not a clean star — snowflaked dimensions, fact-to-fact relationships, or dimensions buried inside the fact — and choosing the redesign that restores a star.
Cardinality
Every relationship has a cardinality that describes how rows match across the two tables.
| Cardinality | Typical use | Notes |
|---|---|---|
| One-to-many (1:*) | Dimension to fact | The standard, preferred relationship |
| Many-to-one (*:1) | Fact to dimension | Same relationship viewed from the fact |
| One-to-one (1:1) | Splitting a wide table | Rare; often a design smell |
| Many-to-many (*:*) | Two non-unique key columns | Powerful but can produce ambiguous results; prefer a bridge |
The key column on the one side must be unique. If it is not, the relationship is invalid or becomes many-to-many.
Filter Direction
Cross-filter direction controls how a filter propagates across a relationship. Single direction propagates from the one side to the many side (the dimension filters the fact) — this is the default and the safest. Both (bidirectional) lets filters flow in both directions, which is sometimes needed for many-to-many or dimension-to-dimension filtering but can introduce ambiguity (multiple filter paths) and slow performance. On the exam, prefer single direction and explicit DAX (CROSSFILTER) over enabling bidirectional everywhere.
Bridge Tables for Many-to-Many
A true many-to-many relationship — a customer in several segments, a student in several courses — is best modeled with a bridge table (also called a junction or factless fact table). The bridge holds the key pairs and sits between the two dimensions, converting one ambiguous many-to-many link into two clean one-to-many relationships. Direct many-to-many relationships in the model are supported but can produce non-additive, surprising totals; DP-600 scenarios usually want the bridge solution.
Active vs Inactive Relationships
When two tables are joined on more than one column (for example a sales fact with both OrderDate and ShipDate to Dim Date), only one relationship per path can be active. The others are inactive and dashed in the diagram. To use an inactive relationship in a measure, wrap the calculation with USERELATIONSHIP inside CALCULATE. Recognizing the role-playing dimension pattern and the USERELATIONSHIP fix is a recurring DP-600 item.
A sales fact table has both an OrderDate and a ShipDate, and the business needs measures by both dates against a single Date dimension. What is the correct modeling approach?
Customers can belong to multiple marketing segments and each segment contains many customers. Reporting segment-level sales currently shows inflated totals. What is the best fix?