cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bitwize_PowerBI Advocate III
Advocate III

instability in the Power BI auto refresh

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

 

error1.jpg

 

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)

 

error2.jpg

 

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

3 REPLIES 3
Microsoft
Microsoft

Re: instability in the Power BI auto refresh

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

Thanks!
Dimah

Microsoft
Microsoft

Re: instability in the Power BI auto refresh

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 Advocate III
Advocate III

Re: instability in the Power BI auto refresh

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

 

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors