Connect Excel Pivot to a Power BI Desktop
With DAX Studio you can write DAX Querry against a SSAS Tabular, a Power Pivot Model or an instance of Power BI Desktop. Some time ago I opened DAX Studio for doing some querying in DAX against a Power BI Desktop instance.
Connect to a Power BI Desktop instance with DAX Studio
After a while the connection was disconnected, maybe a timeout. When I tried to refresh the connection, DAX Studio showed me a localhost adress to an Tabular Server instance.
Reconnect to Power BI Desktop instance
I tried this connection and realized that DAX Studio had reconnected to my Power BI Desktop Tabular instance. I had always asked myself how it is possible that DAX Studio can connect to the Power BI Desktop data model. With this little clue I started to understand what the Power BI Desktop actually is. A kind of mini SSAS server running inside this tiny program. AMAZING! For some IT-Pros this might be nothing new, but for me who is working with Power BI every day it opened many new ways for analysis. For example, I sometimes wished that Excel Power BI had the same improved Features that the Power BI Team is bringing to Power BI Desktop every week.
I first tested if it is possible to connect with Excel to my local Power BI Desktop SSAS instance. I only have the German version of MS Excel, sorry for that (go to Data/From Other Sources and enter the server name (2) and create a Pivot Chart or whatever you want).
Connect with Pivot to Power BI Desktop
It actually worked out of the box. I got access to the data model and could use it, as if a full SSAS Server is running in the Background.
Pivot on Power BI Desktop data model
For most of the Power BI users this is not relevant, because they can also use the native Excel Power BI plugins to create a data model with Power Query and Power Pivot. But I just want to explain an interesting, maybe strange use case.
Now I can use Power BI Desktop as mini tabular prototyping server. The SSAS Tabular is of course the better choice for a big environment, but SSAS Tabular does not have Power Query and I know no better tool for fast data prototyping than Power Query. Excel Power BI could be used instead, but Excel Power BI is not that stable like as Power BI Desktop, especially for complex models. Also some of the newer comfort functions are missing, even in Excel 2016. And I can use different front ends for browsing my Power BI Desktop model, for example Excel, Reporting Services SQL Server Management Studio. This scenario is only suitable for prototyping and testing because there is no way to integrate a user authentication.
Connect with SQL Server Management Studio to Power BI Desktop
I tried to dig a little bit deeper, because there is one problem with every new instance: Power BI Desktop gets a new port. So you have to change the connection string inside your external tool if you have to restart the Power BI Desktop instance. The used port can be seen with TCPView. TCPView is a little Microsoft Tool which shows all open ports in and outgoing traffic on your network card. Here you can see the port that is used by my Power Bi Desktop instance.
The second thing I found out is that not the PBIDesktop.exe is listening for incoming traffic, but the msmdsrv.exe, which is also used by SQL Server SSAS. But I found no configuration settings like it is possible with the SQL Server. At the moment I don’t know how to go further, if there is anything new I will give you an update.
Have fun with your own mini tabular model server.