Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
febyte
Advocate I
Advocate I

SharePoint 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?

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community 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.

99.png

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.

View solution in original post

6 REPLIES 6
v-xicai
Community Support
Community 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.

99.png

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.

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.

@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

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.

@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.

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors