Use Power BI Desktop as a Local Server on a PC
It is possible to open Power BI Desktop, detect the port number, open Excel, connect to the running local instance of Power BI Desktop as a local SSAS instance and then build a pivot table over the local server. You can do this using DAX Studio.
This is very useful and as more people build Power BI Desktop files as the main data model, this will become more important. There is an "Analyze in Excel" feature in the Power BI Service and I would like to see the same for Power BI Desktop.
As a suggested approach to a solution, you could enhance the Power BI Ribbon in Excel. There is already a "Connect to Data" button that connects to the service. You could enhance this to include a button that connects to a local instance of Power BI Desktop running on the PC.
I would be tempted to change the title to make it clearer for other voters - this is about connecting Excel to a Power BI Desktop file. - there are another 20 votes here:
This will really aid in reverse engineering and cleaning up reports that can be optimised
Wouter Magré commented
Please add this to Excel
I would like to see this happen!
Would love this feature
Ivan Bondarenko commented
I have many scenarios where I'd use this.
Khoa Huynh commented
Yes, wonderful feature, please apply.
Abbas Azam commented
I would love such a feature!
I'm working in the bundu and often don't have internet access.
At the moment I'm building the data models twice - once in PowerBI once in Excel 2016
For anyone interested in this scenario, I used Matt's approach with another one using PowerQuery, and leveraging Excel cube formula & pivot cache to create a an excel template that can be shared without the data model
In many Business scenarios - Access is used to
1 Combine data from various sources (Excel / CSV / other Server based DB's)
2. Build Queries - (Joins/Unions/Calc Fields etc)
3. Excel then connects to the Queries and builds Pivot Reports
For the above scenario -a PBI Desktop file used as a Analysis sever replaces Access completely and a Excel file connected to it is much more responsive and has an incredibly small file size
You can do Data Entry in a Power Bi Desktop file - You can create a table and type data in to in
So I see lot of overlap between the two products
Power Bi desktop can not be used for data entry, it is not an RDBMS, so i don't see any overlap between the two products.
@Matt - What Kasper meant was the Power BI team (especially the PQ team ) are very Un-Microsoftish - so MS must be treating them as "Third Party" :-)
I think the real problem is Access will be dead as a product once this is officially supported - so we need to wait till MS decides what to do with their Access team
PS: I made some improvements to your file - I will post it on your blog in a couple of days
Matt Allington commented
@kasper, there is no malicious intent here. I read the ELA before posting and my clear understanding of "Third Party" is a party other than the 2 main parties to a contract. So Excel is not an Application owned by a Third Party, but an Application owned by the First Party. I am not a lawyer, but that is my understanding.
Kasper de Jonge commented
Just fyi, doing this with Power BI desktop today is a clear violation of the License you agree to when installing Power BI Desktop. It states: "You may not allow a third party application to connect and read data from the software without Microsoft’s prior written approval;".
So until they remove this from the license it is illegal to use the AS engine outside of Power BI desktop.
Dan English, my "feeling" is microsoft will not mind, as long as people will not use it as a server in a network, after all Tableau do connect to PowerPivot model :)
Dan English commented
The ability to leverage Power BI Desktop as a server appears to be a definite possibility, not sure what the licensing implication is though... https://github.com/akavalar/SSAS-on-a-shoestring
Matt Allington commented
@jerome yes it is possible - this is exactly what the first paragraph states in my idea, but it is not scalable and it is labour intensive.
I developed a tool to make it automatic http://exceleratorbi.com.au/excel-workbook-connection-local-power-bi-desktop/
But I am asking Microsoft to provide a supported tool
Its possible. you have to identify the TCP port used by the local instance of PowerBI and connect to it through Excel.
I saw a blog post about this, I juist test the SSMS connection, so Excel will be able to do it too