instability in the Power BI auto refresh

[Bitwize_PowerBI]

Hello,

I’m having some stability issues with a scheduled refresh from the Power BI service.

From time to time the auto-refresh fails while nothing in my setup changed.

My setup is the following:

I have a Power Pivot workbook in a OneDrive for Business folder.  This workbook is linked to the Power BI service to allow an auto-refresh schedule to refresh the Power Pivot workbook.

The Power Pivot workbook has several connections:

• One connection to an on-premise SQL Server database ==> this connection uses the personal gateway installed on the same on-premise server where the SQL Server database resides
• One connection to a ‘custom dimensions’ excel workbook in the same OneDrive for Business folder as the Power Pivot workbook ==> ‘Web’ connection with Oauth2 authentication
• One connection to a ‘custom data’ excel workbook in the same OneDrive for Business folder as the Power Pivot workbook ==> ‘Web’ connection with Oauth2 authentication

After a successful auto-refresh (which takes between 10 and 45 minutes - depending on when the refresh happens - for a total amount of 300.000 records), both my Power Pivot workbook and my Power BI reports and dashboards are up-to-data.  So far so good.

After some days (on occasions some weeks) I get 1 out of 2 possible scheduled refresh errors:

Time-out on the SQL Server connection

After investigating the data management gateway log file on the server, I did not notice any errors and the last log entry for the refresh above was at 8:14.  Only 25 minutes after that Power BI ‘detects’ a time-out.  I can only guess that the time-out is triggered somewhere in the Power BI service.  A local (on premise) manual refresh of the Power Pivot workbook - after a failed Power BI auto-refresh – always succeeds.

Error on the ‘custom dimensions’ excel workbook connection (the other excel workbook connection never produced an error)

This error suddenly appears even if nothing changed: the workbook itself was not changed, credentials have not changed, …  Same thing here: A local (on premise) manual refresh of the Power Pivot workbook - after a failed Power BI auto-refresh – always succeeds.

I have been following this up for months now, tracing what happens on SQL server, investigating the log files of the data management gateway, re-installing the gateway, … without finding any reason for the auto refresh error(s).  unfortunately, I cannot trace what’s happening inside the Power BI service.  I hope that someone can do so and tell me what’s wrong and what should be done to avoid these problems in the future.

Regards,

Dries

[dimazaid]

hi @Bitwize_PowerBI,
We're looking into this issue. Thanks for reporting, I'll reply here again once we have a solution.

Thanks!
Dimah

[dimazaid]

hi @Bitwize_PowerBI,
After looking into the issue, looks like you're running into 2 issues:
- Your dataset size seems to be too big, you mentioned it's taking 45 minutes on desktop, so that might be the reason for the timeout.

- For the token issue, we do not support refreshing the oAuth token. The refresh is failing when the token is expiring, and looks like it's getting refreshed manually when you republish? This is because the personal gateway doesn't automatically refresh the token. 
Here's how to work around the 2 issues above:
1- Connect via Direct Query instead of scheduled refresh. You will need an Enterprise Gateway set up instead of the personal Gateway.
2- Seperate the data coming from Cloud from the on-prem sources, as the Enterprise Gateway doesn't allow you to combine online data sources and on-prem. The token issue will be resolved if you have the Cloud resources uncombined with on-prem.

Hope this helps!
Dimah

[Bitwize_PowerBI]

hello @dimazaid,

thank you for looking at the issue.

The dataset size is 300.000 records in total for all tables in the model and takes 10-15 minutes to refresh early in the morning (when it is sheduled).  During the day while the internal network is heavily used then it can be 30-45 minutes.

This is within Power BI limits, no ?

The 'web' reference and oauth authentication for the workbook data sources is defined in Power BI service, not the workbook itself.  I suppose the Power BI sheduled refresh for the workbook data sources has little to do with the gateway or am I wrong ?

regards,

Dries