Semantic models are not documentation-free zones

The manual, click-based configuration of semantic models is a structural liability that no amount of documentation can fix. When you build large-scale models in Microsoft Fabric or Power BI, the GUI provides a false sense of control while hiding the technical debt accumulating in your metadata.

Consider a hypothetical pipeline that manages a large-scale semantic model for a retail organization. The model contains hundreds of measures, dozens of calculated columns, and complex relationships. As the model grows, developers routinely rename columns directly in the Power BI Desktop file, create measures with inconsistent naming conventions, and forget to define format strings or descriptions. Eventually, the model becomes impossible to maintain, and the "technical debt" manifests as slow query performance and unintuitive reports for end-users.

Symptoms

An engineer managing this hypothetical model would likely notice these symptoms:

1. Inconsistent user experience: Reports suffer from "measure sprawl," where five different versions of a Total Sales calculation exist with slightly different logic, leading to inconsistent numbers across the business.
2. Performance degradation: Queries against the model take longer than expected, particularly in Direct Lake mode where suboptimal DAX can trigger fallback to DirectQuery.
3. Metadata chaos: Essential properties like DisplayFolder, Description, and FormatString are missing, making the model opaque to self-service analysts.
4. False leads: Engineers often spend hours debugging individual DAX queries, assuming the engine is at fault, when the actual issue is a lack of standardization in the underlying Tabular Object Model (TOM).

Photo by Mohamed Nohassi on Unsplash

1. The "Default Sorting" Trap

A common issue occurs when columns used for axes in visuals are not properly associated with a sort-by column (e.g., a MonthName column should be sorted by a MonthNumber column). Without this, your visuals look unprofessional and confusing.

Confirmation: Use Tabular Editor to connect to your semantic model. Navigate to the Tables folder and inspect a date or category column. If SortByColumn is empty, the model is likely defaulting to alphabetical order.

Fix: Create a Best Practice Analyzer (BPA) rule. In Tabular Editor, go to Tools > Manage BPA Rules. Add a new rule with this expression:

// Checks if columns that look like months have a SortByColumn set
obj.Name.Contains("Month") && obj.SortByColumn == null

Then, define the fix action to prompt the developer to select the appropriate numeric sorting column.

2. Inconsistent Measure Categorization

In large models, measures scattered at the root of a table are a sign of neglect. They should be grouped in DisplayFolders to assist the end-user.

Confirmation: Inspect the metadata of your measures. If DisplayFolder is empty for most measures, the model lacks logical structure.

Fix: Automate the detection of "orphan" measures. Add this BPA rule:

// Flag any measure that does not belong to a display folder
obj is Measure && string.IsNullOrEmpty(obj.DisplayFolder)

This forces developers to organize their work as they develop, preventing the "wall of measures" problem before it reaches the production workspace.

3. Missing Documentation and Descriptions

In regulated industries, data lineage and dictionary definitions are non-negotiable. If a measure is created without a Description property, it is effectively a "black box" calculation.

Confirmation: Run the BPA scan. If you see dozens of warnings for "Missing Description," your model is poorly documented.

Fix: Use a BPA rule that enforces documentation:

// Flag measures that lack a description
obj is Measure && string.IsNullOrEmpty(obj.Description)

You can enforce this by setting the rule to "Error" rather than "Warning," preventing the deployment of models that fail to meet these documentation standards.

Photo by Joshua katt on Unsplash

4. Suboptimal DAX Patterns

Some DAX functions are notoriously expensive. For example, using FILTER(Table, ...) over an entire table instead of KEEPFILTERS or specific columns can lead to massive performance overhead.

Confirmation: Review your model for high-cardinality columns used in filters. If you see large tables being filtered in their entirety, your Direct Lake performance will suffer.

Fix: Use the following BPA rule to flag the use of FILTER on large tables:

// Flag the use of FILTER on tables with more than 10,000 rows
obj is Measure && obj.Expression.Contains("FILTER") && obj.Table.Partitions[0].Rows > 10000

Note: Ensure your model is connected to the Fabric workspace to allow the BPA to access partition row counts.

Preventing it

The transition from "manual development" to "automated governance" requires treating your Tabular Model Definition (TMDL or BIM file) as code.

1. BPA in the CI/CD Pipeline: Do not rely on manual checks. Integrate Tabular Editor’s command-line interface (CLI) into your Azure DevOps or GitHub Actions pipeline. If the BPA scan returns an error-level violation, fail the build before it hits the Fabric workspace.
2. Standardized Templates: Distribute a .pbit (Power BI Template) file that includes a pre-configured BPA.rules file. This ensures every developer starts with the same governance guardrails.
3. Direct Lake Awareness: Since Direct Lake mode in Fabric relies on efficient DAX to avoid fallback, use the BPA to specifically enforce "Direct Lake friendly" patterns. For example, warn against using USERELATIONSHIP on columns that aren't materialized, as this forces the engine to change its execution path.
4. Regular Audits: Even for projects currently in development, schedule a weekly BPA report. It provides a "scorecard" for the model’s health, identifying which areas are drifting from architectural standards.

By moving the review process from "human memory" to "automated metadata analysis," you eliminate the subjectivity that plagues model development. Treat your semantic model like any other piece of software, and the infrastructure will remain stable even as the data volume grows.

---

Tags: #microsoftfabric #powerbi #dataquality #architecture

Cover photo by Growtika on Unsplash.

---

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.