Reply
Highlighted
Frequent Visitor
Posts: 2
Registered: ‎11-02-2016
Accepted Solution

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


Accepted Solutions
Moderator
Posts: 3,938
Registered: ‎03-06-2016

Re: Datasets with SQL Source and OneDrive Source

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

View solution in original post


All Replies
Super Contributor
Posts: 5,046
Registered: ‎07-11-2015

Re: Datasets with SQL Source and OneDrive Source

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.

Frequent Visitor
Posts: 3
Registered: ‎11-02-2016

Re: Datasets with SQL Source and OneDrive Source

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.

 

Frequent Visitor
Posts: 2
Registered: ‎11-02-2016

Re: Datasets with SQL Source and OneDrive Source

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,

Super Contributor
Posts: 2,887
Registered: ‎06-25-2015

Re: Datasets with SQL Source and OneDrive Source

@MattTaylor@ahipperson@smoupre 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.

Frequent Visitor
Posts: 3
Registered: ‎11-02-2016

Re: Datasets with SQL Source and OneDrive Source

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.

Moderator
Posts: 3,938
Registered: ‎03-06-2016

Re: Datasets with SQL Source and OneDrive Source

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