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
MattTaylor
Regular Visitor

Datasets with SQL Source and OneDrive Source

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

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @MattTaylor,

 

As On-Premises Gateway does not support OAuth while OneDrive requires OAuth.

 

q7.PNG

 

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

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

View solution in original post

6 REPLIES 6
v-qiuyu-msft
Community Support
Community Support

Hi @MattTaylor,

 

As On-Premises Gateway does not support OAuth while OneDrive requires OAuth.

 

q7.PNG

 

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ahipperson
Frequent Visitor

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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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.

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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