power bi cannot acces to a ssas, bu excel can

[fsim]

Hi !

I have this strange behaviour I don't understand, but I'm pretty sure you will.

I have this on premice server running a sql and SSAS service that I want to connect to with PBI.

I can reach the SQL server with no problem, but I got the " the server you atr trying to connect doesen't have any models or you don't have the permission to access them" error. 
But if I use Power query in Excel, I get my data.

My conclusions: it's not the firewall (unless power query in excel is using another port ?), nor the SSAS.

Any idea is welcome 🙂

[fsim · Accepted solution]

And so here is the reason.

It's all about delfault database. Excel don't expect a default database when you connect to SAS. So if no deflaut database is defined on the server, it will take the first one. it's only used to allow you to browse the databases on the server.
PowerBi don't do that. This means, you HAVE TO define a database (even if it's optional) at the connection time. And, then, it works.

[V-pazhen-msft]

@fsim 
Do you have the correct SQL Server version? You can check the following limitation of Connect to SSAS multidimensional models: 

Considerations and limitations

There are certain limitations to using SSAS MD:

• Only enterprise and BI editions of SQL Server 2014 support live connections. For the standard edition of SQL Server, SQL Server 2016 or later is required for live connections.

• Actions and named sets aren't exposed to Power BI. To create visuals and reports, you can still connect to cubes that also contain actions or named sets.

• When Power BI displays metadata for an SSAS model, occasionally you can't retrieve data from the model. This scenario can occur if you've installed the 32-bit version of the MSOLAP provider, but not the 64-bit version. Installing the 64-bit version may resolve the issue.

• You can't create report level measures when authoring a report that is connected live to an SSAS multidimensional model. The only measures that are available are measures defined in the MD model.

Reference : https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-ssas-multidimensional

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fsim]

Hi Paul,

thank you for your answer.

The server I'm trying to reach is basically a clone of the prod server (both are virtual machine) dedicated to dev. I can connect to the ssas on the prod server with power bi without problem. 

everything is in 64bit version. SQL version is, as far as I can see, version 17 

[amitchandak]

@fsim ,

Check if this can help

https://www.youtube.com/watch?v=3ZhqVXOVVzw

https://powerbi.microsoft.com/es-es/blog/power-bi-analysis-services-connector-deep-dive/

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-ssas

[fsim]

Hi thanks for the answer.Unfortunately you are talking about PB services, I'm using power bi desktop and, as far as I know, it's not using the Data gateway.

thanks anyway

fred