Forum Discussion
Having Power Bi Dataset as External Source for Excel Data Model
I followed your steps BA_Pete and connection works as expected as long as I select the 'From Analysis Services', not the one below.
Unfortunately, from then on, the experience is exactly the same as the 'normal' method of connecting to a Power BI dataset. i.e. no option to tick the 'Add to data model' and therefore nothing shows up in the Power Pivot tab.
So Anonymous, as AlexisOlson has pointed out, all modelling needs to be done in Power BI to ensure you have all the measures and columns required. Even then, it's still quite limiting if you want something other than Pivot Table/Chart, unless you want to use cube functions to build a table/report.
There are a bunch of random (badly written) ideas on the topic that you could vote on, but I couldn't find any that already had a lot of votes.
https://ideas.powerbi.com/ideas/idea/?ideaid=8c76088a-d468-41e3-a584-e3d535385994
Hopefully things change in the future.
Hey AlexisOlson ,
Were you able to try my DB connection process? I don't have premium ATM, didn't know if you do and could test my theory?
Pete
I tried and got authentication issues essentially saying it needs Microsoft account credentials rather than Windows credentials but I don't see how to switch to that.
FWIW, here's what the connection string looks like for the Power BI dataset connection that I showed:
Provider=MSOLAP.8;
Integrated Security=ClaimsToken;
Persist Security Info=True;
Initial Catalog=sobe_wowvirtualserver-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;
Data Source=pbiazure://api.powerbi.com;
MDX Compatibility=1;
Safety Options=2;
MDX Missing Member Mode=Error;
Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;
Update Isolation Level=2