Power BI Semantic Model Optimization: What Drives Fabric Capacity Cost

Executive summary

Most organizations discover their semantic model is a problem the same way: a report that used to load in seconds starts taking minutes, users refresh twice out of habit, and someone in finance asks why the Fabric capacity needs to be scaled up again.

The instinct is to buy more capacity. It is the fastest fix, it always works, and it is almost always the wrong first move. In our experience auditing enterprise Power BI estates, most of the capacity pressure does not come from data volume. It comes from a small number of modelling decisions that were reasonable in a prototype and became expensive when that prototype went into production and grew by two orders of magnitude.

Microsoft Fabric has raised the stakes. In the Premium era, a badly designed model was a performance annoyance contained within one workspace. Under Fabric’s shared Capacity Unit model, that same design consumes compute every other workload on the capacity is competing for. A single inefficient measure on a single report is now a line item on a shared bill.

This article covers how to approach semantic model analysis systematically: the diagnostic sequence that surfaces the real cost drivers, the structural foundations that separate a model that scales from one that does not, and the specific anti-patterns that consume capacity disproportionately to the business value they deliver.

Why the Semantic Model Became a Cost Centre

To understand why modelling decisions now show up on an invoice, you need to understand how Fabric accounts for compute.

Fabric capacity is measured in Capacity Units, evaluated in 30-second timepoints. When an operation demands more computing than your SKU provides, Fabric allows it to burst, then smooths that consumption across future timepoints: roughly 5 to 64 minutes for interactive operations such as report queries, and up to 24 hours for background operations such as scheduled refreshes.

This design is deliberately forgiving, and that is exactly what makes it hard to manage. A capacity can run above 100% utilization for extended periods without anyone noticing anything wrong, because smoothing is absorbing the excess. The signal that matters is not peak utilization, it is how much future capacity has already been committed. When that runway is exhausted, throttling begins in progressive stages: a 20-second delay applied to new interactive operations, then rejection of interactive requests, then rejection of everything including refreshes.

Recovery is arithmetic, not instantaneous. A capacity that has consumed 250% of its available background window does not clear when the load stops. It clears when the borrowed compute has been paid back, which can take well over a day.

Two consequences follow for anyone responsible for a Power BI estate on Fabric:

• Capacity problems present user experience problems. By the time report consumers complained about slowness, overconsumption has been accumulating for some time.
• Scaling the SKU treats the symptoms. It buys headroom, and headroom is sometimes the right purchase. But if a model consumes several times the compute it needs to, a larger SKU means paying several times more for the same inefficiency.

For teams on Direct Lake, the constraint is sharper. The Fabric SKU sets a ceiling on how much data can be paged into memory for a semantic model, alongside hard guardrails on rows, Parquet files, and row groups per table. Exceed them on Direct Lake over a SQL analytics endpoint and queries silently fall back to DirectQuery, losing the in-memory performance the architecture was chosen for. Exceed them on Direct Lake over OneLake, where no fallback exists, and operations fail. In both cases, how much memory the model needs is determined by modelling decisions, not by the size of the underlying data.

This is worth stating plainly, because it is the most common misconception we encounter: Direct Lake is a connectivity mode, not a performance strategy. It removes the import refresh cycle. It does nothing to compensate for a model that asks the engine to do unnecessary work.

How to Approach Semantic Model Analysis: A Diagnostic Sequence

Model optimization goes wrong when it starts with the report. Someone identifies the slowest visual, rewrites its measure, gains 15%, and moves on, leaving the structural cause untouched. A useful audit runs in the opposite direction, from storage upward.

1. Establish evidence before forming a hypothesis

Open the model in DAX Studio and run VertiPaq Analyzer against it. You are looking for a column-level breakdown of dictionary size, data size, cardinality, and encoding type, sorted by total size descending. In most enterprise models, a small minority of columns account for most of the memory, and the largest are predictably the same categories: high-cardinality text fields, datetime columns carrying second-level precision nobody uses, and free-text descriptions imported because they existed in the source.

In parallel, open the Fabric Capacity Metrics App and identify which operations generate CU consumption, separating interactive from background. The pairing matters. VertiPaq Analyzer tells you what the model costs to hold. The Metrics App tells you what it costs to use.

2. Capture the actual queries, not the reported symptoms

Users report that a report is slow. DAX Studio tells you why. Run the All Queries trace, interact with the report the way a business user does, and read what the engine receives.

The pattern to look for is a query whose duration is dominated by a single storage engine operation over the fact table. When filtering by a descriptive attribute produces a query that scans tens of millions of rows, the problem is not the visual and not the measure. It is that the attribute lives in a place that forces a scan.

3. Audit cardinality column by column

Cardinality, the number of distinct values in a column, is the primary determinant of how well VertiPaq compresses. Low-cardinality columns compress dramatically. High-cardinality columns do not, and when those columns are text, the dictionary required to encode them can dominate the memory footprint of the entire table.

