on prem gateway including cloud data sources when I publish report to Power BI Service

[jasonyeung87]

Hi,

I have setup a data gateway on my local on prem server which contains a SQL server and analysis services. When I publish a report with the analysis service as the data source, I am able to use the data gateway to connect back to the analysis service and to schedule data refresh.

I have another report which has 3 data sources: the same analysis service, Dynamics CRM (connected via OData Url) and a local excel file. When I publish the report, the data gateway fails. It looks like trying to include the other 2 sources in the gateway.

Because this gateway is used by a lot of our other reports, I don't want the other 2 sources to be included. One is an OData feed, which is in the cloud, so I'm not sure why there's an attempt to include it. The local excel file will eventually be phased out.

I was wondering is there a way to exclude these data sources from the data gateway?

[jasonyeung87]

@lbendlin ,

thanks for the clarification. I was able to set the data gateway to exclude the  cloud data sources (odata) with the information from this link:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-mashup-on-premises-cloud

Once I check the setting, I was able to exclude the oData CRM from the gateway.

I'll look into the OneDrive solution for the file. This file is mainly for proof of concepts.

Jason

[lbendlin]

"I am able to use the data gateway to connect back to the analysis service and to schedule data refresh."

No, that's not what is happening.  You probably meant you were able to live connect to the SSAS source through the gateway.  There is no scheduled refresh for SSAS.

A "cloud" data source does not automatically mean " a cloud data source that is accessible from the Power BI Service (Azure)" .  Generally you don't need gateways for Sharepoint/Onedrive or Azure dbs.  Very often you need gateways for other sources like your ODATA feed, or for AWS etc.

Using a local Excel file as a data source is generally not recommended. The gateway has no idea how to connect to your PC (which may not even be switched on).  The recommendation is to move that file to a OneDrive location.