Semantic Model

The semantic model is the contract between data and decisions. This topic measures how readable, stable, accurate, and scalable it is: star schema, relationships, and cardinalities…

Guide≈ 7 min readCheckedReading

In brief

The semantic model is the contract between data and decisions. This topic measures how readable, stable, accurate, and scalable it is: star schema, explicit relationships and cardinalities, controlled filter direction, dedicated calendar, dimension roles, hierarchies, metadata. See the Glossary for terms.

On this page

1. Why this topic
2. What the topic measures
3. The 5 levels
4. Milestones by level
5. Detailed reading by level
6. Partial achievement and rating
7. Move to the next level
8. See also

Level: 🟢→🔴 · Topic code: MOD

1. Why this topic#

A poorly built model makes everything else expensive: DAX measures working around ambiguous relationships, filters that “leak,” aggregates that become wrong as soon as you change visuals, degraded performance, bypassable RLS. The cost of a model defect grows with the number of reports connected to it: fixing a relationship after 40 linked reports breaks filters, bookmarks, and measures. On the other hand, a clean star makes measures short and visuals predictable.

⚠️ Pitfall

Symptom: “the total does not match the sum of the rows.” Frequent cause: many-to-many relationship or a misplaced degenerate dimension. Fix: return to a star, one fact table per grain.

2. What the topic measures#

Axis: from a “flattened data table” to a contractual, composable, scalable model.
Key indicator: share of tables compliant with strict star design, number of justified bidirectional/many-to-many relationships, number of reports broken by a model change.
Unit: level 1 to 5; control indicators in % of compliant tables/relationships.

3. The 5 levels#

Level	Short name	Promise	Typical indicator (order of magnitude)
1	Flat	The numbers come out, but only the author knows why and cannot reuse them.	1 wide table; unverified automatic relationships
2	Readable star	An analyst opening the model understands within 10 minutes where each measure and each axis is.	> 70% of tables classified as fact/dimension
3	Strict star	Aggregates stay accurate regardless of the visual, and a single calendar drives all time.	0 unjustified bidirectional relationships; 1 marked date table
4	Contractual	A model change is announced, versioned, and does not break any linked report without notice.	0 reports broken by changes over 12 months
5	Composable	Multiple teams build their reports on a certified model, extended by composition, without copying it.	> 90% of reports connected to a shared model

4. Milestones by level#

ID	Milestone	Verifiable evidence
BI-MOD-1.1	The model loads and produces usable totals	Report can be opened, totals checked once
BI-MOD-1.2	Source data is in the model (import or connection), not copy-pasted	Query pane / declared source
BI-MOD-1.3	Column types (number, date, text) are defined	No numeric column stored as text
BI-MOD-2.1	Each table is identified as fact or dimension	Naming convention (`FACT_`, `DIM_`) or description
BI-MOD-2.2	Relationships are created intentionally (not by unreviewed auto-detection)	Model view reviewed, relationships disabled if in doubt
BI-MOD-2.3	Technical columns (keys, IDs) are hidden	Fields hidden in report view
BI-MOD-2.4	Measures live in a dedicated table or folder	Display folders for measures
BI-MOD-3.1	Strict star: facts linked to dimensions, no arbitrary snowflake chaining	Model diagram with no fact table linked to another fact table
BI-MOD-3.2	1-many cardinality, single-direction filter by default; every exception is documented	Relationship list with rationale for exceptions
BI-MOD-3.3	A dedicated, continuous date table marked as “date table”	Property set; range covers data + horizon
BI-MOD-3.4	Grain of each fact table defined in writing	Description: “one row per visit × product”
BI-MOD-3.5	Hierarchies (time, geography, product) defined and tested	Hierarchies visible and usable in drill
BI-MOD-4.1	Dimension roles (order date/delivery date) handled via inactive relationships + USERELATIONSHIP, or role-playing calendars	Measures named by role, documented
BI-MOD-4.2	Stable surrogate keys and uniqueness controlled at source side	Dimension key uniqueness test in pipeline
BI-MOD-4.3	Any model change follows a cycle: announcement, version, change note	Model change log
BI-MOD-4.4	Complete descriptions for tables, columns, measures (used by Copilot/catalog)	> 95% of visible objects described
BI-MOD-5.1	Certified model reused in live connection by other reports	Number of “thin” reports (without their own model)
BI-MOD-5.2	Extension by composition (composite model) rather than copy	No known divergent clone
BI-MOD-5.3	Model drift detected automatically (source schema vs model)	Alert on source column change
BI-MOD-5.4	The model is a documented architecture artifact (who extends it, how, what SLA)	Model sheet published in the catalog

