Fabric Capacity Optimisation: Reduce Cost, Improve Performance and Scale Smarter

Microsoft Fabric brings data engineering, integration, warehousing, real-time analytics, data science and Power BI onto one platform. After the migration, most teams make the same mistake: they pick a capacity SKU and assume capacity optimisation is done.

It is not. A larger Fabric capacity hides architectural problems. A smaller one exposes them as slow reports and failed refreshes when workloads are poorly scheduled or inefficiently built. Capacity optimisation is an ongoing combination of architecture, workload management, performance engineering, monitoring and cost governance.

For an estate running Power BI, Lakehouses, Warehouses, Data Factory, Spark and Dataflows Gen2, the objective is simple to state and hard to earn: get the maximum business value from every Capacity Unit.

A Fabric capacity is a pool of compute measured in Capacity Units (CUs). The Fabric Capacity Metrics app lets administrators watch consumption and judge whether a capacity is sized right or needs scaling.

Optimisation is the continuous work of improving capacity utilisation, workload performance, CU consumption, Power BI performance, pipeline efficiency, Spark and Warehouse workloads, semantic-model performance, refresh schedules, concurrency, sizing and cost.

The aim is not the lowest possible utilisation. It is the right balance between performance, reliability and cost — reducing cost while making Power BI unusably slow is not optimisation.

Picture an estate with 100+ semantic models, 300+ reports, several Lakehouses, Data Factory pipelines, Dataflows Gen2, Spark notebooks, Warehouses and Direct Lake models. It all works — until the complaints start: the dashboard is slow, the refresh failed, the report takes 30 seconds to open, the pipeline is late, we are being throttled.

The first reaction is usually "increase the capacity". Sometimes that is right. More often the real problem is poor workload distribution, inefficient data models, needless refreshes and badly designed transformations — and a bigger SKU only postpones it.

The Capacity Metrics app exists precisely to identify the workloads and time periods driving CU consumption before you decide to scale. That turns the question from "why is Fabric expensive?" into "which workloads are consuming the most CUs?"

Fabric capacity is measured in Capacity Units. The F SKUs map to CUs on a simple scale.

Microsoft recommends sizing from the actual workload — using the Capacity Metrics app and, where relevant, the Fabric SKU Estimator — rather than picking a capacity from theoretical sizing or a user count. Capacity sizing should be driven by workload behaviour, not by the number of people with a login.

The concept that catches teams out: multiple Fabric workloads draw on the same capacity. Power BI semantic models, report queries and refreshes; Data Factory pipelines and dataflows; Data Engineering notebooks and Spark jobs; Warehouse SQL and loading; Real-Time Intelligence eventstreams — all share the pool.

So optimising Power BI alone may not fix a capacity problem. A report can be slow because a large Spark job or a background refresh is consuming capacity at the same moment.

Microsoft classifies operations broadly as interactive or background. Interactive covers user-driven report queries; background covers activities such as semantic-model and dataflow refreshes. When users complain about slow reports but the pressure comes from a background Spark workload, more capacity is the wrong first move.

The first question is "do we have the right-sized capacity?" — not "should we buy a larger one?" There is a real difference.

The common sizing mistakes are familiar: over-provisioning from day one, under-provisioning on optimistic estimates, sizing only for average usage, ignoring peaks, ignoring background processing, ignoring growth, and using user count as the primary factor.

A better sequence is measure, analyse, optimise, size, validate — using observed utilisation from the Capacity Metrics app, not a theoretical estimate. Create or use a trial capacity, review utilisation, identify the timepoints that matter, and scale on demonstrated demand.

This is where the Capacity Metrics app earns its keep. Microsoft's troubleshooting guidance is to identify the highest CU-consuming items over the previous 14 days, drill into specific time periods, then analyse the trend to decide the action.

Look for the top-consuming workspaces, items and operations; peak periods; long-running operations; repeated refreshes; heavy notebooks; and expensive SQL queries. That single shift — from guesswork to evidence — is what turns capacity management into engineering.

The Compute page gives a 14-day view that surfaces usage patterns, peak loads and overloaded periods, with usage data generally available after around 10–15 minutes of processing latency.

Power BI is often the most visible workload, and a poorly designed semantic model quietly burns capacity. Review model size, column count, high-cardinality columns, calculated columns, DAX complexity, relationships, many-to-many and bi-directional filtering, unnecessary tables, duplicate dimensions, aggregations and storage mode.

The question to keep asking: does the model contain everything the report needs, or everything the source system contains? Importing entire ERP tables when a report needs a handful of columns and rows is the classic anti-pattern.

Refresh frequency matters just as much. A model refreshed every 30 minutes when the data changes once a day is waste. Match refresh to the business latency requirement — use incremental refresh, sensible windows and controlled concurrency so only changed partitions are processed. Refresh when the business needs fresh data, not because a schedule exists.

Data integration is another big source of avoidable consumption. Review pipeline frequency, full versus incremental loads, copy-activity design, transformation logic, redundant pipelines, duplicate source extraction, retry policies, parallelism and dependency chains. The single biggest win is usually replacing a full load with an incremental one where the source supports it — processing 25,000 changed records a night instead of re-reading a billion.

