Use a data gateway - greyed out

[Anonymous]

I have created a simple report with one SQL connection that works fine through our Enterprise Data Gateway.

If I add another dataset, this time from Excel file stored in Office 365 Sharepoint, then the option to use the Enterprise Data Gateway is greyed out.

Is it possible to have data from multiple sources and still use the Enterprise Data Gateway.

The problem i have is without the Enterprise Data Gateway, there is no way to reliably schedule a data refresh?

[v-caliao-msft]

Hi @Anonymous,

Since you have two sub-datasets in your dataset, and one is from Office 365 Sharepoint which is web services. Gateway is not required when refresh this dataset. So the option to use the Enterprise Data Gateway is greyed out.

In your scenario, you can split the dataset into two datasets, and set refresh scheduel for them separately.

Regards,

Charlie Liao

[edhans]

How do you split a data set in two? I have a report that has the following:

• CSV files stored on Sharepoint
• SQL Server tables

I want this to automatically refresh but the data gateway option is greyed out. How do I split it in the PowerBI report so the SQL data is updated?

The report was created in PowerBI Desktop.

[Anonymous]

@edhans this is the same problem that I have.

It would seem impossible to have powerBI online to refresh a data source through the gateway that uses 2 data sources from different technologies.

I cannot find a way of building a report with data from 2 different datasets so there is no way of getting the SQL server queries "ring fenced" so that the data gateway is not greyed out.

This is really frustrating because it means one of the major capabilities of PowerBI, i.e. how to combine data in Excel with SQL in a data model, can only be refreshed from the Desktop version of PowerBI and the republished.  As a result I have to refresh and republish stuff every day manually..

@v-caliao-msft you mention a way of using two "sub datasets" but I cant see how you do this?

[edhans]

FWIW - this is now being worked on. https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13405644-combine-enterprise-gatew...

[Anonymous]

Hi David,

I had an simmilair issue. At the moment the Enterprise Data Gateway does not support Online Sources (SharePoint Online/ OneDrive for Business), but the team is working on it:

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

Meanwhile you could try the Personal Gateway if that suffies your needs. I'm not 100% sure, but I thought that it worked for me. Again, not 100% sure and at the time being i'm not able to test it.