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 .odc
connection 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.