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.
Microsoft has made it pretty easy to connect to a Power BI dataset from within Excel:
However, as you point out, this is just a connection to the model and doesn't load the model locally to Power Pivot so you cannot do additional modeling in Excel. If you need to create new calculated columns or measures, I think you'll have to do that in Power BI and republish the dataset.
Personally, I find modeling in Power BI more friendly than in Power Pivot so this limitation doesn't bother me at all but I can understand why this would be frustrating if you don't have permission to edit the Power BI dataset.
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 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.
Many thanks AlexisOlson , KNP for looking into this, much appreciated.
Anonymous, Sorry, it appears as though the correct setup of the PBI dataset is the way to go here. This would be my preference anyway to be honest. As AlexisOlson mentioned, I also find Power BI to be far more user-friendly when setting up data models/measures than Excel.
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