Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have been searching for an answer to this for a few hours but no luck!
I have set up an on-premises data gateway on a server in our company. A datasource has been added in the gateway settings in Power BI with the information required to connect to our SQL database.
The data refresh in Power BI works perfectly for reports where the SQL server is the only source, or where a OneDrive file is the only source.
However, as soon as I link to a OneDrive Excel spreadsheet and SQL server in the same dataset, the refresh no longer works. When I look at the settings for the dataset, I am told I need to install a personal gateway and I can no longer select the gateway I have already set up. Adding the link to the file in the gateway settings doesn't help either. If these two sources work fine independently, why don't they work in the same dataset?
Please coul you tell me what am I doing wrong, and how to I fix it?
Thanks,
Matt
Solved! Go to Solution.
Hi @MattTaylor,
As On-Premises Gateway does not support OAuth while OneDrive requires OAuth.
If we have a dataset that has both OneDrive and on-premise data sources, that dataset cannot be refreshed using an On-Premises Gateway. To work around the issue temporarily, we can use personal gateway. For more information, please refer to kevhav's post in this thread: Unable to schedule refresh with sharepoint and database.
Best Regards,
Qiuyun Yu
Hi @MattTaylor,
As On-Premises Gateway does not support OAuth while OneDrive requires OAuth.
If we have a dataset that has both OneDrive and on-premise data sources, that dataset cannot be refreshed using an On-Premises Gateway. To work around the issue temporarily, we can use personal gateway. For more information, please refer to kevhav's post in this thread: Unable to schedule refresh with sharepoint and database.
Best Regards,
Qiuyun Yu
I'm getting the same thing when using different data sources (OData and Analysis Services). I can refresh in Power BI desktop but once published online it becomes impossible to refresh.
I found it raised here: https://community.powerbi.com/t5/Integrations-with-Files-and/Dataset-refresh-asks-for-installation-o...
The solution provided is not really workable, so I would be interested in finding out if anyone else has resolved this.
Thanks for your reply. I tried the steps in the link and they didn't work for me. Do you know if there is a way to get Power BI to ignore the OneDrive spreadsheet, as it doesn't really need to be refreshed? It is the SQL data that is important, the data in the spreadsheet won't change,
@MattTaylor@ahipperson@Greg_Deckler I'm going to jump in this thread as well, because other than some moderators giving answers to this, I haven't found any official documentation that states the OPDG can only handle one source, but that seems to be the case... And the recommendation I keep reading is "use the personal gateway". Would love to know if I missed something, or if that's the official stance.
Found this, apparently combining on prem and online data sources is just an idea at the moment https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13405644-combine-enterprise-gatew..., might be worth following the link and supporting the idea!
@MattTaylor in your case, if the OneDrive file doesn't need to be refreshed you could create a new table in PowerBI desktop and put the data in there rather than linking to it. I've not experimented with it but it might work for you.
Sounds like you might want to post this as an Issue. https://ideas.powerbi.com/forums/360879-issues
I think I have seen something like this come up before, sounds like the refresh is getting confused because some of your data is on-premises and some is in the cloud.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.