Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kevingauv
Advocate II
Advocate II

Analyze in Excel causing Premium Gen2 to Auto Scale-Up?

Hi!

 

We are using Premium Gen2 P1 with Auto Scale-Up enabled since last November.

Since then, Scale-Up process occurred every week. 1 to 5 times a week, sometime up to 4 more CPU.

 

We are trying to analyze what is causing the CPU Scale Up.

To help us, we are using the Gen2 Capacity Metric app, we are collecting and analyzing the PBI Audit and Log Event activities and recently, we enabled the Azure Log Analytics for some workspace.

 

With all those loggings tools, we found that the Analyze in Excel activities are generally the ones that seems causing the Scale Up. Direct Query also seems to trigger the Scale Up, but in a less proportion.

It's occurred for some huge Datasets (in place prior of our migration from Gen1 to Gen2).

 

Before opening an official service request, we want to know if some of you did notice the same behavior? 

If so, did you find any cause/solution/workaround?

 

Thx in advance!

 

 

8 REPLIES 8
v-kkf-msft
Community Support
Community Support

Hi @kevingauv ,

 

Did you solve your problem? Did you submit a support ticket and have any feedback?

 

Best Regards,
Winniz

v-kkf-msft
Community Support
Community Support

Hi @kevingauv ,

 

The Query Memory Limit of the Power BI capacities will limit all DAX and MDX queries that are executed by Power BI reports, Analyze in Excel reports, as well as other tools which might connect over the XMLA endpoint.

 

The queries issued by tools that use the Analysis Services protocol (also known as XMLA) do not have the same hard ceiling of 10GB as report queries. Users should consider simplifying the query or its calculations if the query is too memory intensive. 

 

Or you might consider limiting the use of Analyze in Excel to only certain users. The following table describes the implications of the setting Analyze in Excel (AIXL):

 

vkkfmsft_0-1641798504066.png

 

Referencing: Configure workloads in a Premium capacity 

Dataset connectivity with the XMLA endpoint 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi, thx for your response.

 

However, our problem with the Premium Gen2 capacity scale up is with the CPU consumption and not the memory limit (because de scale up occurs when the CPU reach the capacity limit, not the memory).

Analyze in Excel is already available only for a limited set of users.


We did some test, and I was able to trigger a Premium Gen2 capacity scale up on my own using “Analyze In Excel” feature:

I did a test with only two Excel connected to the same Power BI Dataset. Each Excel files contained 3 tabs that use the same connection.

So, in this test, my two Excel files triggered a grand total of 6 connections in approximately one minute... it was enough to reach our Premium Gen2 CPU capacity limit.... and the Scale up happened...

Hi @kevingauv ,

 

Please use "Analyze in Excel", then open Task Manager > Performance, check how much CPU and memory are consuming. If the capacity limit is often exceeded, then I think expanding the capacity size is a appropriate option.

 

If your problem is still not solved, I would like you to create a support ticket.

How to create a support ticket in Power BI 

 

Best Regards,
Winniz

Hi, problem still occurs.

 

We will open a ticket shortly

Hi, sorry to revive the old post. How did you resolve it? We are experiencing lots of XMLA Read Operations peaking up our capacitites and I wonder what's guidelines around it. Thank you

Hi, on our side, we never found a real solution, unfortunatly.

 

We found that an AnalyseInExcel based on a Dataset that use a Direct QUery on another Dataset increase the problem a lot.

We try to limit the use of AnalyseInExcel feature for the user by coaching them.
Build Permission are given with precaution.
We discourage the use of DirectQuery between Dataset.

Sorry to not have another good solution.

Hi, thank you, that's was helpful and basically on par with our approach at the moment. Analyze In Excel is one terrible functionality, probably there only for those dinosaurs as well as to increase the cost (=profit for MS).

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors