On-premise gateway refresh failures; scheduled and on-demand

[jderekc]

I am having datasets failing a refresh due to the following error:

Hide	On demand	11/21/2017, 2:27:14 PM	11/21/2017, 2:29:30 PM	Failed	The gateway operation has been cancelled explicitly by the user or from a timeout.
Cluster URI: WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net Activity ID: 42094602-af9f-408f-926c-2f51d2fc1b01 Request ID: 4ee16836-81a5-1b1e-e1cf-a0ae65f8c8f7 Time: 2017-11-21 20:29:30Z

This has been going on for a while now and here's what I've tried:

• Removing large, no longer used datasets that refreshed around the same time.
• Attempted to optimize some of the queries for this dataset.
• Made changes to the report and republished the report.
• Installed the newest enterprise on-premise gateway (November 2017) for our internal SQL Server.
• Restarted the gateway service multiple times.
• Attempted scheduled and on-demand refreshes--both types fail, although not always.
• Changed the times at which the dataset is refreshed.

Does anyone have a solution to this?  Do I need to collect a Fiddler trace when I attempt to do a manual refresh?  The only problem is that the refreshes can take fifteen minutes or more to actually complete for this dataset.  Again, it DOES refresh successfully in the late evening and the early morning (usually), but the middle of the business day it encounters problems.

Thanks!

Best regards,

Derek

[GilbertQ]

Hi @jderekc

Based on your details below if it works at the start or the end of the day, it would appear to me that there are bandwidth issues during the day when everyone is at work and using the internet connection.

Due to there being not bandwidth during the peak hours, it has to send that same volume of data, but with a slower connection this takes longer, which then causes the timeouts to happen.

What I would suggest doing, is on your SQL Server Connection in the Power BI Desktop file, set the timeout to 40 minutes and test. Hopefully that will give it enough time to upload the data before timing out.

[jderekc]

Hi guavaq!

Thanks for the recommendation.  My dataset is set to 300 minutes for timeout (excessive I am sure).  I'll check all of the queries again to see if any don't have a timeout specified.  Let me know if there is anything else I can try in the meantime.

Thanks!

Regards,

Derek

[jderekc]

I set all of my queries to timeout a minimum of 180 minutes after the start of execution.  That should be plenty.  But it is worth noting that the refreshes usually timeout within minutes of starting, so this should never have been an issue to begin with.  I am going to monitor it over the next few days to see if issue persists now that I've made changes.

Thanks!

Regards,

Derek