Spark workloads can become major consumers. Review notebook execution time, cluster configuration, partitioning, file sizes, join strategies, data skew, repeated transformations, caching, shuffle operations and the small-file problem. The anti-pattern is read-everything, transform-everything, write-everything when only a fraction of records changed; the better design is incremental ingestion, incremental transformation and optimised Delta tables.

Fabric's autoscale monitoring for Spark also lets you see which Spark workloads triggered autoscaling and how they contributed to capacity usage — useful evidence when a background job is the real cost driver.

A capacity can run comfortably all day and then buckle in one window when too many workloads overlap — refreshes, ERP ingestion, Spark transformation and executive reports all firing at once. Scheduling is therefore part of optimisation: stagger ingestion, transformation and refresh through the small hours so BI is ready for the working day, and flatten the unnecessary peaks.

Fabric also smooths compute — spreading evaluation over time rather than treating every spike as an immediate capacity requirement. So a short utilisation spike does not automatically mean you need a larger capacity; you need to know how long it lasts, what caused it, whether it throttles, whether it hits interactive users, and whether it recurs.

When workloads do reach the limit, Microsoft's metrics identify distinct throttling scenarios across different evaluation windows: interactive delay (reports respond slowly), interactive rejection (requests error out), and background rejection (refreshes are rejected). The distinction matters — if reports are slow but a background Spark job is the consumer, the fix is to optimise and reschedule that job, not to resize.

One architectural lever is whether everything should share a capacity. Splitting enterprise BI, data engineering and advanced-analytics workloads onto separate capacities is not automatically cheaper, but it can improve performance isolation, governance, cost allocation, troubleshooting and business prioritisation. The right design depends on workload characteristics and organisational need.

Optimisation should not live only with the Fabric administrator — it belongs in the data-platform operating model. Define ownership (who owns each workload), development standards, the metrics you review, the thresholds that trigger investigation, the escalation point for resizing, cost allocation by business unit, and a lifecycle for retiring unused workloads.

That last point is easy to miss: unused semantic models, reports, pipelines, notebooks and dataflows still add complexity and can generate needless background activity. A regular Fabric workload cleanup belongs in governance.

The work is repeatable. We run it as six steps, each feeding the next.

• Discover — inventory capacities, workspaces, workloads, semantic models, pipelines, notebooks and Warehouses
• Measure — analyse CU consumption, utilisation, peak periods, throttling and top consumers
• Diagnose — find inefficient workloads, duplicate processing, excessive refresh, poor data models and capacity contention
• Optimise — improve Power BI models and DAX, pipelines, Spark, SQL, refresh schedules and data architecture
• Govern — set standards, ownership, monitoring, thresholds, cost allocation and lifecycle
• Scale — only after optimising: resize, autoscale where appropriate, then re-measure

Keep a simple before-and-after tracker so every change proves its worth.

The figures vary by workload; the discipline is what matters — measure before versus after, every time.

Scaling is the right move when workloads are already optimised, demand is genuinely growing, capacity pressure is persistent, interactive performance requirements are rising, concurrency is consistently high, and the workloads cannot reasonably be moved or rescheduled. The principle: optimise first, scale second — not always, but often.

The F SKUs support on-demand resizing and pause/resume, with specific autoscale capabilities varying by workload and SKU. For predictable peaks, proactive scaling helps — run F64 on normal days, step up to F128 for month-end processing, then back to F64. Microsoft documents scheduled resizing for predictable surges using automation such as the Fabric CLI, Azure Automation or the Fabric REST APIs.

The point is to model the economics rather than run the largest capacity all the time to paper over a workload you have not looked at.

For manufacturers, capacity optimisation matters more because the workloads have very different shapes through the day. ERP incremental ingestion overnight, MES processing, IoT transformation and production aggregation in the small hours, a semantic-model refresh before dawn, plant dashboards live from 07:00, interactive Power BI through the shift, daily production processing in the evening — and month-end when finance, inventory and production workloads all climb together.

The capacity has to carry both background engineering and interactive business workloads without them fighting each other. A well-designed schedule — heavy engineering off-peak, BI ready for the shift — reduces the contention that makes a capacity look undersized when it is really just badly timed.

Capacity optimisation is not only about cost. There are three objectives, and you need all of them: cost (are we spending the right amount?), performance (are users getting the response times they need?) and capacity (are we using the compute efficiently?). Buying a huge capacity to erase every performance issue is no more "optimised" than starving it to cut the bill.

A mature team does not just read "utilisation = 72%". It knows which workload consumed it, why, when, whether it was necessary, whether users were affected, whether it can be optimised, and whether it should be rescheduled or resized. The conversation shifts from "we need a bigger capacity" to "our Spark job is generating 40% of background CU in the same window as the executive model refresh — let's optimise and reschedule before resizing". That is capacity engineering.

Fabric gives you a powerful unified platform. It does not optimise itself. Before you assume you need more, ask what is consuming the capacity, why, whether the workload can be optimised, whether it can run at a different time, whether the architecture can be redesigned — and only then, whether you need more.