The distinction to internalize: an integer surrogate key is stored using value encoding, where dictionary size is largely irrelevant. A text column of comparable or even lower cardinality is stored using hash encoding, which requires a dictionary mapping every distinct value to a hash. A text key and an integer key with identical business meaning can differ in cost by an order of magnitude.

4. Trace expensive measures back to the columns they touch

Once you know which columns are expensive to store, look at which measures force the engine to read them.

DISTINCTCOUNT deserves specific attention, because it behaves differently from every other aggregation in a way that is not obvious. A sum or a count can be resolved from compressed, partially aggregated data. A distinct count cannot. The engine must materialize the set of unique values, which means reading the full column including its dictionary. Applied to a high-cardinality text column across tens of millions of rows, a single measure on a single visual can force the entire column into memory on every evaluation.

In a Fabric context this is the mechanism that converts a modelling error into a capacity charge. The memory is drawn from the capacity, the compute is billed as CU consumption, and the smoothing window absorbs it until it cannot.

5. Check the report layer, not only the model

Not every avoidable query originates in the model. On report pages carrying several slicers, every individual slicer change fires its own set of DAX queries. A user adjusting four filters generates four rounds of engine work, three of which produce results nobody looked at.

Enabling the Apply slicers button batches those selections into a single submission. It is a report setting rather than a modelling change, it takes minutes, and on heavily filtered pages it removes a meaningful share of total query volume. Low effort items like this one are worth clearing up before undertaking structural work.

6. Validate against capacity, not against the desktop

A model that performs acceptably in Power BI Desktop on a well-specified laptop tells you little about how it will behave at a shared capacity serving concurrent users. Desktop has dedicated memory and one user. Neither has capacity.

Test after publishing and measure the change in CU consumption rather than only wall-clock load time. Load time is what users notice. CU consumption is what the organization pays for, and the two do not always move together.

The Four Foundations of a Cost-Efficient Semantic Model

A diagnostic process tells you what is wrong with an existing model. These four principles are what you build on so that the diagnosis is uneventful next time.

Foundation 1: Dimensional structure, not a single wide table

A star schema is not an aesthetic preference or a convention carried over from the data warehouse era. It is the structure the VertiPaq engine is optimized for. Narrow fact tables holding keys and measures, joined to dimension tables holding descriptive attributes, allow the engine to compress each column against a much smaller set of distinct values.

The performance argument that matters most is not about storage, though. It is about where filtering happens. In a single wide table, every descriptive attribute sits on the fact rows, so filtering by category, channel, or status means evaluating a condition against every row in the table. In a star schema, the same filter resolves against a dimension table of a few hundred or a few thousand rows, and only the resulting keys touch the fact table. The same user action, the same visual result, two entirely different orders of computational cost.

Full normalization is not the goal, and in Power BI it is counterproductive. Partial, deliberate normalization is. The objective is to move repeating descriptive attributes out of the fact table so that what remains is keys and numbers.

There is a second benefit that is easy to overlook. When a fact table is reduced to key columns, its grain can often be redefined as the distinct combination of those keys, which collapses row count substantially before the model ever sees the data.

Foundation 2: Integer surrogate keys, always

Every relationship in the model should be built on an integer key. Business keys that arrive as text (policy numbers, customer identifiers, product codes, tax numbers) should be mapped to integer surrogate keys during data preparation, with the original text preserved as an attribute on the dimension where it can be displayed without being scanned.

This is the least glamorous item on the list and frequently the highest yield. It reduces storage, accelerates relationship traversal, and turns the expensive text-based distinct count described earlier into an operation on a value-encoded integer column.

Foundation 3: Cardinality reduction as a design decision

When a high-cardinality column is unavoidable, reduce it structurally rather than accepting it.

Splitting is the standard technique: a column with millions of distinct values can often be decomposed into two columns with far fewer distinct values each, from which the original can be reconstructed when needed. Datetime columns should be split into date and time components, with time truncated to whatever precision the business reports on. A related move is grouping where analysis happens at the level of a category, the fact table does not need to carry the individual code.

One caveat to plan around: splitting a column removes the ability to run a distinct count directly on it. That is a trade-off to make consciously, and it is one reason cardinality strategy and measure design need to be decided together rather than sequentially.

Foundation 4: Data types and column inclusion as explicit choices

Every column in the model should be there because a report needs it. Every data type should be the narrowest type that holds the required values. Neither happens by default, because most import processes bring everything through at whatever type the source declared.

Practical rules that hold up across engagements:

• Remove columns with no visual, measure, or relationship references. Unused columns cost memory and refresh time permanently.
• Reduce decimal precision to what is reported. Fixed decimal beats floating point where cents are the smallest meaningful unit.
• Strip time from date columns unless intraday analysis is a genuine requirement.
• Prefer measures over calculated columns. Measures are evaluated at query time and do not consume storage; calculated columns are materialized and do.

