Power BI Desktop is a fantastic report authoring tool. I have lots of experience working with Tableau as well and I can say, man, Power BI is growing very quickly. Lots of awesome ideas have been added to Power BI and a lot more is coming. But, It might be a question for some of you that is that possible to connect to a Power BI Desktop model from Excel, SQL Server Management Studio (SSMS) or SQL Server Profiler? The answer is yes, you can. But, how on earth someone should connect to a Power BI Desktop model from Excel, SSMS or SQL Server Profiler? Well, it could be useful for the following scenarios:
Connecting to the model using SQL Server Profiler for performance tuning, monitoring and so forth
Again, if you have some performance issues you might need to connect to the model from SSMS
You have a complex model and it’s hard for you understand it, but, you are a great Excel developer, so you can connect to Power BI Desktop model from Excel so you can use reach features available in Excel like named sets
Just for curiosity! You are curious about writing MDX codes over an existing model, you want to see how your model look like in Excel and so forth
In this article I show you how to connect to Power BI Desktop model regardless of any use case scenarios. So for whatever reason you’d like to connect to a Power BI Desktop model this post will help you achieve your goal.
How it works
Power BI Desktop uses xVelocity technology and loads data into memory. It uses a local instance of SQL Server Analysis Services (SSAS). It does the job by running msmdsrv.exe file which can be found in “bin” folder under your Power BI Desktop installation folder which is normally under you Program Files. The msmdsrv.exe is indeed the SSAS service file. So even if you haven’t installed SSAS on your machine Power BI Desktop runs msmdsrv.exe. When Power BI Desktop runs msmdsrv.exe it creates a local instance of SSAS. This local SSAS instance uses a random port number so it would be valid until Power BI Desktop is not closed or the msmdsrv.exe is not killed from Task Manager.
So, we have a local instance of SSAS using a random port number. Therefore, we should be able to connect to the instance from Excel, SSMS or SQL Server Profiler only if we know the port number.
Note: If you have installed an instance of SSAS on your machine you can find msmdsrv.exe under “\OLAP\bin” folder from SQL Server installation path:
%ProgramFiles%\Microsoft SQL Server\msasXX.INSTANCE_NAME\OLAP\bin
which XX is your version of SQL Server. So XX would be 10, for SQL Server 2008R2, could be 11 for SQL Server 2012 and so on. The difference between the local msmdsrv.exe file located in your Power BI Desktop\bin folder with the other one you can find under your SQL Server installation folder is that the one which Power BI Desktop runs is a console programme while the other one is a Windows service programme.
How to find Power BI Desktop local port?
There are various methods you can obtain the port number. In this post I explain three of them.
- Finding Power BI Desktop local port using Windows Command Prompt (CMD)
- Using DAX Studio
- Finding local port number from Power BI Desktop temp directory
Continue reading “Connect to Power BI Desktop Model from Excel and SSMS”