Desktop vs Service and data refresh from source files in OneDrive for Business

[Anonymous]

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-one...

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-busine...

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/

[jmdh]

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 .

[MattAllington]

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

[emmet63]

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?

[jmdh]

Matt,

Thank you. at this stage i still have no been able to have any autmatic refresh from Onedrive pro working. 

I also have issues with the Gateway: the personal gateway didi not unsinstall properly and could not re install.
I Installed it on a second machine and it seems to have priority on anything else (ie the on prmise gateway installed on my own machne).

I XWill contact support at this stage or do you have any better idea?

Jmdh

[Anonymous]

@jmdh, I've had a lot of trouble with the Gateway, too. I've installed and uninstalled it several times, always following the instructions here: https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-scheduled-refresh/

It's a mess, though. Sometimes app.powerbi.com will tell me the gateway isn't available when I know it is. And I've seen it update the data in a report even though it says the gateway is unavailable. It's been very finicky and unreliable.

[MattAllington]

Personal Gateway doesn't uninstall correctly - known issue. You should install the enterprise gateway. It is not difficult (as its name suggests) and has better support. It's shockingly easy, so just do it. Personal Gateway will still be there lying dormant as offline though. 

[jmdh]

Yes, i have also done this and it is easy as you say.

However, you then need to "activate" one by one each data source that you are using in your PowerBI desktop file which is both time consuming and frustrating since it has already been done in the PBI file itself.

Furthermore, it seems that once you have activated the gateway, it bypasses everything else, including the supposed automatic refresh from OneDrive.

I am waiting for support to come back to me on these topics.

Jmdh

[Anonymous]

Thanks, @jmdh. I didn't know there was a known bug there but it makes sense.

I tried and tried to get the Enterprise gateway working but just couldn't get it to work. There were several things going, but one of the errors I saw repeatedly was in trying to add my data sources to the enterprise gateway. It would tell me that I couldn't add a folder becuase it was in use by another process. I wonder if that other process was the zombie personal gateway?

I ended up uninstalling again and setting up the Personal Gateway. I know it's not best practice, but it did at least work.

I'll be interested to hear what you find out from Support about this. I'll keep an eye on the thread.

Thanks again for your insight here.

[MattAllington]

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?

[Anonymous]

@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.