Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

OneDrive Refresh

I'm pretty new to Power BI, so I'm not really well-versed in the interplay between datasets and reports in Power BI Service. I've read several articles indicating that if a dataset is loaded into Power BI Service, it will update automatically, roughly every hour, without the need for a typical scheduled refresh, which would otherwise be limited to eight times a day). 

 

I created a report in Power BI desktop using an excel file in OneDrive for Business as a data source. I connected to the excel file as a web data source by using the file's URL, as syncing the files to my local machine and connecting to the file that way would prevent the report from updating without my machine syncing. In order to take advantage of the OneDrive Refresh, I saved the pbix file in OneDrive as well, and imported the pbix file into the Power BI service as the dataset for the published report. As far as I understand, OneDrive Refresh regularly looks for changes in the dataset (in this case, the pbix file), and if there are updates, it automatically refreshes. 

 

Here's my question. Since the pbix file uses a separate excel file as a data source that is also saved in OneDrive for Business, will OneDrive Refresh look through the pbix file to the underlying excel dataset, and refresh that as well? I don't foresee making any additional changes to the pbix file itself, but the excel data source will be changed regularly. If this is possible, how do I take advantage of it? Is importing the excel file through the URL the correct method? If this isn't possible, what's the point of OneDrive Refresh at all? 

 

I've seen questions and answers that come close to my situation, but I haven't seen a response that addresses the "lookthrough issue" I'm facing. Any help would be greatly appreciated!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

you're nearly there.

 

Storing the .pbix on the OneDrive is good practice, but has nothing to do with your issue.  .pbix files only live on the Desktop. Once the .pbix is "published" it will live on the Power BI service inside a workspace, split up into a dataset and a report (the latter is optional).  The dataset is what gets refreshed, and it gets refreshed form your Excel file on the OneDrive (NOT from the .pbix unless you re-publish. But that's a whole 'nother story).

 

Now, Microsoft claim that OneDrive changes are automatically propagated within the hour. However we have seen no solid evidence of that in our environment. So what we advise our developers is to add scheduling to the dataset "just in case".  Since it is a cloud data source no gateway is required.

 

Final note: There are subtle differences between these connectors: Excel, File, Web, Sharepoint Folder - they all can access your Excel file, but with varying efficiency.  Note that Sharepoint Folder is basically the same thing as native OneDrive. That's why I prefer it even though it is a tad slower than the other connectors.

View solution in original post

8 REPLIES 8
lbendlin
Super User
Super User

"Onedrive refresh" seems to be a euphemism for any refreshes initiated outside of Power BI, via API call. Can be from Power Automate or from Powershell etc.

HamidBee
Impactful Individual
Impactful Individual

I've just come across one drive refresh now. This is what I've understood so far:

The source file and the pbix file needs to be in one drive, it is then published onto the power bi service. Any changes made from that point to the source file will be detected by power bi within after an hour or so and it will be visible on the service. When I saw you mentioend API I figured I'm totally off the mark here. 

Not totally off, but slightly.  You need to separate data refresh from meta data refresh. Meta data refresh (aka Publish, or PBIX changes in your case) are audited elsewhere, in the tenant audit logs.

HamidBee
Impactful Individual
Impactful Individual

I see. I've set it up now so I'll wait an hour to see if the file updates. I posted a question here asking for the steps but the super user here is asking me to do that and setup a scheduled refresh inorder for it to work: https://community.powerbi.com/t5/Desktop/What-are-the-steps-for-creating-a-one-drive-refresh-in-Powe...

If you read my earlier reply it kinda sorta matches what the other SU says.  Frankly, that whole idea of sharepoint based refreshes seems half baked and fragile. I wouldn't want to base my production process on all these assumptions.

v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Check this document.

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data#onedrive-refresh

 

Best Regards,

Jay

 

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Hi, I experienced similar confusion around the OneDrive sync and the different ways to import Excel files. When I first imported the Excel file using the "Excel" import, the Service told me this was an on prem data source and that I needed to install a Gateway to have a scheduled refresh, even though the file is stored in One Drive for Business (aka a SharePoint folder - I believe the two terms are analogous, or at least share the same backend).  

After reading the OneDrive refresh article I realized I needed to switch the import to be from OneDrive, however I don't see that as an option in the desktop app, only "SharePoint folder".  This at least solved the issue of requiring the Gateway but I am still not seeing the automatic sync described in the article happening. When I click on the Refresh history in the Service, it does show the OneDrive tab and the history of refreshes, but they are all On demand from me manually refreshing. But I do not have the additional sections for "OneDrive credentials" and "OneDrive refresh".
Could someone clarify which Import method I need to use in order to get these OneDrive refresh settings? (e.g. Web, SharePoint folder, Excel, something else??) 

Thanks

lbendlin
Super User
Super User

you're nearly there.

 

Storing the .pbix on the OneDrive is good practice, but has nothing to do with your issue.  .pbix files only live on the Desktop. Once the .pbix is "published" it will live on the Power BI service inside a workspace, split up into a dataset and a report (the latter is optional).  The dataset is what gets refreshed, and it gets refreshed form your Excel file on the OneDrive (NOT from the .pbix unless you re-publish. But that's a whole 'nother story).

 

Now, Microsoft claim that OneDrive changes are automatically propagated within the hour. However we have seen no solid evidence of that in our environment. So what we advise our developers is to add scheduling to the dataset "just in case".  Since it is a cloud data source no gateway is required.

 

Final note: There are subtle differences between these connectors: Excel, File, Web, Sharepoint Folder - they all can access your Excel file, but with varying efficiency.  Note that Sharepoint Folder is basically the same thing as native OneDrive. That's why I prefer it even though it is a tad slower than the other connectors.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors