Ivaylo Shalev on 29 Jul 2022 10:22:33
Analyse in Excel works for SQL Server DB datasets with Connect live mode.
Currently you can connect Excel Online spreadsheet to table(s) in SQL Server DB you have on-premise (via On-premise Gateway) and browse the data in real-time as it is a live mode connection.
However, for some reason this doesn't work when we try to Analyse in Excel a dataset with connect live mode to SQL Server Analysis Services (cube).
PowerBI allows to create live connection to cube, deploy the report and edit it in the web browser etc. but Analyse in Excel of the PowerBI dataset fails.
I contacted MS support and this is their explanation:
Connecting to on-premise SSAS via Power BI Datasets is currently not supported in Excel Online. The engineering team is aware of this limitation and taking this into consideration in the planning for future improvements.