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. | Tier | Needs | What it gets you | | --- | --- | --- | | **TMDL on disk** — `definition/**` | nothing | Shape, relationships, declared cardinality, measure text, storage mode. Works offline and in CI. Most of §4 and §5 come from here. | | **Live metadata** — `INFO.VIEW.*` via executeQueries | model admin + Build; tenant setting | The model *after* binding: real storage mode, calculated columns, measure `[State]`. | | **Fabric notebook** — `sempy.fabric` | Fabric capacity, workspace contributor, **ReadWrite** on the model | Best Practice Analyzer (60+ rules, five categories) and Model Memory Analyzer. The only path to column-level memory and cardinality *for a Direct Lake model*. | 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](references/REFERENCE.md).