Forum Discussion

jhalverson's avatar
jhalverson
Icon for Advocate I rankAdvocate I
4 years ago

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

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!

 

 

 

 

Need Help

5 Replies

    • jhalverson's avatar
      jhalverson
      Icon for Advocate I rankAdvocate I
      4 years ago

      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

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Have you tried

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

    rather than the import version?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      jhalverson
      Icon for Advocate I rankAdvocate I
      4 years ago

      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 [email protected];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>