Report Refresh Fails with On-Premises Data Source w/Gateway

[Shelley]

I have a report that is published to powerbi.com. When I refresh it on my desktop, it refreshes all tables without errors. When I schedule refresh or try to "refresh now" it has been failing sporadically since February 21. It fails more than it succeeds. In fact, it has succeeded only once out of the last 20 times.

Here's the error:

Last refresh failed: Mon Mar 04 2019 13:29:40 GMT-0600 (Central Standard Time)

We reached the data gateway, but the gateway can't access the on-premises data source.

Hide details Cluster URI: WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net

Activity ID: 3188cbc0-cd2e-4e86-bb22-a57a79105228

Request ID: d78bd5a8-7b1f-cf72-0a6c-dcfeef7290bd

Time: 2019-03-04 19:29:40Z

The data files: I have two Excel files connected via Teams/SharePoint and four Excel files on OneDrive, and many tables from a SQL server instance running on an Azure virtual machine. Sometimes it fails in seconds and sometimes minutes.

I cannot figure out what is wrong. We even tested on a Gateway that has more up to date software and it failed there too.

[v-qiuyu-msft]

Hi @Shelley,

Which type of gateway do you use, on-premises data gateway or on-premises data gateway(personal mode)? 

Please go to Dataset Settings check if gateway and data source credential for the dataset is fine. 

Best Regards,
Qiuyun Yu 

[Shelley]

It's an enterprise Gateway and yes, all the credentials are fine, no errors. I'm suspecting it is timing out somehow as we have to do several lookups into a large table. It used to work just fine though, so this is perplexing. 

[Anonymous]

I'm having this exact same problem.

[Shelley]

@Anonymous Microsoft support ticket did not resolve this issue. They never identified a solution. So as far as I know, this is still a problem. I resolved it for myself, by removing all my merge queries that were looking up values for an Excel file sourced from SharePoint into a table from our SQL server. I loaded the tables and rather than using merge queries in the front end query editor, I added columns to each table and used LOOKUPVALUE to populate from the SQL sourced table, within Power BI itself. This definitely seems to impact performance for the end user (slows down report response), but the only way I found to resolve the auto-refresh failures. Incidentally, since this resolution, we continued to have occasional SQL server failures on other reports. We increased the resources on our SQL VM and now we have none of these types of failures. So, you may try boosting your SQL server resources dramatically and see if that works before you resort to using LOOKUPVALUE instead of merge queries.