Common Anti-Patterns and Their Capacity Impact

What This Looks Like in Practice

The principles above stay abstract until they meet a production model.

An insurance client came to us with a customer analytics report that had become effectively unusable. First load approached three minutes, and a single run of the report consumed between 30% and 40% of the available Fabric capacity, creating pressure across the wider reporting estate. The model ran in Direct Lake mode over a Fabric Lakehouse, and the working assumption was that data volume had simply outgrown the platform.

The audit found two broken fundamentals rather than a volume problem. The model was built on one wide, fully denormalized table combining facts and dimensions, and the report’s headline metric was a distinct count evaluated over a text customer identifier. Descriptive attribute filters compounded the problem, resolving as text search operations across the full fact table. Individual DAX queries behind the main visuals were taking over 150 seconds each.

The remediation was partial normalization to a star schema: a narrow fact table holding only keys, with descriptive attributes and the customer identifier moved into dimension tables keyed by integer surrogates. Direct Lake was retained throughout. No connectivity mode changed, no capacity was scaled.

Measured against the original model, first load fell from 187 to 58 seconds and CU consumption for the same operation dropped by roughly 72%. Across the full test session, total consumption fell by about 73%. Interactive filter queries, previously running at around 150 seconds, resolved in tens to hundreds of milliseconds once attribute filtering moved to the dimension tables. The capacity pressure that had prompted a conversation about scaling the SKU no longer occurs.

That proportion is typical. The changes that deliver the largest capacity savings are rarely sophisticated. They are the fundamentals, applied to models built before anyone knew how large they would become.

Key Takeaways

• Fabric changed the economics of model design. Under a shared CU model, an inefficient semantic model imposes cost on every workload on the capacity, not only its own report.
• Peak utilization is not the warning signal. Smoothing masks overconsumption until the runway is exhausted, at which point throttling escalates from delays to outright rejection, and recovery takes hours or days.
• Audit from storage upward, not from the slowest visual downward. VertiPaq Analyzer, DAX Studio, and the Capacity Metrics App together tell you what the model costs to hold, what it asks the engine to do, and what it costs to run.
• Where filtering happens matters as much as how much data you hold. Attribute filters resolved against a large fact table are the single most common cause of slow interactive queries.
• Cardinality and data type are the primary cost levers. Integer surrogate keys instead of text keys is consistently the highest-yield single change available.
• Direct Lake does not fix a badly structured model. Storage mode determines how data reaches the engine. Model structure determines how much work the engine must do.
• Scale the SKU after optimizing, not before. A larger capacity applied to an unoptimized model multiplies the cost of the inefficiency rather than removing it.

Frequently Asked Questions

How do I know whether my Fabric capacity problem is a sizing problem or a modelling problem?
Compare CU consumption against the analytical work being delivered. If a handful of reports account for a disproportionate share of consumption relative to their complexity and user base, the cause is design rather than scale. VertiPaq Analyzer will usually confirm this within an hour by showing memory concentrated in a small number of high-cardinality columns.

Does moving to Direct Lake solve semantic model performance problems?
No. Direct Lake removes the import refresh cycle and can significantly improve time to data, but it does not compensate for poor modelling. It arguably raises the stakes, because Fabric SKU guardrails on rows, files, and memory are evaluated against the model, and exceeding them causes DirectQuery fallback or outright failure depending on the Direct Lake variant in use.

Is a star schema still necessary if the underlying data volume is small?
Small models tolerate structural shortcuts. The difficulty is that models rarely stay small and retrofitting dimensional structure into a model with established reports, measures, and downstream dependencies costs considerably more than building it correctly at the outset.

Can distinct count measures be made efficient, or should they be avoided?
They can be made efficient. The cost comes from the column being counted rather than from the function itself. Applied to an integer surrogate key of moderate cardinality, a distinct count is manageable. Applied to a high-cardinality text column across tens of millions of rows, it is one of the most expensive operations a semantic model can perform.

How Algomine Can Help

Algomine designs, audits, and optimizes enterprise BI environments on Power BI and Microsoft Fabric, as part of the BI and data visualization services delivered.

Our semantic model audit is a hands-on engagement. We work directly in your environment, profile the model at column level, capture the queries your reports generate, correlate what we find against measured capacity consumption, and deliver a prioritized remediation plan that separates changes worth making immediately from those that can wait. Where the root cause sits upstream, our data platform architecture team addresses it at the source rather than compensating for it in the model.

The pattern we see most often is an organization preparing to scale its Fabric SKU because performance has degraded and capacity is under pressure. That decision is sometimes correct. It is worth knowing, before committing to the higher run rate, whether the capacity is genuinely saturated or whether it is absorbing the cost of a modelling decision that takes days to fix.

Talk to our team about a semantic model audit