Connecting to the dataset from Excel - different options and issues

[Marcinex]

Hi All, I couldn't find a clear information in the docs nor community forum so it's time to ask. 

People in our organization connect to PowerBI datasets from Excel pivot tables. For the basic use cases it's working fine. But, there are users who creates multiple pivots based on a single connection in the single Excel file. Most of the time it works fine, but there are days (feels like it's random) when such Excel file cannot refresh and gives following error:

This is really random. Our dataset connects to the Snowflake privatelink using Direct Query via PowerBI on premise gateway. Could it be somehow related?

And finally I was exploring different ways of connecting to the dataset from Excel:

1. Data -> Get Data -> From Power Platform -> From PowerBI -> selecting dataset

2. Data -> Get Data -> From Database -> From Analysis Services  -> providing workspace URL as a server powerbi://api.powerbi.com/v1.0/myorg/...

3. Data -> Get Data -> From Other Sources -> Microsoft Query -> OLAP Cubes -> providing workspace URL as a server powerbi://api.powerbi.com/v1.0/myorg/...

When I look into connections detail, I see some differences between options 1 and 2/3:

1. Initial Catalog=sobe_wowvirtualserver-XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX;Data Source=pbiazure://api.powerbi.com

2. Initial Catalog=<dataset_name>;Data Source=powerbi://api.powerbi.com/v1.0/myorg/...

Are these differences somehow significant?

[lbendlin]

Our users have migratged away from Analyze in Excel (MDX queries) towards "Excel with live connection" (DAX queries).  Maybe something to consider for your users too,

[Marcinex]

So I did more digging and it seems to me that "Excel with live connection" is not a replacement for "Analyze in Excel" as it basically gets data from a single visual, it's not the interface to the whole semantic model. The idea behind "Analyze in Excel" is to user be able to create whatever pivot table he or she needs using all metrics and dimensions avaliable in the model. 

[lbendlin]

Yes, it uses a single DAX query against the semantic model. The beautiful thing about this is that it is actually what our users always wanted (they disliked having to wrangle cubes) and it also dramatically reduces the strain on the Premium capacity. 

It's not really related to the visual - you can run whatever DAX you want.

The user can check the connection details to see if the connection is MDX or DAX.

[Marcinex]

So our users differes 🙂 People in my org want to have cube. How to check if the connection is MDX or DAX? Whatever I do, I still see things like "MDX Compatibility=1" in connection details. The only difference is that there can be DAX expression. But I feel like it's not the case when I want to have cube like experience.

[lbendlin]

I am glad I have my users and not yours 🙂 

But you are right, they changed the connection mode for the "live"  connection (which is a bit of a lie anyway) from powerbi://api.powerbi.com to pbiazure://api.powerbi.com - without telling us. Not cool.

[Marcinex]

Is it enough to just enable this option?

As far as I understand, from end user perspective, there's no way to tell what's the connection mode (MDX or DAX).