Forum Discussion
Desktop vs Service and data refresh from source files in OneDrive for Business
I use Power BI desktop to create my reports. I have a Power BI Pro subscription. I have the data gateway running on my server.
I store all of my source data in OneDrive for Business (and the PBIX files, too, for that matter). My source files are a mixture of CSV, Excel, and MDB database files.
When the report is done, I publish it up to the service and then publish them to the web as needed.
I'm having trouble getting the reports to refresh based on updated data as I expected. I export new up-to-date source data files and overwrite the existing files in the OneDrive for Business (OneDriveFB) folders.
I'm using OneDrivFB explicitely because it supports better data refresh options, but I don't seem to be getting those benefits. Per this doc: https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/#local-files-and-files-on-onedrive-or-sharepoint-online
I suspect it's because I'm using PowerBI Desktop > Get Data > File to navigate to the file I want and bring the data into the report and Power BI Desktop treats it like any other file source without recognizing that its in OneDriveFB.
In order for the sweet frequent refreshes to work with OneDriveFB, do I have to create my reports in the Power BI Service and use Get Data > File > OneDrive for Business as shown in this doc? https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-5-4-connect-onedrive-for-business/
In looking at my options in the Schedule Refresh settings, I see signs of this issue but am not sure what to do about. See screenshots:
I guess, at the end of the day, my question really is: What is my workflow supposed to be to enable the best options for frequent data freshes using .CSV, Excel, and MDB files? Do I need to be creating creates online using the Power BI Service, or can I still use PowerBI Desktop?
As you can probably tell, the whole Power Bi Desktop/Service issue has been extremely confusing and frustrating for me.
I also just found this link which is very much on subject but I still haven't gotten a clear answer from it: https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-desktop-file-onedrive/
10 Replies
- I have exactly the same issue and spent hours trying to understand the intricacies of "getting updates" to PowerBi services.
I have found out that excel files to be updated automatically need to be in table form and I am testing that, even if it defies the purpose of powerbi .jmdh, that is very interesting. I am interested to hear what your testing finds. If you validate this, I think you should provide some feedback to MS about this
- Matt, this is what I have observed. In Power BI Service I can connect to OneDrive for Business files and by default refresh hourly (awesome). Using Desktop there is no direct connection like in Service > Get Data > OneDrive for Business (ODfB) so I use the Web connector like the article mentions. This bring the Excel file data in from my ODfB file but not in the same way that the Service does. In the screen shot Item A show Schedule Refresh of the PBI Desktop solution deployed to PBI Service. Refresh requires turning on "Keep your data up to date". The scheduled refreshes show under Scheduled (1) and On Demand refreshes show in the OneDrive tab (2). Item B in the screen shot shows the solution where Get Data in the PBI Service was used to directly connect to a ODfB csv file. It also shows that by default ODfB files are kept up to date hourly. Will PBI Desktop get a native OneDrive connector?
I wrote an article on this topic a few weeks ago. http://exceleratorbi.com.au/data-modelling-onedrive-source-files/
My expectation is that your reports should update within an hour of the ondrive source data updating. Did you use the same process as i described in my article?
MattAllington, love that article. Thanks. I haven't tried connecting using a web URL yet, but I will soon. I was bringing in files from OneDrive using the file picker.
I'll try your method, soon, though.
