Forum Discussion
Advocate ISharePoint Online and Gateway
I have a gateway that's connected to an SQL Server and I want to include Excel content from SharePoint Online. When I tried simply adding it as a data source it did not work because the gateway can't support SharePoint Online and I can't select which data sources use the gateway and which do not. I tried creating a separate dataset for the SharePoint Online content and including the dataset as a data source but I got an error that the "Connect Live option is disabled". I tried creating a dataflow for the SharePoint Online content but I wasn't able to add it because the gateway couldn't support it and I couldn't just tell it not to use the gateway.
Is there any solution to this problem? Is there any way to include on-prem SQL Server and SharePoint Online content in the same report?
Hi febyte ,
You can choose SQL Server database connector with DirectQuery mode or Import mode to connect on premise SQL Server, see :Refresh data from an on-premises SQL Server database, and choose SharePoint folder connector to connect excel files of SharePoint online at the same pbix file in Power BI Desktop, see: Loading Excel Files from Sharepoint.
Since your database combines on premise data source and cloud data source at the same time, the gateway need to be installed and set, add the two data sources of your dataset to gateway in Power BI Service, as described in Add a data source. Then you can use manual refresh or schedule refresh to get updated data, see more :On-premises data gateway , Configure scheduled refresh .
Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
v-xicaiCommunity Support
Hi febyte ,
You can choose SQL Server database connector with DirectQuery mode or Import mode to connect on premise SQL Server, see :Refresh data from an on-premises SQL Server database, and choose SharePoint folder connector to connect excel files of SharePoint online at the same pbix file in Power BI Desktop, see: Loading Excel Files from Sharepoint.
Since your database combines on premise data source and cloud data source at the same time, the gateway need to be installed and set, add the two data sources of your dataset to gateway in Power BI Service, as described in Add a data source. Then you can use manual refresh or schedule refresh to get updated data, see more :On-premises data gateway , Configure scheduled refresh .
Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- febyte
Setting "Allow user's cloud data sources to refresh through this gateway cluster" seems to have allowed the SharePoint Online data source to refresh directly and the SQL Server data sources to refresh through the gateways, which is exactly what I wanted. The SharePoint gateway data source requires Anonymous or Windows authentication so I don't think I can add the data source to the gateway even if I wanted to.
Gladiator909Helper I
febyte Hi there, I came across your feed and I am facing a similar issue.
I was wondering if in your data model, you were appending datasets from different sources: SQL Server & SharePoint combined together. Or were you just using relationships?
I appended data from Oracle server and SharePoint and even after setting "Allow user's cloud data sources to refresh throught this gateway cluster" it did not work.
Thanks for your help
