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?
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.
Based on research, your issue seems related to permissions, please take a look at following link about similar issue.
In addition, is there any change on your datasource or has issues to use local device to connect ssas data source?
I think your admin is block 'analyze in excel' feature works on AS datasource, please refer to below link to know more about this:
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.
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.