Combine enterprise gateway and online data sources
At this time, a gateway will not function properly if you are using data sources and one is online and one is on-prem. As soon as the online data source is added, the gateway is designed to gray out because Power BI interprets that it is no longer needed. I would like to be able to combine data sources from both on-prem and online sources together with the use of gateways.
Was finished a while back…marking as finished
As of recently I am still having this problem. However, I've found a workaround by utilizing dataflows for the sharepoint part.
In my case I am combining data from SSAS Tabular Database with an Excel file in Sharepoint.
Here are the steps approximately:
1) For the Excel on Sharepoint, I created a dataflow to read the Excel by providing the path/URL to the Excel File. No gateway is selected.
2) In Power Query (Desktop), I created a query for my SSAS database which is on premise (gateway required). Then I created another query where the source is my dataflow that I created in step 1.
3) I published my dataset/report
4) The refresh worked.
Another thing to note here is to make sure you have the most recent gateway installed. This is because the dataflow feature is relatively new in Power BI Service and since you want to use the dataflow as a source in PowerBI you need to make sure you have the most recent gateway installed or at least the one that support connecting to a dataflow from Power Query Desktop.
It is not resolved from my perspective.
Why? We still cannot mix source: Sharepoint with SQL Server.
As i understood, propose of gateway is to have all sources access managed in one place.
Now it is not possible.
Richard Przybylski commented
OK So now that we have PowerApps and they connect to both On Prem and Online sources why are we lacking the same connectivity for SharePoint to SQL? I have a SQL DB I need to query from Online SharePoint using a list. I don't understand the issue. I know Microsoft is trying to do away with any and all custom development here is the thought we can use the Gateway to connect to the on prem source to get the required data. This should be easy as a couple of clicks as it used to be. Taking away capabilities is dumb and adding truly untested functionality is even worse. PowerApps? Seriously? Not all functionality is applied on the status-phone or iPad anything. You would think Microsoft would be thinking that making functionality using Microsoft products to use Microsoft devices would be a better approach then opening things up to iPhones because every teen in the word has one so they can say they do. Microsoft, Marketing is key. Sayin that you products can do something should always be accompanied by detailed instruction of how to. Even auto mechanics have manuals to show them how to and what the specifications of that product are. I need to connect to a SQL Database that has more than 137000 records. SharePoint Online allows 2000. That was nice in 2010 and even then it was cumbersome. Now we are here 9 years later and still don't have a solution, but we have PowerApps that is worthless to me. I put in a Support ticket that we pay a boatload for the tenant and have been told I am going to have to pay for 70 hrs of work at $225/hr. Seriously? You must be joking. I am thinking we consider other solutions.
Got an update from Microsoft:
The refreshes required Personal gateways because there wasn’t an option to set privacy levels for cloud data sources. Recently this was also enabled.
More information at the following link: https://powerbi.microsoft.com/en-us/blog/privacy-levels-for-cloud-data-sources/
This setting has fixed our issue. We can now refresh dataset that merges data from on-prem SQL server and SharePoint Online.
Any news on this? Trying to combine SalesForce data with on premise data, but can't create a SalesForce data source on the gateway
We're having the same issue reported where I'm trying to combine SharePoint data with other data and I can't add SharePoint to my gateway as OAuth isn't an option via the gateway. Please help resolve this as it seems a pretty normal use case.
I am having the same problem. I have lots of reports using data from our SQL server AND Excel files on Sharepoint and OneDrive, and they work just fine, refreshing over an Enterprise Gateway. However, this is the first report where I try to merge queries, looking up values in a SQL table onto the Excel tables, and now it will not refresh no matter what I try. Although I did have one random refresh on demand work in 6 minutes. It refreshes on my Desktop in 5 minutes. I have a ticket in with Microsoft.
Microsoft support have told me that this idea/feature does not yet work on Enterprise Gateway. It only works on the Personal Gateway for now. I've asked them to update this ideas to page to reflect the fact that is is not yet "Complete".
Support: "After looking in to the verbatim we found that you are trying to combine an On premise datsource with cloud datsource (WEB). This is not possible by using the On premise data gateway in enterprise mode. In order to combine the data you should be using the On premise data gateway (personal Mode)."
We can't use personal mode in our case - please allow this feature for enterprise mode as well!
Antonio Palomas commented
It is very important that Power BI technical team responds to this problem that is impacting our business processes.
I have this problem also with SQL database on premisses and Sharepoint online.
Same as Carl.."We also tried refreshing datasets which merge on-prem source (SQL server) and online source (Excel on SharePoint online), and this is still not working, despite enabling the Gateway setting “Allow user’s cloud data sources to refresh through this gateway cluster" Any news about this_??
I am getting this same error message after connecting my datasources to the On-premise Gateway for other sources in the report.
The SharePoint datasource is giving me the error "SharePoint: Request failed: Unable to connect to the remote server" when I initialize a refresh from the Power BI service or when i schedule a refresh through the Gateway.
If i initialize a refresh through the Power BI Desktop, it works perfectly fine.
Also it does not complain about the file that I am loading but complains about the another table where I am merging the SharePoint file with another file
All the steps mentioned in this document has been performed and still the same error message.
Please if you can help.
We also tried refreshing datasets which merge on-prem source (SQL server) and online source (Excel on SharePoint online), and this is still not working, despite enabling the Gateway setting “Allow user’s cloud data sources to refresh through this gateway cluster.”
It doesn't work with SharePoint Online et SQL Server On-premise.
Refreshing datasets containing queries that merge data from a web source with an on-Prem source still does not work. The linked documentation describes a procedure that doesn't solve the problem. This is not finished an marking it as "completed" is wrong.
I ran a couple of tests and documented them in an issues post where people can vote for it:
This is frustrating, we've been waiting for this fix for more than a year!
Daniel Davis commented
It's convenient this is marked complete, when it doesn't fully work. I opened a support request trying to trace this issue and was sent tracing in circles providing logs and network traffic captures to no avail.
Daniel Davis commented
Has anyone found any workaround for this? It is crippling my ability to publish our latest dataset since migrating to using CSV files in Sharepoint to filter and augment database results
David Cresp commented
This is a major flaw in Power BI Gateways. Please fix this asap.
David Cresp commented
I have just come up against this problem. One year on from starting it still seems to be a major issue. Please fix this one MS.
Melissa Coates commented
In the settings for the On-Premises Data Gateway, there's an option to “Allow user’s cloud data sources to refresh through this gateway cluster.” You can see that option shown on the 2nd screenshot on this page: https://docs.microsoft.com/en-us/power-bi/service-gateway-mashup-on-premises-cloud. Hopefully that option will help some of the people still commenting on this being an issue.