Forum Discussion

kevingauv's avatar
kevingauv
Advocate III
4 years ago

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 tryi...
Need Help
v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

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):

 

 

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.

 

  • kevingauv's avatar
    kevingauv
    Advocate III
    4 years ago

    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...

    • v-kkf-msft's avatar
      v-kkf-msft
      Community Support
      4 years ago

      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

      • kevingauv's avatar
        kevingauv
        Advocate III
        4 years ago

        Hi, problem still occurs.

         

        We will open a ticket shortly