Matt Allington on 10 Nov 2016 08:25:30
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.
Administrator on 17 Jun 2021 00:22:35
With the External Tools integration, users can now connect various tools to a local Power BI desktop instance including Excel.
- Comments (20)
RE: Use Power BI Desktop as a Local Server on a PC
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
https://datamonkeysite.com/2017/01/01/how-to-use-excel-as-a-front-end-to-power-bi-desktop/
RE: Use Power BI Desktop as a Local Server on a PC
'@Mimoune
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
RE: Use Power BI Desktop as a Local Server on a PC
'@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
Sam
PS: I made some improvements to your file - I will post it on your blog in a couple of days
RE: Use Power BI Desktop as a Local Server on a PC
'@SAM
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.
RE: Use Power BI Desktop as a Local Server on a PC
'@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.
https://goo.gl/4rUh8i
RE: Use Power BI Desktop as a Local Server on a PC
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.
RE: Use Power BI Desktop as a Local Server on a PC
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 :)
RE: Use Power BI Desktop as a Local Server on a PC
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
RE: Use Power BI Desktop as a Local Server on a PC
'@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
RE: Use Power BI Desktop as a Local Server on a PC
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