Bridge keys before dashboards: remodel the device fact
Helpful gold views that "just join service and device" hide many-to-many grain until a report page disagrees with itself. Filters double count. Role-playing dims break. Two pages that should reconcile never do.
Deploy bridge keys as a first-class artifact and remodel the service-device fact before you polish visuals. Matching logic must be shared with telemetry enrichment or the numbers diverge by construction. Semantic role-playing dims need matching foreign key types. Remodel first. Visual polish second.
Figure 1. Many-to-many grain leaks into blank or double-counted visuals. Remodel the fact before polishing pages. Source: Microsoft Learn: understand relationships.
Figure 2. Bridge keys and filter direction are part of the remodel. Dashboards inherit whatever grain you leave in gold. Source: Microsoft Learn: understand relationships.
I learned this when a service page and a device page both looked plausible and refused to reconcile on the same week. The gold layer had a convenience view that flattened a many-to-many into something that looked one-to-many on a good day. Dashboards were not the bug. The grain was.
The core idea
If the relationship is many-to-many in the business, the model needs an explicit bridge (or a fact that tells the truth). Convenience views are not a grain strategy.
Bridge keys belong in gold with validation, not as DAX that "fixes it in the report." Shared matching with telemetry enrichment keeps estate-wide counts honest. Role-playing dimensions multiply the cost of one wrong key type. Report polish on a broken grain trains users to distrust every number.
A model that stays explainable
1. Name the grain out loud
Write whether a row is a service event, a device assignment, a billable line, or a bridge between service and device. If two truths fit in one table, split them. Ambiguous grain is how double counting enters through the side door while the star schema diagram still looks clean.
2. Promote bridge keys to first-class columns
Surrogate or business bridge keys should be documented, tested for uniqueness at the intended grain, and used consistently in the semantic model. Do not leave bridges as "the join we do in that one view." First-class keys survive handoffs.
3. Share matching logic with telemetry enrichment
If devices resolve to contracts or sites with a module for events, service facts use the same module. Divergent matching guarantees divergent headlines. Shared logic is cheaper than reconciliation meetings.
4. Align FK types for role-playing dims
Date roles, site roles, and device roles need matching types on every relationship endpoint. An int64 device key next to a string role-play is a blank visual waiting for a demo. Fix gold and TMDL together.
5. Validate bridges before pages
Acceptance tests: bridge uniqueness, orphan rates, reconciliation between service-only and device-only slices on fixture keys. Do not accept "the page looks right" as grain proof. Visuals can look right while counting the wrong entity twice.
6. Sequence work as remodel, then visuals
When stakeholders ask for a prettier page, check whether the fact still lies. Remodel tickets unblock report tickets. Mixing them produces beautiful wrongness.
Failure modes I design against
Convenience gold views. Many-to-many flattened until it fails.
DAX bridges. Business rules hidden in measures nobody owns.
Unique matching for events vs service. Estate metrics argue forever.
Mixed FK types on role-plays. Blank visuals blamed on "Power BI being weird."
Page-first delivery. Polish locks in the wrong grain.
No orphan metrics. Silent drops of devices or services without contracts.
A minimal remodel checklist
List current consumers of the convenience view. Define the bridge grain in one sentence. Add keys and tests. Point the semantic model at the new fact/bridge. Rebuild affected pages. Only then delete or freeze the convenience view. Freezing without a delete date guarantees a zombie path. Publish the matching module version next to the remodel so telemetry work cannot drift a week later.
Why "helpful" gold views fail under filters
A convenience view that duplicates device attributes onto every service line will look fine with no filters. Add a device slicer and a service-type slicer together and cardinality explodes or collapses depending on relationship direction. The semantic model cannot invent a bridge you refused to store. If product owners want both "services per device" and "devices per service" as first-class questions, they have already asked for a bridge. Your job is to stop pretending a flat view answers both without a grain statement.
Semantic tests that catch remodel regressions
Keep a tiny fixture pack: one device with two services, one service with two devices if the business allows it, one unmatched device, one unmatched service. Assert counts from both directions after each gold change. Add a role-playing date check so "service date" and "install date" do not silently share a wrong type. These tests are dull. They are also how you stop rediscovering the same bug after every "small" enrichment tweak.
Trade-offs
Explicit bridges add tables and teaching cost. Hidden many-to-many adds trust cost. Shared matching modules require package discipline across notebooks. Remodel delays visible UI progress; it prevents rework of that UI. Strict orphan reporting can surface uncomfortable source gaps. Surface them; do not hide them in inner joins.
What I would put on an ADR
- Service-device many-to-many is modeled with an explicit bridge/fact grain statement.
- Bridge keys are validated (uniqueness, orphans) in gold pipelines.
- Matching/enrichment logic is shared with telemetry facts.
- Role-playing relationship endpoints share types with gold FKs.
- Report work on affected pages waits on remodel acceptance tests.
- Convenience views that hide grain are deprecated with a removal date.
Primary references: Power BI star schema and relationship guidance on Microsoft Learn, plus Fabric gold-layer modeling practice. Pair diagrams with the grain sentence in the repo.
Closing
Dashboards cannot repair a lying fact. They can only make the lie presentable.
Bridge keys first. Shared matching next. Types aligned. Then polish the page. Reconciliation stops being a weekly mystery when grain is honest.