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.
Hi Anonymous ,
If you have your PBI dataset in a premium capacity workspace (WS), the following *might* work for you, but I've honestly not tested it myself I'm afraid:
1) Go to the Premium tab in workspace settings for the WS that holds your dataset and select the type of premium licence you have. This will display your Workspace Connection string, which you need to copy:
2) In Excel, go to the Data tab > Get Data > From Database > From Analysis Services. Paste your Workspace Connection string into the server name input:
3) Select the correct credential method for your setup (probably Windows Auth).
I get a "Power BI not licensed" exception at this point as I don't currently have a Premium/PPU licence, but it should connect as a data model DB at this point if you do.
Are either of you guys able to verify/validate this please?
Pete
But the thing, when you will do this, it will get it for you as a connection, but not as a data model, right?
Anonymous ,
I don't know I'm afraid. As I said, I'm not able to fully test it currently, sorry.
Based on the fact that Analysis Services is part of the Database source group, my assumption is that it would be connected as an open tabular model and loaded into the Excel file data model as such.
If this method doesn't work, then I don't see any other way to achieve what you're asking for, other than making connections to each of your dataset's tables again and rebuilding the dataset model in the Excel data model from scratch.
Pete