New on-premises gateway & mixed on-prem and cloud data sources

[Anonymous]

Using the new On-Premises gateway, has anyone been able to successfully setup such a report that uses both on premises databases and Excel files hosted in SharePoint Online? Everything works fine in Desktop but when published to Power BI Service we are unable to attach the report to the gateway if the report contains a connection to SharePoint Online (with DB connections, it works fine). If we remove Excel (SharePoint Online) references from the report, gateway option is visible in the dataset Gateway connection settings. If we add them back, gateway option disappears.

We are able to get the thing working by hosting the file in a server that can be connected using the gateway (file connection is configured to the gateway settings), but how are we able to use SharePoint Online as a data source successfully as we need to have version control etc.

[v-yuezhe-msft]

Hi @Anonymous,

Based on my test, when we mix on-premises data source and cloud source in PBIX file, we are not able to schedule refresh for the dataset after we publish the PBIX file. I encounter the same issue as yours that the gateway option is disabled. However, we are able to use “Refresh now ” to refresh the dataset on demand.

In your scenario, I would recommend you migrate data from on-premises SQL Server databases to Azure SQL database, then set up report by using both Azure  SQL databases and Excel files hosted in SharePoint Online. This way, you are able to schedule refresh for your dataset, and as you are using cloud data sources, there is no need to install gateway.


Thanks,
Lydia Zhang

[dave_blount]

This is really poor. This would be a very common customer scenario. And migrating databases to the cloud is not a viable option for many. Is there a uservoice posted already for this I could upvote on?

[Anonymous]

See if this link helps - https://powerbi.microsoft.com/en-us/blog/on-premises-data-gateway-february-update-is-now-available/