Slow refresh when using Analyze in Excel

[pontushaglund]

Hi all. I'm using Analyze in Excel for my dataset to build some reports that are hard to format as we'd like them in Power BI (values on rows rather than columns). When using a slicer in Excel or clicking the Refresh-button the query is running for at least 30 seconds before bringing back the updated values. This in a pivot with about 100 data points so nothing huge. 

Is this expected behaviour? Are there ways to optimize the model (or something else) to increase performance?

Thanks in advance,

Pontus Haglund

[GilbertQ · Accepted solution]

Hi there

My first question would be is how is the connectivity to the Power BI Service, I have found in the past that it can depend on the connectivity?

Also which way are you using the Analyze in Excel?

Are you downloading the ODC file?

Or are you using the plug in for Power BI Publisher for Excel? https://powerbi.microsoft.com/en-us/excel-dashboard-publisher/

Personally I prefer the Power BI Publisher for Excel, because it allows you to connect to the different sources quite easily. And this might possibly also be a bit quicker.

If it is still slow after that then it might be the way you have modeled your data?

[MWinter225]

@GilbertQ Can you treat the data connection in the Power BI Publisher for Excel from a dataset in a workspace as a data source in Power BI and have it refresh? it looks like the only drawback is that you can't have it refresh in the background, you have to have the file open. I'm trying to gather usage metrics from multiple workspaces and combine them into one report. Currently, when you connect to a PBI service dataset in Power BI you are limited to selecting only one dataset. I think this might be one way to get around that.

[GilbertQ]

It appears that you currently have to have the Excel File Open to refresh the data in the connection.

Another possible workaround is within Power BI Desktop to connect directly to the dataset for the app workspace. I am certain that I did read a blog post about how to get the data from multiple workspaces.