2

How to set up automatic hourly refresh of a Power BI dataset from an Excel file stored on OneDrive for Business? What am I doing wrong?

My set up goes the following path on the map of possible connections described by Vesa Tikkanen in the post Power BI: Why my report is not refreshing from OneDrive? Compatibility chart for sources.

enter image description here Original image comes from: https://www.qumio.com/Blog/Lists/Posts/Post.aspx?ID=44

Manual refresh of a dataset works correctly as shown on the picture below but hourly refresh does not even start.

enter image description here

I try to set up automatic hourly refresh by choosing 1 and then 3 "Schedule refresh". I have OneDrive refresh set up for ON. It should be sufficient. Should anything else be enabled? Should "Scheduled refresh" be enabled?

enter image description here

For sure I have good "Data source credentials", because manual refresh works. My PBIX file is also stored on OneDrive so it does not require "Publishing" for PBIX update.

I have sourced my Excel file just like it has been presented in this tutorial: https://youtu.be/uxMlV9KKf6E?t=315

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191

1 Answers1

5

I try to explain the meaning of those 2 different options.

  1. Scheduled refresh is for Power BI to go to source and read new data and refresh the model with the new data.
  2. OneDrive Refresh is for automatically re-uploading the file from OneDrive to Power BI. So if you don't update the file it will upload every hour that same pbix file to service. This behaviour is meant for reports which cannot be scheduled to be auto refreshed but you manually construct new version of the report and that should be uploaded automatically to service after you updating the file in OneDrive.

I think you are trying to implement the automatic refresh. That means that you should uncheck the onedrive refresh and add scheduled refresh to report. This "Onedrive refresh" option is only visible when you originally uploaded report from OneDrive.

OneDrive Refresh is for updating the file, scheduled refresh is to collect data from source

Vesa Tikkanen
  • 91
  • 1
  • 1
    Is what you suggest a gateway scheduled refresh with 8 times per day limit? What should I do to take advantage of the hourly automatic refresh? I really would like to go for it. Should I go another path on the map? Should the pbix be published instead of added to MyWorkspace from OneDrive? – Przemyslaw Remin Nov 16 '20 at 15:12
  • 1
    You are right. For "standard" usecase there is a refresh limit of 8 times per day. Unfortunately there is no shortcut. Premium dataset would make this possible. Either Premium capacity or the new Premium per user licence. (which has no price announced yet as of today..) – Vesa Tikkanen Nov 17 '20 at 16:07
  • Let me comment the other path - starting from Power BI Desktop > Publish > and then straight to point 4 (Data in OneDrive as a Web Source). This path seems to be worse because it does not even show OneDrive Refresh toggle (inside dataset Scheduled Refresh options). – Przemyslaw Remin Nov 19 '20 at 09:41
  • Just adding a reference for @VesaTikkanen's comment. 8 refreshes/day for shared, 48 for premium via https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data#setting-a-refresh-schedule Agreed that you should turn off "OneDrive Refresh" and instead go with just the refresh schedule. Current PowerBI "Refresh" options are: Shared Refresh, Premium Refresh, & DirectQuery. – sgdata Nov 21 '20 at 18:53
  • Is there any update on this topic after 2Y since it been asked? Is it possible to type something in Excel stored on OneDrive/Sharepoint and immediately/soon see the change in PBI without scheduled refresh? – Przemyslaw Remin Oct 26 '22 at 15:11