5. Detailed reading by level#

🟢 Level 1 — Flat#

Observable signs: one or two very wide tables (50+ columns), auto-detected relationships without control, calculated columns everywhere, untouched source column names.
What you gain: speed of first result.
What it does not guarantee: aggregate accuracy, reusability, readability by a third party.
Difficulty to reach: 1/5 · Effort: 1/5.

🟢 Level 2 — Readable star#

Signs: tables named by role, hidden keys, grouped measures; the star exists but allows deviations (one dimension linked to another, a few bidirectional relationships “to make it work”).
Gain: analyst onboarding in minutes, fewer reading errors.
Limit: exceptions are not justified; no guaranteed calendar.
Difficulty: 2/5 · Effort: 2/5.

🟡 Level 3 — Strict star#

Signs: clear star diagram, marked single calendar (date table), single-direction filter, written grain. Many-to-many relationships are either absent or replaced with an explicit bridge table.
Gain: short, predictable DAX; accurate aggregates; healthy foundation for performance and RLS.
Limit: the model remains “a project's model”; evolution happens without protocol.
Difficulty: 3/5 · Effort: 3/5.

🧪 Example

SFA common thread: FACT_VISITE (grain visit × presented product), dimensions DIM_CLIENT, DIM_PRODUIT, DIM_COMMERCIAL, DIM_DATE. Planned visit and completed visit refer to two dates: one active relationship (completed) and one inactive one (planned) activated by USERELATIONSHIP in the dedicated measure.

🟡 Level 4 — Contractual#

Signs: the model has an owner, a version, a change note; column names do not change without reason (see the common thread “a column keeps its datamart name”). Keys are checked before loading.
Gain: consumer trust, changes without surprises.
Limit: coordination effort; the model remains a monolith as long as it is not composable.
Difficulty: 4/5 · Effort: 4/5.

🔴 Level 5 — Composable#

Signs: one certified model, its local extensions (composite models with model chaining), thin reports. Source changes are detected before loading. The model is described for humans and AI tools.
Gain: one single source of truth; near-zero duplication cost.
Limit: a shared model becomes a contention point; governance must keep up (GOV).
Difficulty: 5/5 · Effort: 5/5.

✅ Good practice

Write a fact table's grain in one sentence in its description. If you cannot, the table mixes two grains.

💡 Expert tip

1-1 relationships are often a sign of a wrongly split dimension: merge them (or keep them only if required by a security role). A degenerate dimension (order number) can live in the fact table.

🤖 AI

An assistant can propose a star from a schema and draft column descriptions; it often misses the real business grain and suggests bidirectional relationships. Review is mandatory.

6. Partial achievement and rating#

Score based on the common README rule. Example of a “partial 5”: a certified model exists and feeds 60% of reports, but schema drift detection is still manual (BI-MOD-5.3 = 0.5) and two reports still carry a copied model (BI-MOD-5.2 = 0.5): average of level 5 milestones ≈ 0.75, score ≈ 4.74.

Quick evidence: model export in TMDL (see IND) and relationship review; analysis with Tabular Editor / BPA (TST).

7. Move to the next level#

Possible means (optional) — these are levers, not the promise:

Transition	Possible levers
1 → 2	Separate facts and dimensions; hide keys; use meaningful names
2 → 3	Remove bidirectional relationships without rationale; add a date table; write the grain
3 → 4	Create a change log; key uniqueness tests; named owner
4 → 5	Certified model + thin reports; composite model; schema drift monitoring

Pitfalls and gaming (Goodhart):

Aiming for “0 bidirectional relationships” by duplicating dimensions to work around it: you hide the defect, you do not fix it.
Counting descriptions without reading them: 95% of “Customer column” is worthless.
Freezing the model out of fear of breaking things: the contract is not immobility.
False positive: a pretty star diagram while the fact table mixes two grains.

8. See also#

Related topics: ETL (data feed) · DAX · PRF · RLS · GOV
Other matrices: PD — Modeling and Architecture · DL — Architecture and Design
Content: Cross-Cutting Power BI Best Practices · SQL Server in SaaS — Architecture · OneLake + SaaS Mobile Apps: Architecture · Version Control for Data: SQL and Power BI

✻ Written by Claude Sonnet 5.5