The Power BI Service "Analyse in Excel" feature produces an .odc file that is exported, downloaded onto local machine, and when executed which opens Excel, creates Excel cube and Pivot Table using the Power BI data.
I have been trying to use the .odcconnection file information to put together a url that I can use in Power Query to connect to the Power BI Analysis Services database that hosts the report's data source.
This is connection string from .odc file:
<odc:ConnectionString>
Provider=MSOLAP.7;
Integrated Security=ClaimsToken;
Data Source=https://analysis.windows.net/powerbi/api;
;
Initial Catalog=37c4ee9e-6cf5-49d2-aaa7-e66e42646274;
Location=https://wabi-west-us-redirect.analysis.windows.net/xmla?vs=sobe_wowvirtualserver&db=37c4ee9e-6cf5-49d2-aaa7-e66e42646274;
MDX Compatibility= 1;
MDX Missing Member Mode= Error;
Safety Options= 2;
Update Isolation Level= 2
</odc:ConnectionString>
The .odc file also contains an url in the though this ends up in the Name field of the Excel Data Connection:
<title>https://analysis.windows.net/powerbi/api; 37c4619e-6cf5-49d2-aaa7-e66e42646274 Model
</title>
I am guessing that this is the AS database url from the .odc file:
https://wabi-west-us-redirect.analysis.windows.net/xmla?vs=sobe_wowvirtualserver&db=37c4ee9e-6cf5-49d2-aaa7-e66e42646274
So using that AS url I create Power Query SSAS connector like this:
= AnalysisServices.Databases("https://wabi-west-us-redirect.analysis.windows.net/xmla?vs=sobe_wowvirtualserver&db=37c4ee9e-6cf5-49d2-aaa7-e66e42646274")
I enter my Power BI Service username and password as Basic authentication with Private security status.
The Power Query Loads without error, runs for brief second, then shows error:
DataSource.Error: AnalysisServices: The connection either timed out or was lost.
Has anyone else done this? Is there more the AS database url than I have specified.
