Help!! - Having trouble adding a SharePoint Excel to an On-Premises Data Gateway

[Anonymous]

Hi,

I have a dedicated Server running On-Premises Data Gateway. Through logging on to that Server I can see the Data Gateway is installed and running.

I logon to the Power BI Service account (UserA) that will be used to add datasources to the Data Gateway. This is the same account that was signed into when configuring the Data Gateway on the dedicated Server. So the UserA Power BI Service account should see the configured Data Gateway available to add data sources against it - and I confirm I can see it.

I have an Excel file residing in a SharePoint location. I want to add that Excel file as a data source against the Data Gateway via the UserA Power BI Service account. When attempting this is is failing - why?  Below is the screenshot I receive notifying of the error (I've hidden the full details but it was only the SharePoint path). I've tried connecting to this Excel file in SharePoint using Datasource Type = 'File', 'SharePoint, and 'Web' but no joy on any of the attempts.

Two tests I have done to narrow down the issue:

1) When opening the .pbix file using the UserA account and clicking 'refresh', it does successfully refresh the data (of course I am prompted for to provide the UserA credentials). From this test, I know that UserA has permissions to the source data and it's locations.

2) In the UserA Power BI Service account I add a datasource to the Data Gateway. This datasource is an Excel file on the local drive. The datasource is added successfully. From this test, I know datasources be added to the Data Gateway using the UserA account.

With the two tests above having completed successfully then why is the failure I'm receiving happening?

Thanks in advance.

[v-yuezhe-msft]

@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

[Anonymous]

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.

[v-yuezhe-msft]

@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,
Lydia

[Anonymous]

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

[v-yuezhe-msft]

@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

[Anonymous]

Thank you Lydia.

Just need some clarification.

An On-Premises Data Gateway can be installed using a single Power BI Service account (i.e. UserA) and that same On-Premises Data Gateway can be shared with other Power BI Service accounts (i.e. UserB). It can be shared by making UserB as Administrator for the Gateway and you can provide UserB with permissions for the added datasource(s) too. This means that both UserA and UserB, from within their own Power BI Service accounts can schedule the data refreshs and configure the Gateway.

With a On-Premises - 'Personal' Data Gateway, only the Power BI Service account that installs the Personal Gateway can use that Gateway. For example, if UserA downloads and installs the Personal Gateway then only UserA can use the Personal Gateway. UserA cannot share the access/privileges for the Personal Gateway with another user.

Are both of my statements correct?
Thanks.

[v-yuezhe-msft]

@Anonymous,

Yes. Your statements are correct.

Regards,

[Anonymous]

Thanks for your help on this.

[v-yuezhe-msft · Accepted solution]

@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