Architecture Best Practice

Hi FabricEnjoyer​ ,

I would approach this as a workload/serving-layer issue.

With 32 workspaces, the SQL analytics endpoints are currently serving several different workloads at the same time: Excel refreshes, ad-hoc SQL, semantic-model refreshes, and reporting. Under the current metering model, that can result in significant SQL compute consumption.

I would consider the following approach:

1. Measure the SQL workload first

Before changing the architecture, use the Fabric Capacity Metrics app and SQL monitoring to identify which workspaces and operations are responsible for most of the CU consumption.

I would specifically separate:

• Excel/user-generated SQL
• Ad-hoc SQL queries
• Semantic-model activity
• Scheduled workloads
• Long-running or frequently repeated queries

With 32 workspaces, I would optimize the highest consumers first rather than redesigning everything at once.

2. Don't make the SQL endpoint the default path for every Power BI workload

For Power BI semantic models, evaluate Direct Lake where the model and requirements support it.

Instead of always using:

Evaluate:

This is particularly worth testing if the current architecture is generating significant SQL activity just to serve Power BI.

3. Review semantic-model refresh frequency

If some models are refreshing eight times per day, I would validate whether that frequency is actually required.

Mirroring may already be keeping the underlying data current, so the refresh strategy should match the semantic-model storage mode and actual business latency requirement.

Reducing unnecessary refreshes is one of the first optimizations I would test.

4. Treat Excel and ad-hoc SQL as separate workloads

Excel refreshes and unrestricted ad-hoc SQL can create unpredictable demand.

Where possible, I would provide users with governed semantic models or curated SQL objects rather than unrestricted access to the entire mirrored database.

For users who genuinely require SQL access, consider separating or governing that workload so it doesn't compete with business-critical reporting.

5. SQL Pools can help with workload isolation

Custom SQL Pools are worth evaluating, particularly for separating workloads such as:

The important distinction is that SQL Pools should be viewed primarily as a workload-management/resource-governance mechanism, not as something that automatically makes queries consume less CU.

They can help prevent lower-priority or unpredictable workloads from consuming resources needed by critical reporting.

6. Consider workload isolation across workspaces

Fabric documentation also describes the workspace as a natural SQL compute isolation boundary.

For particularly heavy read workloads, it may therefore be worth evaluating whether all consumers should query the same SQL engine, or whether OneLake/shortcut-based patterns and separate serving workspaces would provide better workload isolation.

I wouldn't introduce this complexity for every workspace without first proving that it solves a measured bottleneck.

Suggested direction

Conceptually, I would move toward:

I would introduce a separate curated Lakehouse/Warehouse serving layer only where there is a genuine requirement for additional transformation, modeling, security, or SQL serving. Adding another layer by itself doesn't guarantee lower CU consumption.

So my priority would be:

Measure → reduce unnecessary refreshes → evaluate Direct Lake → govern Excel/ad-hoc SQL → test SQL Pools for workload isolation → consider additional serving/workspace isolation only where required.

That avoids a large redesign while directly targeting the workloads most likely contributing to the increased SQL Endpoint consumption.

If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.

If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!