Fabric Semantic Model Audit

Auditing a semantic model

A review procedure over a finished model — stand back from it and report on what it is, not on the line you are typing. Everything else in the payload that touches a semantic model is authoring guidance: fabric-tmdl and coding-tmdl.md own conventions, fabric-tmdl-api owns deployment, code-review reviews a diff. This owns the corpus.

The output is a set of findings, not a rewrite. Do not edit the model unless asked separately.

1. Establish storage mode before anything else

This gates every memory claim you are about to make, and getting it wrong is this skill's main way of producing a confidently wrong answer. Read mode: on each table's partition in definition/tables/*.tmdl:

grep -rh "mode:" definition/tables/*.tmdl | sort | uniq -c

import, directLake, directQuery or dual. Mixed modes mean a composite model, which in turn means source groups — and cross source group relationships are limited (§5). Direct Lake has two forms, on OneLake and on SQL analytics endpoint, and they differ on nearly every limitation that matters to remediation; the connection expression in expressions.tmdl tells you which.

State the storage mode in the finding report header. An audit that prescribes an import-mode fix to a Direct Lake model is wrong even when every individual sentence in it is true.

2. Three tiers of evidence

Not a fallback chain. Each tier answers questions the others cannot, and you should say which tier a finding came from.

Start at the top tier. TMDL on disk answers the highest-value questions in this skill and costs nothing.

If a scripts/data/dax.sh wrapper is deployed in the repo (an internal local-cli template), prefer it over raw curl for the second tier — dax.sh -s tables|columns|measures|relationships are four canned INFO.VIEW projections. It is an accelerator that happens to be present, never a dependency: this skill loads at user scope in repos that have no scripts/data/.

For a local import model open in Power BI Desktop, the VertiPaq DMVs reach column-level memory with no capacity at all — that is pbid-tom-live's surface, not this one. It does not work for Direct Lake models or thin reports.

3. The shape dimension

Table type is not a property — it is inferred from relationships. Per the star-schema guidance, "the 'one' side is always a dimension table while the 'many' side is always a fact table." So classify by walking relationships.tmdl, never by name or by guessing from Dim/Fact prefixes.

The both-sides test. A table that owns a fromColumn in one relationship and a toColumn in another is acting as both fact and dimension. That is a snowflake chain stated in the data rather than inferred, and it is the single cheapest shape signal available offline.

"Flatten" names two opposite operations

Both directions are documented, and confusing them is the classic error.

• Snowflake → star: do it. "Generally, the benefits of a single model table outweigh the benefits of multiple model tables." The four named costs of keeping the snowflake are more tables loaded (storage and performance), longer filter-propagation chains, a cluttered Data pane, and — the one people forget — "it's not possible to create a hierarchy that comprises columns from more than one table."
• Star → one wide table: don't. Listed as a defect in as many words: "Not using star schema: Semantic models that use flat, denormalized tables or pivoted data structures make DAX less efficient and harder to write correctly… Unpivot wide tables into normalized structures." Ontology generation compounds it — entity types derive from tables and relationship types from relationships, so one wide table generates one entity type with no relationships (see fabric-ontology).

What an audit must not flag

These are sanctioned by the same guidance, and reporting them as defects is a false positive:

• Degenerate dimensions — an order number on the fact table is explicitly "an exception to the formerly introduced rule that you shouldn't mix table types."
• Junk dimensions — deliberate consolidation of small attributes.
• Disconnected tables — legitimate for what-if parameters and user input.
• Factless fact / bridging tables — the recommended way to relate two dimensions many-to-many.
• Planning models — a model built for planning in Fabric states row existence in the dimensions on purpose: a Subcategory table carrying its parent CategoryID, a validity table with an IsValid flag, a Scenario table with IsForecast / OpenFrom / OpenUntil, and a weight matrix reached through Blend as a measure rather than through a relationship. Check 1 fires on the first of those and is wrong here — those relationships are what generate the planning grid's rows, so collapsing the snowflake removes the thing the model exists to do. The validity tables trip check 9, but the two bullets above already stand that down; check 1 is the one this bullet is load-bearing for. A reporting model lets the fact table decide which rows appear; a planning model cannot, because it must represent futures that have no transactions yet. Recognise it and stand down; how to build one is out of scope for this skill.

Carry the hedge, so the report does not read as dogma: "optimal model design is part science and part art. Sometimes you can break with good guidance when it makes sense to do so."

4. Relationship health

In roughly the order an audit can actually detect them.

Inactive relationships with nothing that can activate them. The highest-value offline check in this skill, and it is two greps: count isActive: false in relationships.tmdl, then count USERELATIONSHIP across the tables. An inactive relationship with no USERELATIONSHIP naming its columns is unreachable by any calculation — it cannot affect a single query result, and it still costs (§5). Report the unreachable subset, not the raw inactive count.

Role-playing dimensions — count per (fact table, dimension table) pair, never per dimension. A date table on the to-side of four relationships is not evidence of anything: four different fact tables each with one active date relationship is a shared date dimension working exactly as designed. The finding is when one table reaches one dimension more than once. Counting inbound relationships per dimension makes every correct shared date table look defective.

Where a genuine role-play exists, the documented default is to fix it, not to keep it: "Generally, we recommend defining active relationships whenever possible… Using only active relationships means that role-playing dimension tables should be duplicated in your model." The cost is named and small — "duplication of the date dimension table resulting in an increased model storage size… Because dimension tables typically store fewer rows relative to fact tables, it's rarely a concern." Inactive relationships plus USERELATIONSHIP remain sanctioned when there is no need to filter by two roles at once and the measures exist. In Direct Lake the duplication fix is much harder — see §6 before prescribing it.

Bidirectional cross-filtering. "Can impact negatively on performance" and can create ambiguous propagation paths. Where ambiguity exists, Power BI resolves it by priority tier first and path weight second, and returns an error when two paths tie — so a bidirectional relationship added "to make it work" can break a different visual later.

Regular vs. limited. Not a settable property — inferred from cardinality and source, which is exactly why humans miss it and an audit should compute it. A relationship is limited when it is many-to-many cardinality, or crosses source groups in a composite model. Limited relationships get no data structures built, join with INNER JOIN semantics, add no blank virtual rows for referential-integrity violations, and break RELATED. The absence of blank rows is the sharp end: violations silently vanish from results instead of showing as (Blank).

Cardinality declarations. TMDL omits fromCardinality / toCardinality at the default. Absent means default, not unknown — but note that for Direct Lake, web modeling issues no validation queries for cardinality or cross-filter selections; user selections are assumed correct. A declared cardinality in a Direct Lake model may never have been checked against data.

One-to-one — "likely represents a suboptimal model design because of the storage of redundant data."

DateTime relationship columns. The engine only has DateTime; Date is a formatting construct. A time component still counts, so keys silently fail to match. Fix in Power Query, not the Modeling tab.

Assume referential integrity — DirectQuery only. When integrity is actually compromised the INNER JOIN silently understates results.

The documented performance ordering, fastest to slowest: one-to-many intra source group → many-to-many via an intermediary table with at least one bi-directional relationship → many-to-many cardinality → cross source group.

5. Memory and query cost — split by storage mode

This is where the design and performance halves fuse, and where the mechanism differs by mode. Do not generalise either bullet to the other.

• Import. Power BI "creates a data structure for each regular relationship at data refresh time," and — verbatim — "Inactive relationships are also expanded, even when the relationship isn't used by a calculation." So an unreachable inactive relationship is simultaneously a design finding and a refresh-cost finding. One finding, two dimensions; a skill split down the design/performance line would have to report it twice.
• Direct Lake. Join indexes are built at query time, not at refresh: "If the DAX query accesses columns from multiple tables, Direct Lake must build join indexes according to the table relationships." They are memory-resident and are evicted under memory pressure (the semiwarm state). Whether inactive relationships get join indexes built is not documented — say so rather than assuming the import behaviour transfers. The design finding stands regardless; the refresh-cost arithmetic does not.

Direct Lake performance is dominated by Delta-side factors rather than by the model: V-Order compression, segment size (aim for 1–16 million rows per row group), column cardinality, and destructive update patterns that defeat incremental framing. Name these and point at the lakehouse; they are not model edits.

Column-level memory and cardinality need model_memory_analyzer() in a Fabric notebook (or the VertiPaq DMVs via pbid-tom-live, import models only). Nothing in the offline tier reaches them — do not estimate.

6. Direct Lake constrains the remediations

Before prescribing a fix, check it is available in this storage mode.

• Duplicating a role-playing dimension runs into "adding multiple tables from the same data source table" being unsupported in Power BI Desktop and web modeling (XMLA external tools can, but Edit tables and refresh then error).
• Calculated tables are preview on Direct Lake on OneLake and unsupported on Direct Lake on SQL.
• Calculated columns on Direct Lake on OneLake are User Context only and unmaterialized — and "because they don't materialize, these calculated columns can't be used in relationships." So the calculated-column route to a surrogate key is closed.

The realistic Direct Lake remediation is usually upstream: add the role table to the lakehouse and bind it, rather than duplicating it in the model.

7. Downstream readiness

One pass per consumer, each deferring to the skill that owns it.

• Reports — the classic case: hidden helper measures, implicit measures, missing formatString, no descriptions.
• AI instructions blob — a different artifact from the model. Defer to fabric-semantic-model-ai-instructions.
• Data agent — the model-shape half only: non-descriptive names (TR_AMT, DIM_GEO_01), duplicate or overlapping measures, helper measures that should be excluded, implicit measures, ambiguous date fields, and hidden fields that break verified answers. Two governance points worth stating: report-scoped measures are invisible to the agent (move them into the model), and the DAX generation tool reads only model metadata and Prep-for-AI configuration — it ignores data-agent-level instructions, so model-specific guidance placed on the agent is silently dead. Defer agent configuration to fabric-data-agent.
• Ontology — carry only the model-shape consequences; the generation constraint matrix belongs to fabric-ontology. Cite it, don't restate it.

Non-descriptive names (check 13) are the one finding here with a documented bulk remediation. On a large model the finding is otherwise useless — "rename 400 columns by hand" is not an action anyone takes. The data-agent guidance names the tool: if a powerbi-modeling-mcp server is configured, an LLM can generate business-friendly names for tables, columns and measures in bulk. Same idiom as dax.sh in §2 — an accelerator that happens to be present, never a dependency, and this skill still loads where no MCP server is configured at all.

Two things stay true when you point at it. Reporting the finding is still this skill's job; running the rename is not — that is a write against the model, so it needs a separate ask (§8). And carry the source's own caveat rather than just the recommendation: review and validate the renames before saving, because a rename can break DAX expressions, relationships and other dependent objects. A bulk rename applied unreviewed converts a naming finding into a broken model.

8. Reporting

Per finding: what, the evidence (a file:line or the query that produced it), which dimensions it belongs to, the remediation and its stated cost, and which tier and storage mode it rests on. Separate what is measured from what is inferred. A finding whose remediation is unavailable in this storage mode is not a finding yet.

9. Constraints and false negatives

• INFO.VIEW.* blanks [Expression] for users without write permission. A measures listing with empty formulas means read-only access, not an empty model. This is the single most likely way this skill produces a confidently wrong answer.
• BPA and the memory analyzer need ReadWrite on the model — they go through TOM, which is stricter than the INFO.VIEW read path and a different failure mode. list_tables / read_table go over XMLA and need XMLA read-only enabled, a third gate again.
• Semantic link is Fabric-only — "Use of semantic link is supported only in Microsoft Fabric." There is no local entry point, which is why the second tier is a REST wrapper and not SemPy.
• Notebooks fail to run if the semantic model name ends with a whitespace.
• executeQueries limits: one EVALUATE per call, 100,000 rows / 15 MB, 120 requests per minute. A full metadata sweep is four calls, not one.
• The planning-model carve-out in §3 is documentation-derived, and exercised once. It comes from Microsoft's planning semantic-modeling guidance, fetched 2026-09-03. That day it was run against Microsoft's own companion sample — see tests/skills/fabric-semantic-model-audit/ — and an A/B against a copy with the carve-out stripped confirmed it is what stops check 1 prescribing "collapse the snowflake" on that model. But a published sample is not a production model, no real planning model has been audited from this machine, and Plan is a preview workload. If a check-1 finding is contested on planning grounds, re-read the source rather than insisting.
• The notebook tier is documented, not exercised. As of 2026-09-02 nothing in §2's third row has been run from this machine — it cannot be, without a capacity. Treat its invocations as first-party documentation rather than as verified-in-practice.

Long detail — the check catalogue, the INFO.VIEW and sempy.fabric invocations, the star-schema pattern vocabulary, and the Direct Lake constraint table — is in references/REFERENCE.md.