Analyze in Excel Error

[AUaero]

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

[venal]

@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]

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 · Accepted solution]

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.

[AUaero]

j0hnnyw1nter that absolutely worked.  I probably wouldn't have stumbled upon that on my own, so thanks!

This sounds like a bug to me.

[Anonymous]

@AUaero glad it worked out.

Yeah, should probably flag it up as Microsoft