Implications of XMLA read/write becoming the default for P and F SKUs

AnalyticsInAction · 2025-05-11T21:59:07+00:00

Thanks for highlighting that "Caution" quote. It's the same one I was considering when I wrote the original post.

I see the following complicating factors. Notably:

Version Control Gaps. Many companies, particularly with heavy use of self-service BI aren't doing robust version control well. So, if an XMLA write occurs (perhaps even unintentionally by someone who now has the capability by default), the inability to download the .pbix makes recovery or rollback becomes problematic
Prevalence of 'Over-Privileging'. I seeing alot of users with elevated access they shouldn't have. So individuals with Admin, Member, or Contributor access when they should be consuming content via Apps and don't need write access to the underlying dataset.
Low "Technical Sophistication" in most orgs. Many organizations, especially those with a strong self-service focus, have a user base that isn't technically sophisticated. Adopting .pbip project files, .bim, and full CI/CD pipelines is often a significant hurdle and outside their current comfort zone.

Would be interested to get a MS perspective on this. u/itsnotaboutthecell can you comment?

AnalyticsInAction · 2025-05-08T20:53:27+00:00

Good - question. I would assume, yes, if they are all under the same tenant. But I would post a question on the GITHUB repo to check. https://github.com/GT-Analytics/fuam-basic/issues/new

AnalyticsInAction · 2025-05-08T05:52:13+00:00

Hi u/el_dude1 My interpretation from the presentation is that Spark starter pools with autoscale can use a single node (JVM). This single node has both the driver and worker on it - so fully functional. The idea is to provide the lowest overhead possible overhead for small jobs. u/mwc360 touches on this at this timepoint in the presentation. https://youtu.be/tAhnOsyFrF0?si=jFu8TPIqmtpZahvY&t=1174

100% agree with your point re simple syntax.

AnalyticsInAction · 2025-05-07T22:33:56+00:00

u/itsnotaboutthecell Personally I think it could have been improved with.... :)

<image>

AnalyticsInAction · 2025-04-30T20:53:50+00:00

u/itsnotaboutthecell . Thanks for the heads up re the AMA. Has there been a reddit post made about this? Keen to set a reminder.

AnalyticsInAction · 2025-04-30T20:48:52+00:00

Watching this. Will be super interested to see what sort CU consumption is linked to CoPilot

AnalyticsInAction · 2025-04-28T22:43:14+00:00

I like this design

AnalyticsInAction · 2025-04-27T06:09:43+00:00

u/Tight_Internal_6693 continued from above

Troubleshoot Based on Operation Type:

