Scheduled Refresh for two data sources

[tleslie04]

I was wondering if it is possible to to enable a sheduled refresh when a dataset contains data from two sources; one from sql server and the other from an excel spreadsheet (which can be stored locally or in onedrive). When i create the dataset in powerBI desktop and publish it to power BI, the Gateway connection is greyed out.

[krshaffer]

Did you ever get this figured out. Having the data source available isn't working for me.

[v-sihou-msft]

@tleslie04

"I was wondering if it is possible to to enable a scheduled refresh when a dataset contains data from two sources; one from sql server and the other from an excel spreadsheet (which can be stored locally or in onedrive)."

Yes, we should be able to enable a scheduled refresh when a dataset contains data from two sources. I have tested it on my site(dataset with two data sources, one from sql server and the other from an excel stored on local), it works all fine. 

"When i create the dataset in powerBI desktop and publish it to power BI, the Gateway connection is greyed out."

I have reproduced this issue too. The Gateway connection is greyed out because you haven't add the two data sources to your gateway. Here is the steps to add a data source to gateway from Power BI service.

After you add both the two data sources to your gateway from Power BI service, the Gateway connection will be enabled, and you should be able to schedule a refresh for the dataset.

Regards,

[anithat]

Hi @v-sihou-msft

What you said in adding two datasources to the Enterprise gateway and scheduling it is perfect.

However, the same is not working with Sharepoint as source.

Ex: I have a dataset with 3 diff sources.

Teradata

Odata

Sharepoint

I am able to add Teradata and Odata to on premise gateway but unable to add Sharepoint due to which the data refresh cannot be scheduled on Enterprise gateway.

Sharepoint is authenticated by OAuth and which is a Limitation at the moment.

LimitationsOAuth is not a supported authentication scheme with the On-Premises Data Gateway. You cannot add data sources that require OAuth. If your dataset has a data source requiring OAuth, you will not be able to use the gateway for scheduled refresh.

May I know if there is any solution for this. What would be the better way to approach in this case?

[anithat]

Hi @v-sihou-msft

Could you please provide update on my query. Thank you!

[tleslie04]

Hi Simon

when i try to add the sharepoint file as a data source (excel file using the url - https://xxxxxxxx.sharepoint.com/Shared Documents/xxxxxxx - Copy.xlsx) it tells me that its unable to connect due to invalid credentials, however they are the same credentials i used to connect to the same file via power BI desktop

I have tried a different approach aslo, and that is telling me i need a personal gateway (not enterprise). Are you able to assist

Thank you