Having Power Bi Dataset as External Source for Excel Data Model

[Anonymous]

Hello everyone,

I’ve a question please, I know that Analysis Services in Excel allows to link to Power Bi dataset thru a connection that could be loaded in pivot table, but actually it is a pivot table, not a power pivot, so, there is no possibility to create measure in the Excel file accordingly, and the other thing that the pivot table itself is limited as the calculated fields option is getting disabled as well, so, it is really limited pivot table here.

My question is, is there a way to link the Excel file to the external source of Power Bi dataset thru a data model?

[AlexisOlson]

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.

[BA_Pete]

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 

[KNP · Accepted solution]

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.

[BA_Pete]

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

[AlexisOlson]

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

[BA_Pete]

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.

@AlexisOlson , @KNP 

Are either of you guys able to verify/validate this please?

Pete

[Anonymous]

But the thing, when you will do this, it will get it for you as a connection, but not as a data model, right?

[BA_Pete]

@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