SQL Server Analysis Services does not work in Power BI, but can access in Excel

[jhalverson]

Hi all,

I am trying to analyze some data from the Analysis Services (the one with a cube) in Power BI. When I open up Analysis Services from Get Data in BI, I get this screen:

I punch in my server and database and just get an unable to connect error stating that the connection either timed out or the server is incorrect.

In Excel if I go to 'data' > 'get data' > 'from database' > 'from analysis services' I get a whole different screen:

From here I put in the same server name along with my credentials and it works just fine. I have the excel file stored on the exact same filepath as my pbix file if that matters. I also noticed in excel you can navigate to 'data' > 'get data' > 'from database' > 'from SQL server analysis services database (import)' which will bring up the same popup as the one in power bi. It looks like the 'from analysis services' in excel is an SQL cube or tabular model while the 'from SQL server analysis services database (import)' is a database. It also looks like Power BI only has the latter. Are these two different things? Can you get just the cube/tabular in power bi desktop? Is this a question for the database admin at my company?

I appreciate the help!

[Anonymous]

Hi @jhalverson ,

I can't reproduce your problem. The version of my Power BI Desktop is 2.108.997.0 64-bit (August 2022). What's the version of your Power BI Desktop? Please try to update to latest version and check if you can connect to SQL Server Analysis Services successfully.

Best Regards

[jhalverson]

I have the 2.108.825.0 64-bit (August 2022) version if that helps. I actually discovered that with the source that works in excel you can produce a .ocd (Microsoft Office Data Connection) file. I guess apparetly BI does not allow for that. Here is the idea help on power bi ideas

https://ideas.powerbi.com/ideas/idea/?ideaid=45afc361-3373-4c12-afb6-e22862044501

Here is what the connection string in excel looks like, but no idea how to use it in power bi, or if that is even possible:

Provider=MSOLAP.8;Persist Security Info=True;User ID=firstname.lastname@mycompany.com;Initial Catalog=CallCenter;Data Source=database.mycompany.com;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2</odc:ConnectionString>

[bcdobbs]

Have you tried

Get Data > More > Database >
SQL Server Analysis Services database

rather than the import version?

[amitchandak]

@jhalverson , which version of power bi you are using? in case you are using August 2022, refer few users have faced connectivity issues -https://community.powerbi.com/t5/Issues/idb-p/Issues

In case you have connected before, Open datasource setting and clear permissions and try again

[jhalverson]

Have not been able to connect before in Power BI. I do have August 2022 so maybe that is it, but my guess is that the 'from SQL server analysis services database (import)' is funamentally different than 'from analysis services' in excel. Power BI seems to only use the former