SharePoint Online and Gateway

[febyte]

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?

[v-xicai · Accepted solution]

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.

[Gladiator909]

@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

[ColineH]

Hello @Gladiator909 , @febyte  , did you find a solution? 

I'm facing the same issue and I don't want to refresh Sharepoint sources through my On Prem gateway (use to refresh Oracle Data).

I can refresh if I use only relationship but not if I use merge or appen table.

[Gladiator909]

@ColineH so basically what I did was, for the sharepoint source, I created a "DataFlow" on PowerBI service to grab my excel sheet that was on sharepoint. Then on Power BI Desktop, I bring in that particular dataflow and append it to the table from my Oracle database. The auto refresh should work without any issues since you are refreshing a dataflow and the oracle table. Just remember to refresh your dataflow before you refresh the appended tables. Let me know if you have any questions.

[ColineH]

Thanks for your answer, I will test it very soon and I tell you, I never done a dataflow.