Onedrive data refresh failure

[burnie21]

Hi,

I am getting a data refresh error on a file hosted in OneDrive, using data managment gateway (Enterprise). The file refreshes fine using excel in the desktop, but when I try to refresh in Power BI it fails. I have other reports in the same OneDrive, connecting to the same gateway and on-premise database, they all refresh consistently without failure. The error message that I am receiving doesn't provide any details either. Is there somewhere where I can get more details of the root issue?

Thanks

[dimazaid]

Hi @burnie21,

You do not need an Enterprise Gateway to connect to your OneDrive file, you should be able to connect to it as any cloud resource. 
What other data sources you have in the same PBIX?

[burnie21]

Hi dimazaid

I am using the Enterprise Gateway to refresh the connections that are contained within the excel file. The data sources are used in a powerpivot model that connects to an on premise SQL database. The data refreshes just fine when I run the refresh in excel but I get an error when refreshing in power bi.

Thanks

[dimazaid]

I see. This scenario is still not supported. If possible, can't you connect the SQL datasource to your PBI directly?

[burnie21]

Sorry but I thnk it is supported, otherwise this article wouldn't have been published:

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-excel-file-onedrive/

Also, as mentioned earlier, I have other excel workbooks, located in a oneDrive, that we are refreshing on a regular basis using Power BI Enterprise Gateway.

[v-qiuyu-msft]

Hi @burnie21,

Based on my understanding, the Excel for Power Pivot data model retrieve data from SQL Server database and this Excel file is stored in OneDrive. Then you connect to this Excel file in Power BI Sevrice. Right?

In your scenario, I would like to know how do you connect to OneDrive Excel file? Use "Import Excel data into Power BI", or "Connect, Manage, and View Excel in Power BI" option? And are you connecting to a personal OneDrive, or OneDrive for Business? Please share more information for us, so that we are trying to reproduce the issue.

In addition, you can take a look at those articles about how to troubleshoot refresh issues:

Tools for troubleshooting refresh issues

Troubleshooting refresh scenarios

Best Regards,
Qiuyun Yu

[burnie21]

Hi Qiuyun Yu,

All of your assumptions in your first paragraph are correct. Regarding your question of how we are connecting the OneDrive file, we chose the option of "Connect, Manage, and View Excel in Power BI", and this file is stored in a OneDrive for business folder.

Thanks,

Brian

[v-qiuyu-msft]

Hi @burnie21,

Based on my test, refresh data from OneDrive file doens't need gateway. When the SQL Server table is updating with new records, we need to refresh the Excel via Data -> Refresh All button, then save the change. Then when we click Refresh Now in Power BI Service, the PowerPivot table data will update correspondingly. Please follow the steps like us to check if the issue persists.

Best Regards,
Qiuyun Yu

[burnie21]

Sorry but that doesn't make sense. My understanding is a gateway is necessary in order to process any queries to on-premise sql databases. What you are describing suggests that a gateway isn't necessary for running queries to SQL on premise.

[Seth_C_Bauer]

@v-qiuyu-msft I'm with @burnie21 here. Everything I'm testing around the "connect" scenerio to Excel in OneDrive for Business is either not working or results in an error with the EG. The documentation lays out that the refresh scenerio in this case should require a gateway to use the connection to the SQL server via the Excel file. And that the data will be updated in the Excel file.

Your test scenerio is flawed as you are manually refreshing from the Excel file. The test should be initiated from the Service and the data should update in the excel file via "Refresh Now".

The scenerio works with my personal gateway, but when I switch over to the EG, I had one successful execution of the "Refresh Now" process, but it didn't update the data in the Excel file. When I switch back to the PG, it works again. (albeit I have to hit F5 to refresh the connection in Power BI to see the updated results reflected.)