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.
23 Comments
- 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
kdejongeMicrosoft Employee
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.- 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
- 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 :)
- '@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
- '@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
- '@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
- '@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
- '@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.
- 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/
Recent ideas
Allow Power BI subscriptions to use a no-reply sender instead of the subscription owner's mailbox
Following the migration to the new email delivery platform, subscription emails now appear to come from the subscription owner. This generates large volumes of Out of Office replies, auto-replies, an...
