Optimising Fabric Capacity Usage

Hi all,

Due to the recent changes in Fabric capacity metering, I've been spending quite a bit of time trying to optimise our CU consumption. The challenge I've run into is that it's difficult to distinguish between SQL activity generated by user queries versus SQL activity generated during semantic model refreshes, which makes it harder to accurately evaluate different architectural approaches.

Approach 1: Materialising Views as Delta Tables

Historically, our semantic models have queried SQL views hosted in Fabric. To reduce SQL Endpoint consumption, I began converting the T-SQL view logic into notebooks that materialise the results into Delta tables. The semantic models then connect directly to these Delta tables via the ADLS Gen2 connector in Power BI, effectively bypassing the SQL endpoint during refresh.

However, the results have been inconsistent. In some workspaces this appears to reduce overall CU consumption, while in others it actually increases costs because the notebook execution itself incurs significant compute usage.

Current new architecture(old queried straight from omdb sql views):

My questions are:

• From a CU consumption perspective, is it generally more efficient to:
  • Have semantic models query Fabric SQL views directly, or
  • Materialise those views into Delta tables and have semantic models consume the Delta tables through ADLS Gen2?
• Has anyone compared the total cost of repeatedly querying views during semantic model refreshes versus the cost of creating and maintaining materialised Delta tables?
• While I understand that a star schema is considered best practice for reporting and semantic modelling, if the same business logic can be represented through a set of SQL views, is there still a compelling reason to physically materialise the data into tables from a cost-efficiency perspective?

I've attempted to benchmark both approaches, but isolating the relevant consumption metrics has proven more difficult than expected.

Approach 2: Shifting Transformation Logic to the Semantic Model

Another approach I'm exploring is moving the transformation and view logic out of our Fabric workspace and into semantic models hosted in the client's Power BI Pro tenant.

My assumption is that this would reduce SQL Endpoint consumption within our Fabric capacity because less querying and transformation would occur on our side. However, I would expect semantic model refresh times to increase as more transformation work is pushed downstream.

My questions here are:

• Would moving the transformation logic into the semantic model generally reduce or increase overall SQL-related CU consumption?
• Have others seen meaningful capacity savings using this approach?
• Is there a way to connect one semantic model to another semantic model without using DirectQuery? My concern is that DirectQuery would negatively impact performance and potentially introduce additional query-related costs.

I'd be very interested to hear how others are approaching this, particularly now that SQL Endpoint consumption has become much more visible within Fabric capacity metrics.

I have also reviewed the Query Insights for each workspace in an attempt to correlate SQL activity with overall CU consumption. However, this does not always translate into lower capacity usage. One of the challenges is that many users are querying the SQL Endpoint directly through Excel and other tools, without going through a semantic model at all. Under the new metering model, these ad hoc user queries appear to be a significant contributor to capacity consumption, making it difficult to isolate the impact of semantic model refreshes versus interactive user activity. As a result, determining whether a particular optimisation has genuinely reduced costs becomes far more challenging, as overall CU usage may be heavily influenced by user behaviour outside of the reporting layer.

Thanks in advance!