Forum Discussion
Help!! - Having trouble adding a SharePoint Excel to an On-Premises Data Gateway
Anonymous,
Please mark appropriate replies as solutions to close this thread if your issue is solved. That way, other community members would easily find the answer when they get same issues.
Regards,
Lydia
Anonymous,
Does your Excel file reside in SharePoint Online site? If that is the case, when your dataset only contains the Excel data source, gateway is not required to refresh your dataset.
In Power BI Service, go to Settings->Datasets and find your dataset, you should find that Power BI Service connect directly to your data source, after you edit the credential for your data source, you are able to set schedule refresh for the dataset.
Regards,
Lydia
Thank you Lydia.
The Power BI Dashboard has data from SharePoint (Excel) and On-Premises. I wondered if an On-Premises Gateway was required for both types of datasources (including SharePoint Online) if the report took data from both Online and On-Premises data.
I guess not. I have a blocker preventing me from testing this right now, but once removed I'll test without the Gateway configuration for SharePoint Online datasource.
Anonymous,
If your dataset combines sharepoint online data source and on-premises data source, please use personal gateway to refresh your dataset. On-premises gateway is not suitable for this scenario as it doesn't support OAuth type authentication, thus we are not able to add SharePoint Online data source within on-premises gateway.
Regards,
LydiaThanks for your prompt response.
I have 3 questions now:
1) You mention to use 'personal gateway' but I understand the last day a personal gateway could be used was the 31st July 2017. If this is correct, then we only have two options now a) On-Premises Gateway - which you've stated cannot authenticate SharePoint Online data, b) On-Premises (Personal Mode) Gateway - to clarify, I'm aware that when the datasource is SharePoint on its own then no Gateway is needed, however when the datasources are in the cloud (i.e. SharePoint Online) and on-premises (i.e. SQL DB) then both datasource types (ncluding the SharePoint Online datasource) need to be added to the Personal Mode Gateway?
2) The two datasources are SharePoint Online and an ODBC connection. I've just read the On-Premises Personal Mode Gateway doesn't support an ODBC connection using Windows credentials. Is this correct? If yes, how will I satisfy my requirement of refreshing SharePoint Online data (can't use On-Premises Gateway) and refreshing ODBC data (can't use Personal Mode)?
3) The On-Premises Gateway is on a dedicated server to allow refreshing 24/7. I am aware that an On-Premises Gateway and a Personal Mode Gateway can coexist on the same server (correct me if I'm wrong). The On-Premises Gateway can can be scheduled for refreshes when everyone has gone home as the server is always on, however how can I ensure the refreshes using the Personal Mode Gateway execute each time even when everyone has gone home? Would I have to install the Personal Mode Gateway on the same server as the On-Premises Gateway and just add my datasources to the Personal Mode Gateway?
I hope you understand what I'm trying to explain.
Thank you.