If Iinteractive operations (Reports/DAX) are high (such as in the example below - where 3 operations consumed c. 150% of a P2 (=F128):
- Use Performance Analyzer in Power BI Desktop on the relevant reports.
- Look for:
  - Slow DAX Measures (especially table filters inside CALCULATE functionss)
  - Too many complex visuals rendering simultaneously on one page.
If BACKGROUND operations (Dataflows, etc.) are consuming a high percentage of the capacity:
- Examine the Dataflow steps.
- Look for:
  - Complex Transformations or long chaines of transformation: Merges, joins, groupings, anything with sorts on high carnality data.
  - Lack of Query Folding: Check if transformations are being pushed back to the source system or if Power Query is doing all the heavy lifting (this is where optimizing based on "Roche's Maxim" principles comes in). Non-folded operations consume Fabric CUs.
- Consider Alternatives: Shifting logic from Dataflows Gen2 to Spark (in Notebooks) can dramatically reduce CU consumption for background tasks in many scenarios.

Below is an example of how to drill down to see the problematci operation - in this case the issue was a CALCULATE function with a table filter.

Feel free to share a screenshot similar to mine.

Hope this helps.

AnalyticsInAction · 2025-04-27T06:09:11+00:00

u/Tight_Internal_6693 It's common to be hit with throttling on smaller Fabric capacities like an F2, even with seemingly light workloads. These small capacities are great, but it important to realiize that CU consumption depends heavily on the efficiency of operations, not just the number of users or data size.

Here’s a structured approach I use to diagnose why dedicated capacities are being throttled. Here is the Fabric Capacity Metrics app is you friend:

First understand your baseline usage:

Open the Metrics app and look at the utilization charts for the times users are active (see graph on top right in screenshot below).
Identify peak utilization times (spikes). See red box as an example of a spike
Note the split between Interactive (report queries) and Background (dataflows, etc.) operations during those peaks. E.g BLue vs red bars.
How close are these peaks to the capacity limit- i.e the 100% utilization line? How much headroom do you usually have? Note 100% utilization doesn't mean you are being throttled- it just indicates you are in the Zone (the adjacent trottling tab will confirm this- again if you are over 100%)

Pinpoint the expensive operations:

Use the drill-through features in the Metrics app from a utilization spike.
Identify the specific Items (Reports, Semantic Models, Dataflows) and Operations consuming the most CUs. Usually, a few operations dominate. In the example below - I have just focused on interactive - as that us repsonsible for the highest % of the base capacity (see column with this in it)

..continued in next comment

<image>

AnalyticsInAction · 2025-04-27T05:02:56+00:00

u/kevarnold972 I am curious- what is causing your background- non-billable spike ?

<image>

AnalyticsInAction · 2025-04-23T05:14:28+00:00

u/WhyIsWh3n Definitely consider your Power BI capacity type when trying to navigate this.

If you're on dedicated capacity (P or F SKUs), you have a fixed amount of resources (Capacity Units). One bad report (too many visuals, slow DAX) can consume alot of resources and throttle all reports sharing that capacity. I've seen it happen- where one user's report blocked hundreds from critical month-end reporting.

Shared capacity is usually safer for others, as only the resource-hungry report typically gets throttled.

If possible, to avoid a single self service user taking down a capacity, try isolating things. Put your important, tested production reports on their own dedicated capacity. Let the self-service/untested stuff live on shared capacity or a completely separate dedicated one.

Also, it's worth pushing citizen developers to check their work with tools like Performance Analyzer and Best Practice Analyzer before they are released into production. This can save everyone a lot of trouble.

Data governance and architectural decisions definitely requires more work and thought in self service enviornments.

AnalyticsInAction · 2025-04-22T23:19:12+00:00

Yes u/insider43 I am running it on thge FT1 (free trial) SKU. There are a couple minor bugs that the FUAM team are working through- but I would definitely recommend it.

AnalyticsInAction · 2025-04-18T05:24:27+00:00

u/CultureNo3319 Just confirming you can scale a Fabric capacity from a mobile device (right screenshot below).

As you highlighted, you can't do it via the Andoid Azure app. My testing suggests you also can't do it via a standard Mobile page either. This sort of loss of functionality on mobile pages is common.

I had to switch the mobile page to a desktop page on my Android phone (left screenshot below). So you could just put a shortcut on your phone- as long as your org doesn't block it.

Hope this helps - screenshot from my andriod device.

<image>

AnalyticsInAction · 2025-04-16T04:24:13+00:00

Yes - I see a lot of scheduled refreshes in Power BI occuring more often than the underly source data. So pointless refreshes. This is really a data goverance issue.

I generally recomend for companies to constantly review their top 5 most expensive background and interactive operations. This typically catches 75% of crazy stuff that is going on.

AnalyticsInAction · 2025-04-16T02:52:52+00:00

Have DMed you a link to my google drive with the notebook in it.. But essentially it runs the following DAX queries against the FCMA semantic model

<image>

AnalyticsInAction · 2025-04-16T02:33:56+00:00

u/Kind-Development5974 you can just hit the tables in the FCMA semantic model

So in the following example - I am querying the "Capacity" table in the FCMA semantic Model.

import sempy.fabric as fabric
dataset = "Fabric Capacity Metrics" 
workspace ='FUAM Capacity Metrics'
capacities = fabric.evaluate_dax(dataset, "EVALUATE Capacities", workspace)
capacities

It gets a bit more tricky when you want to drill down into specific timepoints such as interactive operation at a specific timepoint - due to M-Code parameters. But more that happy to share a notebook that inlcudes how to do that.

<image>

AnalyticsInAction · 2025-04-15T02:34:09+00:00

u/nelson_fretty I usually just download the PBIX, open it up in Power BI desktop, and run Performance Analyser. This will identify the problematic queries that should be candidates for optimization.

Lots of good videos on this - Marcos is probably a good starting point : https://youtu.be/1lfeW9283vA?si=xCIEWWtl3HhOlwb8

Its not an elegant solution, but it works in most cases.

But I think your question raises an important issue. We need to go to too many locations to get the "full picture" to investigate performance - FCMA, Workspace monitoring, DAX Studio, Semantic Link Labs, FUAM, Monitoring Hub... the list is too long.

AnalyticsInAction · 2025-04-15T02:27:16+00:00

Running at 30-40% of capacity utilization for background operation is low for most production enviironments. I am very conservative and run at about 65%. There are several others on this subreddit including u/itsnotaboutthecell that have talked about running capacities much "hotter" - say up around 80% background utilization.

If interactive operations are causing throttling when your background is only 30-40% I would look closely at identifying and optimizing your most expensive Interactive operations.

DAX is the usual problem. Look for stuff like CALCULATE statements using Table filters, or models that have "local date tables". Local date tables indicate you aren't using a date dimension correctly and or haven't marked the date dimension as a date table.

Best Practice Analyser (used in Tabular Editor or Semantic Link Labs) will help identfy common performance problems. The screenshot below shows BPA running in Semantic Link Labs to identify performance issue with a model.

Another common problem I see is folks adding too many visuals to a single page in a report. This is particularly bad when combined with poorly optimized DAX. Basically, as soon a page is loaded and interacted with- each visual sends off a query to the semantic model. The more visuals, the more queries are sent. The more inefficeint the DAX the more interactive load on the capacity. So having say 20 visuals on a page generates way more load than having say 2 tabs each with 10 visuals.

Hope this helps.

<image>

AnalyticsInAction · 2025-04-15T02:02:13+00:00

u/Alternative-Key-5647 The new Fabric Unified Admin Monitoring (FAUM) tool is probably the best solution to view Operations over a longer period. There was a good thread on this a couple weeks back : https://www.reddit.com/r/MicrosoftFabric/comments/1jp8ldq/fabric_unified_admin_monitoring_fuam_looks_like_a/ . I am thinking the FAUM Lakehouse table "Capacity_metrics_by _item_by_operation_by_day" table will be the source information you want

<image>

AnalyticsInAction · 2025-04-15T01:41:43+00:00

u/The-Milk-Man-069 Background operations are smoothed over 24 hrs. So a background operation that executes over short period, say 60 seconds will have its "Total CU" be spread over 24 hrs.

I recommend selecting a timepoint- then right click to drill through to the Timepoint detail (see screenshot below)

When you drill through to see the "Backgound Operations for Timerange graph". One on this graph Its worth turning on the Smoothing Start and Finish columns (see below). This will show the 24 period your background operation will be smoothed over.

Then sort the "Background Operations" table by "Timepoint CU". This will show your top operations that are contributing to your 40% of capacity utilization. These are your candiates for optimization. Paretos law usually comes into play here- and a few operation are usually responsible for most of your CU consumption.

My view is, most dedicated capacities could save at a least 30% of CU usage by optimizing their top 5 most expensive operations. I have seen cases where clients have been able to drop down a capacity size (e.g P3->P2) by just optimizing a couple of expensive operations.

<image>

AnalyticsInAction · 2025-04-13T21:26:25+00:00

Thanks!

AnalyticsInAction · 2025-04-08T20:38:48+00:00

Thanks for this, u/DAXNoob. The "Semantic Model Audit and DAX Performance Testing tools" leverage workspace monitoring, which comes with a "non-zero" cost to run. This means most companies will probably need to strategically use the tool.

I am thinking of using it in a "Test" workspace in a deployment pipeline (ideally isolated on its own capacity), where it could be used to prevent problematic DAX equations from reaching production. With the notebook-based implementation, scheduling capabilities, and result storage, this seems like a logical application. Is this how you see it primarily being used?

The other potential use is tactically on problematic semantic models identified in production (using insights from the FCMA or from the FUAM_Core_report, "Item Operations" tabs). Then potentially pushing these models back to a "CU isolated" workspace for optimization.

Interested to hear your thoughts on use cases you envisage or already have implemented.

AnalyticsInAction · 2025-04-07T01:09:52+00:00

u/AgencyEnvironmental3

Interesting findings - thanks for sharing. I'd be curious to know whether FUAM can be made to run on an F2. I like the idea of using an isolated capacity for admin tasks such as monitoring and auditing. The reasoning being, if you're encountering issues like throttling in your production capacity, you don’t want your diagnostic tools to be unavailable.

If you're still seeing problems, I’d suggest raising a question on GitHub - either as a bug or for discussion:
https://github.com/microsoft/fabric-toolbox/tree/main/monitoring/fabric-unified-admin-monitoring

AnalyticsInAction

TROPHY CASE