4.2 Semantic Star Schema & Relationships

Why the Star Schema Dominates Model Questions

Microsoft repeatedly names the star schema as the optimal shape for semantic models, and DP-600 reflects that bias. 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 (many rows, few columns — mostly keys and numeric measures); dimensions are short and wide (few rows, many descriptive attributes).

The exam rewards recognizing when a model is not a clean star and choosing the redesign that restores one. Watch for three anti-patterns:

• Snowflaked dimensions — a dimension split across multiple normalized tables (Product to Category to Department). Flatten them into one dimension for clarity and fewer relationship hops.
• Fact-to-fact relationships — two fact tables joined directly. Route them through a shared (conformed) dimension instead.
• Dimension data buried in the fact — descriptive text columns living in the fact table inflate cardinality. Move them to a dimension keyed by an integer surrogate.

VertiPaq performs best on a star because filters propagate along short, unambiguous one-to-many paths.

Cardinality

Every relationship has a cardinality describing how rows match across the two tables.

The iron rule: the key column on the one side must contain unique, non-null values. In Direct Lake and Import models, queries actually fail if duplicate values are detected on a one-side column at query time, rather than silently returning wrong results. When the 'one' side is not unique, you either have a data-quality problem to fix upstream or a genuine many-to-many that needs a bridge. A common DP-600 distractor offers '1:1' as a fix for a many-to-many symptom — it is wrong because the keys are not unique.

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 choice; it keeps filter paths deterministic and fast.

Both (bidirectional) lets filters flow in both directions. It is occasionally required for legitimate dimension-to-dimension filtering through a bridge or to make a slicer on the many side filter the one side. But bidirectional filtering can introduce ambiguity when multiple filter paths exist between two tables, and the engine then refuses to resolve the model or returns surprising results. It also costs performance.

Exam guidance: prefer single direction plus explicit, surgical DAX over enabling 'Both' globally.

• Use CROSSFILTER(Table1[Key], Table2[Key], BOTH) inside a measure to enable bidirectional behavior only where one calculation needs it.
• Use TREATAS to push a virtual filter from one table onto another without any physical relationship at all.
• 'Enable bidirectional on everything' is almost always the wrong answer on the exam.

Bridge Tables for Many-to-Many

A true many-to-many relationship — a customer in several marketing segments, a student in several courses, a product in several promotions — is best modeled with a bridge table (also called a junction or factless fact table). The bridge holds only the key pairs (CustomerKey, SegmentKey) and sits between the two dimensions, converting one ambiguous many-to-many link into two clean one-to-many relationships:

Dim Customer (1) --> (*) Bridge (*) <-- (1) Dim Segment

The payoff is correct, predictable aggregation. A direct many-to-many relationship between Customer and Segment is supported by the engine but produces non-additive totals: summing segment-level sales can double-count a customer who belongs to two segments, inflating the grand total. A bridge resolves the path so each customer's sales are attributed cleanly.

DP-600 scenarios that describe 'segment totals that add up to more than total sales' or 'inflated category figures' almost always want the bridge-table answer. Hiding a table, changing storage mode, or forcing 1:1 are classic distractors that do not fix the underlying math.

Active vs Inactive Relationships (Role-Playing Dimensions)

When two tables are joined on more than one column — for example, a Sales fact with both OrderDate and ShipDate pointing at one Dim Date — only one relationship per filter path can be active. The others are inactive and shown as dashed lines in the model diagram. This is the role-playing dimension pattern.

To use an inactive relationship in a calculation, wrap it with USERELATIONSHIP inside CALCULATE:

Sales by Ship Date =
CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) )

The base [Total Sales] measure keeps using the active OrderDate relationship; the ship-date variant temporarily activates the inactive one for that single evaluation. Recognizing this pattern — and rejecting the wrong fixes — is a recurring DP-600 item. Wrong answers include duplicating the entire Date dimension (this breaks dimension conformance and explodes the model with two date tables), enabling bidirectional filtering (which does nothing for dual dates), and merging the two date columns in Power Query (which destroys the ability to slice by either date independently).