Forum Discussion

AUaero's avatar
AUaero
Responsive Resident
6 years ago
Solved

Analyze in Excel Error

Hi,


I've got a Power BI report that is fed by a live connection to an on-premise SSAS tabular model via the enterprise data gateway.  When I click the "Analyze in Excel" button for this report and open the downloaded ODC file, I get an error stating "Unable to obtain list of tables from the data source".

 

I am an adminstrator of the Power BI tenant and I am an administrator on the analysis services server.  I know all of the permissions are correct.  

 

According the MS doc for troubleshooting analyze in Excel, I should be able to access on premise Analysis Services as long as I am on the same domain as the AS server and my account has access to the AS server (both are true in my case).

https://docs.microsoft.com/en-us/power-bi/desktop-troubleshooting-analyze-in-excel 

 

Any ideas?

 

Thanks,

Clayton

 

 

 

 

 

 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hey AUaero ,

     

    I posted that original thread. Nobody was forthcoming with a useful answer, but I did get to a solution.

     

    Wait for it... it's a bit of a crazy fix.

     

    So, I found that it depends how you create your Analysis Services connection when you first create your Power BI report.

     

    If, when you use the Get Data wizard, you specify the database in the first dialogue screen, then the report works fine when you build it, but once published the Analyze in Excel gets the error you've described.

     

    If however, rather than specify the database, you click through the dialogue screen and select the database, and then the model you wish to connect to, the PBI report seems to understand the metadata needed for the Analyze in Excel capability to work.

     

    So... don't do this:

     

    Do, do this: 

     

     

    Let me know if this helps, or whether this is just some quirk that seems to have helped us.

5 Replies

  • venal's avatar
    venal
    Memorable Member
    6 years ago

    AUaero 

    Can you please refer the link.

    If you have any queries, please let us know.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more
    If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

    • AUaero's avatar
      AUaero
      Responsive Resident
      6 years ago

      Thanks, venal.  I had already reviewed that thread and didn't find a solution there.  My account has full privileges on the SSAS server, so I don't believe it is an issue where the user isn't privileged to view the SSAS model.  I also checked to ensure that the Analyze in Excel feature was not blocked administratively (it is not).

       

      I can use Excel to successfully connect to the SSAS model in question.  It's only when I try to access the ODC file generated by Power BI that I get the error message.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hey AUaero ,

         

        I posted that original thread. Nobody was forthcoming with a useful answer, but I did get to a solution.

         

        Wait for it... it's a bit of a crazy fix.

         

        So, I found that it depends how you create your Analysis Services connection when you first create your Power BI report.

         

        If, when you use the Get Data wizard, you specify the database in the first dialogue screen, then the report works fine when you build it, but once published the Analyze in Excel gets the error you've described.

         

        If however, rather than specify the database, you click through the dialogue screen and select the database, and then the model you wish to connect to, the PBI report seems to understand the metadata needed for the Analyze in Excel capability to work.

         

        So... don't do this:

         

        Do, do this: 

         

         

        Let me know if this helps, or whether this is just some quirk that seems to have helped us.