Unable to schedule refresh with sharepoint and database

[FS]

First off, sorry if this has been already asked/answered.

I am having trouble scheduling a refresh of one file with two data sources - one is a file on SharePoint, the other is a database.

Ive got a gateway set up and working no problem - I have no issues scheduling a refresh when I only use the database as my source of data and I have no problems when using files uploaded on Sharepoint either.

But when I upload my .pbix file with these two sources and go to "Schedule refresh", I get prompted to download data gateway.

I hope this makes sense, my english is not that great.

[DavidMoss]

Hi, Has anyone had an update on this issue where we have two data sources in our development environment been (A) Sharepoint Online and (B) an onPremises database (this user case its MS Access) ?

Both data sources are in the same .pbix file

After publishing the file to the PBI service then when selecting schedule refresh from the dataset ... icon and after clicking 'gateway settings' it is stating personal gateway is offline.

We actually have 2 fully functioning on-premises enterprise gateways which work perfectly for datasets with on-premises data only.

We also have sharepoint only datasets that refresh (they don't need the gateway as they are not on premises).

With this dataset because there are both an online (A) and an on premises (B) data source after clicking 'gateway settings' when configuring the schedule refresh the PBI system does not find either of the on-premises gateways but only states the personal gateway is offline. Implying we need to install and use the personal gateway.

I am understanding that Microsoft advise to use the personal gateway to resolve this issue.

As of this date March 2017 month end is this still advised ?

Or has the On-Premises gateway been updated to accomodate this issue ?

If so please point to where you read this.

Please post your expereiences aswell re dual datasources (A+B) schedule refreshes. Thanks.

[DavidMoss]

I now see this is in discussion here so vote there.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13405644-combine-enterprise-gatew...

[Greg_Deckler]

If either your SharePoint or your Database is on-premises (not in the cloud) then you must have a data gateway for the Service to talk to in order to refresh your data.

[FS]

@Greg_Deckler

The database is on-premises, data gateway is set up and working - already used to refresh few reports.

I have no problems scheduling a refresh when using only sharepoint file/only database as source, but im struggling to schedule a refresh when using both in the same report.

[v-qiuyu-msft]

Hi @FS,

According to your description, it seems that you are connecting to SharePoint Online Excel and on-premise database in the same report, right?

Based on my test, as SharePoint Online doesn't require gateway while on-premise database requires, and those two data sources requires different kind of credentials, you need to install personal gateway to connect to on-premise database and enter credential for SharePoint Online use oAuth2 authentication. See:

Reference:

Power BI Gateway - Personal

Best Regards,
Qiuyun Yu

[FS]

Thanks @v-qiuyu-msft, I tried installing personal gateway and had no problems with setting up scheduled refresh.

But I would like to utilize the on-premises (Enterprise) gateway i have been using for everything else until now for this task as well - is it even possible?

[v-qiuyu-msft]

Hi @FS,

As mentioned in my original post, in your scenario, as the dataset contains two data sources, on-premises data source and SharePoint Online file, it's not supported to use on-premise gateway to build connection. Only personal gateway is available now for us to setup schedule refresh.

Best Regards,
Qiuyun Yu

[kevhav]

I have this same issue.

I have a dataset with some data source files in SharePoint Online, and some on-premise data sources.

I have an "On-Premises Data Gateway" that works great with my on-premise data sources.

But in the Power BI service, I can't set up scheduled refresh of my dataset.

So do I have to also use a "Personal Gateway" for cases like these? Isn't there some way I can use the new "On-Premises Gateway" I already have set up?

[FS]

@kevhav, yes, same issue, thanks for wording it a bit better.

I have set up a Personal Gateway for now, still hoping to make the "On-Premises Gateway" work though.

[kevhav · Accepted solution]

I heard back from Microsoft that...

• Currently, the On-Premises Gateway does not support OAuth (a requirement for SharePoint Online connectivity); so if you have a dataset that has both SharePoint Online and on-premise data sources, that dataset cannot be refreshed using an On-Premises Gateway.
  
  
• As mentioned here, you can use a Personal Gateway to set up a scheduled refresh of a dataset that has both SharePoint Online and on-premise data sources. This is what they recommended.
  
  
• Microsoft is investigating the possibility of a change to allow OAuth with the On-Premises Gateway, but have have no timelines for it.
  
  
• They recommended that I continue to to use the On-Premises Gateway for datasets where it works, but use the Personal Gateway as a "workaround" for my one dataset that has both SharePoint Online and on-premise data sources

[aaronsteers]

I am also affected by this issue, as @kevhav states:

"Currently, the On-Premises Gateway does not support OAuth (a requirement for SharePoint Online connectivity); so if you have a dataset that has both SharePoint Online and on-premise data sources, that dataset cannot be refreshed using an On-Premises Gateway."

Our use case is that we have a sharepoint folder with shared access where users can upload their own data and then integrate it with on-prem DW data. Without the integration in the on-prem gateway, it seems we don't have a way to automate this use case.

Is there a request in the feature backlog that I can vote for? It seems OAuth should be an important part of the on-prem gateway featureset, or if not, we should not recommend users to share their files in OneDrive/SharePoint.

[kevhav]

Using the "Personal Gateway" worked for me. Be sure to note the "Personal Gateway" documentation regarding installing it as an admin. 

It's a bit annoying that now I have both an "On-Premises Gateway" and a "Personal Gateway." They work a bit differently. But at least it works to automatically refresh my dataset whose data sources include both on-premise DBs and SharePoint Online files.

Microsoft had said they might add this capability to the "On-Premises Gateway" in the future. If that does ever happen, then I'll be happy to go back to having a single gateway.