how to open an on-premise SQL AS dataset in excel via powerbi enterprise gateway?

[krazineurons]

Hi,

I have a SQL AS Database on my on-prem server and i was able to connect it to powerbi using the On-Premise application gateway that is running on my on-prem machine that has connectivity to the on-prem SQL AS Database server.

I would like to now open this report inside excel from my home using the analyze in excel feature. That means i am hoping to connect to the on-prem SQL AS Database via PowerBI inside Excel. I assume since i have connected it via the Application Gateway, PowerBI should be able to proxy the requests through the gateway. But it is not working. If i look at the connection string in the .odc file that powerbi provides, it is making a direct connection to the on-premise AS database server instead of proxying it via powerbi endpoint.

What have i done wrong? is it not a supported scenario?

[v-qiuyu-msft · Accepted solution]

Hi @krazineurons,

When we use Analyze in Excel feature, the  .ODC file has an MSOLAP connection string that connects to the dataset in Power BI. When we analyze or work with the data, Excel queries that dataset in Power BI and returns the results to Excel. See: Analyze in Excel.

As your dataset is connect to SSAS, Power BI use on-premise data gateway to get data from SSAS. When you interact with the data in .odc file, it open connection to dataset in Power BI, then Power BI use data gateway to access on-premise data gateway.

Best Regards,
Qiuyun Yu