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.Anonymous,
1) I mean On-premises gateway(personal mode), this gateway us able to refresh your dataset when you combine online data source and on-premises data source in a single dataset.
2) ODBC connection based on Windows authentication is currently not supported. If you use other type authentication in your ODBC source, the On-premises gateway(personal mode) can be used. If you connect to ODBC data source using windows type auth, please split your data sources(Sharepoint online and ODBC) into different datasets, then use on-premises gateway to refresh the dataset that contains ODBC data source. The dataset that contains SharePoint online data source doesn't require gateway to refresh.
3) The two gateway can be installed on same server, ensure that the server is always on and connected to internet, dataset can be refreshed even everyone has gone home. Go to Settings->Datasets and find your dataset, choose on-premises gateway (personal mode) option, after you edit the credential for your data source, you are able to set schedule refresh for the dataset using this type of gateway.
Regards,
Lydia