Can I use Enterprise Gateway connections with Excel Online?

[MartinHatch]

I have Enterprise Gateway configured to connect to on-prem SSAS reports. I can create Power BI reports and refresh them. All is working. 

I have then created an Excel Workbook and, using the Power BI ribbon, connected to my Power BI Dataset (which uses a Gateway connection), created a simple Pivot Table (and was summarily prompted to login to Power BI).

When I'm running in the Excel desktop client, it all works fine.

BUT ... when I upload either:

* To a SharePoint library

or

* to Power BI (from Create > Dataset > Files > Local) 

Then Data Refresh just keeps failing.

My key questions:

Is use of an Enterprise Gateway data connection supported in Excel Online??

Is it possible to surface an Excel Pivot Table in Power BI, which uses an Enterprise Gateway connection as the data source?

[v-frfei-msft]

Hi @MartinHatch,

Based on my test, I followed the steps as you described, here I cannot refresh the workbook in Power BI Service either. Refresh in Power BI Service is not supported for the Excel workbook that connecting to Power BI datasets.

To work around this issue, you can connect to the SSAS data source directly using excel instead of getting dataset in excel from power bi service.

Then in Load To, make sure you select Add this data to the Data Model.

After that we need to add the data source in gateway to set schedule refresh for the workbook.

For more details, please check the online document.

Regards,

Frank

[MartinHatch]

Yeh the problem with that is that you can't "add to data model" when you are using a large compiled SSAS with row-level security.

This is really frustrating as this is specifically what OLAP cubes were designed to deliver, and a lot of companies have an awful lot of collateral and expertise in Excel based reports and dashboards.

For now I guess I have to just put it down to "it doesn't work" 😞