Excel file running Power BI dataset slow performance

[KasperJ90]

Hi,

I have build an Excel file for controlling purposes based on my organizations Power BI dataset. People are using slicers I included in the file as standard but also redefining the Pivot tables in the Excel file. The issue is that it is running quite slow and "Running OLAP query" each time a new selection is made in the file. 

How can I increase the performance of this Excel file? 

[Tsanka]

Hi @KasperJ90 ,

I have exactly the same case. The MDX queries from Excel to the Azure Analysis Service (live connection) are slow and sometimes raise errors. The connected Excel tables are based on DAX and have a good performance, however they lack flexibility. Have you managed to find a solution?

Thanks

Tsanka 

[NeedhelpBI]

Hi @Tsanka 
I connect via the new feature "insert table" in Excel. I can not get all rows out I want, so I am still limited.
Maybe you can use it. See it here: https://www.youtube.com/watch?v=kIWRKdapx08&t=283s

[v-yanjiang-msft]

Hi @KasperJ90 ,

There are several factors that can affect the speed and efficiency of your Excel file, such as the size and complexity of the Power BI dataset, the network latency, the gateway configuration, the data model optimization, and the Excel settings. Here are some possible ways to increase the performance of your Excel file:

• Only load the columns and measures that you absolutely need in your Excel PivotTables or tables. Avoid adding unnecessary fields or calculations that can increase the query time and memory usage.
• Make sure you have your columns typed correctly in the Power BI dataset. For example, use Date instead of DateTime if you don’t need the time component, or use Whole Number instead of Decimal Number if you don’t need fractions.
• Configure the capacity settings for your Power BI dataset, such as the memory limit, the query timeout, and the refresh frequency. You can also monitor the capacity metrics to identify any performance issues or bottlenecks.
• Size your data gateway according to the number of users and queries that you expect to handle. You can also use multiple gateways to balance the load and improve availability.
• Reduce the network latency between your Excel client and the Power BI service. You can use a wired connection instead of a wireless one, or use a VPN if you are working remotely.
• Analyze your performance by using tools such as Performance Analyzer, DAX Studio, or SQL Server Profiler. You can identify and optimize any slow or inefficient queries or calculations in your Power BI dataset.
• Speed up your data model by using best practices such as using star schema, creating hierarchies, using aggregations, and avoiding bidirectional relationships.
• Adjust your Excel settings to improve the user experience, such as disabling animations, turning off background refresh, or using manual calculation mode.

For more information, please refer to these web pages:

Power BI & Large Dataset: How to improve performan... - Microsoft Fabric Community

Design refreshable reports in Excel with Power BI data - Power BI | Microsoft Learn

Connect Excel to Power BI datasets - Power BI | Microsoft Learn

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KasperJ90]

Thanks you for answer.
I have a question for your first point "Only load the columns and measures that you absolutely need in your Excel PivotTables or tables". Currently I am loading the entire data model and save the Excel file in our organizations share folder. I actually only need data from my sales table, but how can I extract only this tables including the measures?