Missing "live connection", only "import" or "direct query", power BI desktop to on-prem SSAS

[allmhuran]

We have a tabular SSAS cube deployed to an on-prem (azure VM) SQL 2016 analysis services server.

A user is attempting to explore this cube via power BI desktop.

When setting up their connection they are choosing "SQL Server Analysis Services Database".

Their connection settings dialogue only allows them to select either "import" or "direct query". 

The Microsoft documentation here suggests that they should be able to choose between "import" and "Connect live":



Why would my user's experience not match the documentation? SSAS 2016 doesn't even support "direct query" mode.

[allmhuran]

Thanks Owen, that was indeed the issue. We won't be able to use directQuery to build composite models until we upgrade our instance to SSAS2022 and update our models, but as it happens the user did not in fact need the additional data in this case (it was already in the model!). Cheers.

[OwenAuger · Accepted solution]

Hi @allmhuran 

It sounds like your user has already connected to another data source and is now adding a connection to SSAS. If you want to see the "Connect live" option, the user should start in Power BI Desktop with no data loaded, then connect to the SSAS data source.

"Connect live" or "live connection" refers to a connection to a single SSAS dataset (or Azure Analysis Services or Power BI dataset). With live connection, the dataset is remote, and Power BI queries that dataset to produce the required visuals.

However, if you have already connected to another data source in Power BI Desktop and then attempt to connect to an SSAS data source, "Connect live" is therefore no longer available.

In this situation, the options are:

1. DirectQuery: This is similar to "Connect live" in that it connects to a remote dataset, but allows connections to other sources as well within the same "local dataset". This produces what is known as a "composite model". With this method, there is the ability to rename the SSAS tables/columns/measures and add columns, while the source dataset remains remote.
2. Import: Tables are imported from SSAS into the local model, similarly to any other imported table.

Article to refer to:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models