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

[Anonymous]

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

[ovonel]

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

[leonardmurphy]

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.

[GilbertQ]

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?

[Anonymous]

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

[v-shex-msft]

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

[v-shex-msft]

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

[Anonymous]

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