cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Analyze in Excel - 'unable to obtain list of tables from the data source'

Hi all,

 

I have a dataset published in the PowerBI service which is hooked up to an Azure Analysis Services cube via DirectQuery. If I try and use the Analyze In Excel option, it downloads the .odc file but then once I'm through the authentication page, it errors out with 

 'unable to obtain list of tables from the data source'

 

Anyone come across this before?

 

I have a different data set in a different workgroup that is the same set up. The only difference being that the Analysis Services cube for this was developed in VS2015 so is at 1200 compatability level.

 

The cube that's getting the error was built in VS2017 and is 1400 compatability level.

 

Anyone any clues? If there's a known issue with 1400 compatability level that isn't getting fixed anytime soon, then I guess I'll just have to re-engineer my cube, but if that's not the problem I don't want to waste time doing that.

 

Any advice anyone can offer would be much appreciated?

 

Thanks

J

7 REPLIES 7
ovonel
Post Patron
Post Patron

After days of suffering this last message was the fix, this is shocking!!

leonardmurphy
Skilled Sharer
Skilled Sharer

Old question, but I ran into this exact error ("Unable to obtain list of tables from the data source") when trying to Analyze in Excel from a Power BI report that connected to a model in AAS and it had nothing to do with permissions and was report specific.

 

To fix it, I opened the source pbix file in Power BI desktop and selected Transform Data > Data Source Settings. Both the Server and the Database name were filled in. I deleted the Database name and clicked ok. This caused the Navigator screen to pop up, showing me all the databases on the server (I only have one). However, rather than just selecting the Database name and clicking ok, I dropped one level lower and selected the model/perspective name instead. I then republished the report and had no issues analyzing in Excel.

 

My theory on what the issue is: the default perspective for a model is "model" - which contains everything. I had renamed that. Power BI handles the rename with no issues - if you only select the database name when you connect to the model, it defaults to the renamed perspective. But, it appears Excel defaults to the name "model" if a perspective isn't specified. If there is no perspective called model, it cannot load the list of tables - it doesn't recognize the rename. By connecting in Power BI to the specific perspective I wanted (rather than the database name), Excel knows what perspective to open and doesn't have to use a default that doesn't exist.

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
GilbertQ
Super User
Super User

Hi there

Could it be that the Firewall on the Azure Analysis Services is not configured to allow connections from your IP Address where you are using Excel?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Hi - thanks, but I have other datasets using the same Azure Analysis Services instance that work fine.

Hi @Anonymous,

 

Based on research, your issue seems related to permissions, please take a look at following link about similar issue.

Reference link:

data connection wizard cannot obtain a list of databases error with standart user rights

 

In addition, is there any change on your datasource or has issues to use local device to connect ssas data source?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

I think your admin is block 'analyze in excel' feature works on AS datasource, please refer to below link to know more about this:

Analyze in Excel

Administrators for Power BI tenants can use the Power BI Admin Portal to disable the use of Analyze in Excel with on-premises datasets housed in Analysis Services (AS) databases. When that option is disabled, Analyze in Excel is disabled for AS databases, but continues to be available for use with other datasets.

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi - thanks, but that's definitley not the problem as I can use the function on other datasets using the same datasource with no problem

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors