PowerBI Service: Data Set Refresh Failure

[lee_a]

Hi, 

I have built a report that I've published to the Service which will fail data set refreshes approx 95% of the time. 

This is the error that I got earlier today before I gave up. 

Last refresh failed: Wed Jun 06 2018 12:10:40 GMT-0500 (Central Daylight Time) 
There was an error when processing the data in the dataset.

Data source error:	Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.. The exception was raised by the IDataReader interface. Table: [BM] POS.
Cluster URI:	WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID:	5feafc06-a755-83a4-7f7e-a8c26ccd4d51
Request ID:	585bf707-c444-f020-19fa-3262bae6987a
Time:	2018-06-06 17:10:40Z

My entire data set is approx 4.0gb. It's sitting in the sharepoint site of the workspace in which I created specifically for my PowerBI reports. The idea being that my data set refreshes would be handled by PowerBI Service instead of my work laptop as a full refresh would take upwards of 2-3hrs and all my system resources. 

The data refresh will occassionally work however will fail 95% of the time and I can't understand why. 

Could I get some help on this issue please. 

Thanks!

-AL

[v-shex-msft]

Hi @lee_a,

Can you please share more detailed information to help us clarify you scenario?

You can refer to below document to share more contents:

Power bi gateway troubleshooting

Spoiler

Collecting logs from the gateway configurator

There are several logs you can collect for the gateway, and you should always start with the logs. The simplest way to collect logs after installing the gateway is through the user interface. In the On-premises data gateway user interface, select Diagnostics and then select the Export logs link near the bottom of the page, as shown in the following image.

Installer logs

Copy

%localappdata%\Temp\On-premises_data_gateway_*.log

Configuration logs

Copy

%localappdata%\Microsoft\On-premises Data Gateway\GatewayConfigurator*.log

On-premises data gateway service logs

Copy

C:\Users\PBIEgwService\AppData\Local\Microsoft\On-premises Data Gateway\Gateway*.log

Event Logs

The On-premises data gateway service event logs are present under Application and Services Logs.

Fiddler Trace

Fiddler is a free tool from Telerik that monitors HTTP traffic. You can see the back and forth with the Power BI service from the client machine. This may show errors and other related information.

Refresh History

When using the gateway for scheduled refresh, Refresh History can help you see what errors have occurred, as well as provide useful data if you should need to create a support request. You can view both scheduled, as well as on demand, refreshes. Here is how you can get to the Refresh History.

1. In the Power BI navigation pane, in Datasets, select a dataset > Open Menu > Schedule Refresh.

   

2. In Settings for... > Schedule Refresh, select Refresh History.

   

   

For additional information about troubleshooting refresh scenarios, take a look at the Troubleshooting Refresh Scenarios article.

In addition, which type of datasource you used? Have you consider to summary records to reduce data amount?

BTW, scheduled refresh has its timeout limit, I think your issue may due to huge data amount and unfinished refresh operation stream.

Scheduled refresh timeout

Scheduled refresh for imported datasets timeout after two hours. This timeout is increased to five hours for datasets in Premium workspaces. If you are encountering this limit, you can consider reducing the size or complexity of your dataset, or consider breaking the dataset into smaller pieces.

Regards,

Xiaoxin Sheng

[lee_a]

i Xiaoxin, 

The data source used is a "Sharepoint folder". 

The sharepoint site is the site that is created for the PowerBI Service Workspace I published the report to. 

This is not an on-premises date source so I've been connecting directly and not through an on-premises gateway. 

I'm not doing scheduled refreshes at this time. Only manual refreshes. I'm ensuring I've authenticated appropriate prior to hitting refresh by signing in through the OAuth setting. 

I've reviewed the troubleshooting link you provided and the only info I could was as follows:

"Microsoft is investigating a solution that allows the data loading process to refresh the token and continue. However, if your Dynamics CRM Online or SharePoint Online instance (or other AAD OAuth data source) is so large that it could run into the two-hour data-load threshold, you may experience a data load timeout from the Power BI service as well.

Also note that, for refresh to work properly, when connecting to a SharePoint Online data source using AAD OAuth, you must use the same account that you use to sign in to the Power BI service."

I do not believe the 2hr time-limit is  what's causing this issue as the time-out occurs much earlier than 2 hrs. A full refresh on my local laptop with 8gig of ram takes less than 2hrs as well, I would assume that PowerBI Service would be able to complete this refresh even quicker than that. This refresh failed within 5min. 

The refresh fails 95% of the time but in the rare 5%, it succeeds. If there was a setting issue the failure would be consistent.

Is there a bandwidth limit on refreshes? My dataset is only 4gb in size. 

[v-shex-msft]

Hi @lee_a,

I check on known issues but not found any similar, I also can't reproduce your issue. Maybe you can submit a support ticket for better support.

submit a support ticket

Regards,
Xiaoxin Sheng