Query Diagnostics on the Power BI Service

[Anonymous]

I have a dataset that takes about 2 minutes to do a full refresh in Power BI Desktop but takes on average more than 20 minutes on the Power BI Service.

I've tried to run a full refresh with Query Diagnostics turned on in Power BI Desktop, but it doesn't provide any clear answer to what is causing the slow refresh (again it only takes about 2 minutes to complete). I've also looked at the refresh tab in the Power BI Capacity Metrics app, but those numbers don't provide details on table refresh time or similar.

1. Is there any way to do a full refresh on the Power BI Service with Query Diagnostics turned on?
2. Or another way to figure out what is causing the refresh to be slower by a magnitude of 10?

The dataset pulls in data from multiple databases, Dataflows, and even SharePoint but my main suspect is the gateway, which is hard to confirm or deny without more details from the refresh on the Power BI Service.

[v-zhenbw-msft]

Hi @Anonymous ,

Please upgrade the gateway to the latest version, ensure that the gateway and Power BI Desktop are both at the latest version.

Please check your entire process according to this article to see if there is any room for optimization.

https://medium.com/datadriveninvestor/my-dataset-is-taking-too-long-to-refresh-843fd8fd4a51

Best regards,

Community Support Team _ zhenbw

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

[Anonymous]

It's worth looking into if updating the gateway can alleviate the problem.

So to sum up: there is no query diagnostics equivalent for refreshes on the Power BI Service?

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

Sorry for that Power BI Service doesn't have query diagnostics equivalent.

perhaps you can submit the requirement to ideas and add your comments there to make this feature coming sooner : https://ideas.powerbi.com/forums/265200-power-bi-ideas

Best regards,

Community Support Team _ zhenbw

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

[Anonymous]

I found the root cause to be the direct query cache that slowed down the refresh on the Power BI Service.

Without being sure, I think that the cache gets filled each time the refresh runs on the Power BI Service causing a huge slow-down and also a lot of RAM being consumed during the refresh.

So be careful with using the direct query cache!