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

Finding Power BI Desktop local port using Windows Command Prompt (CMD)

• Run Windows Command Prompt as Administrator
• Copy, paste and run the following command:

TASKLIST /FI “imagename eq msmdsrv.exe” /FI “sessionname eq
console”

• You should see something like this

• What we need is the PID
• Now run the following command and put the PID number you got from previous command between quotation marks

netstat /ano | findstr “13944”

• The results should be something like the screenshot below which shows active connections, their local address which is local IP address followed by the port number. And this is what we are looking for.

Note: The column names for the result table respectively from left to right is:

Active Connection, Local Address, Foreign Address, State, PID

As I mentioned, Active Connection is the one we are interested in so that the number coming after colon is the port number.

Finding Power BI Desktop local port using DAX Studio

DAX Studio is an awesome tool to run DAX queries against SSAS Tabular Models and Power BI Desktop Models. The interesting point is that it’s free and you can download it from here.

• Install DAX Studio on your machine and run it
• Select “Power BI Designer” as data source then click “Connect”

• At the bottom of the programme window you can find the port number right after colon

Finding Power BI Desktop local port from Power BI Desktop temp directory

In real world you might NOT have access rights to open CMD command prompt when you work on your customers’ sites. You potentially don’t have the rights to install a new software like DAX Studio. But, you’ll always have access to your local files even when you work on site for a customer.

Whenever you run Power BI Desktop it creates a bunch of files and folders in a temp directory on your local disc. You’ll find lots of interesting information about a running instance of Power BI Desktop like SQL Server Profiler trace data files, local instance of Analysis Services log file and port number. For the purpose of this post we just need the port number, but, I encourage you to have a look at the other files in the Power BI Desktop temp directory. I bet you’ll find it informative. Whenever you run Power BI Desktop on your machine it opens a random port number. The port number is independent of the model so it doesn’t really matter if you haven’t connected to any data sources or if you haven’t open any saved Power BI Desktop (*.PBIX) files. That port number is stored in a text file named “msmdsrv.port.txt”. So the only thing we need is to browse the temp directory of Power BI Desktop and open the above text file.

Note: This is a temp directory  that is created whenever you run Power BI Desktop and is deleted when you close Power BI Desktop.

You can find Power BI Desktop temp folder here:

• Navigate to%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces

• There should be an “AnalysisServicesWorkspaceXXX” folder which XXX is a random number. Open that folder
• Open “Data” folder
• Find “msmdsrv.port.txt” in the folder and open it. It contains the random local SSAS instance port number used by Power BI Desktop

Connecting to Power BI Desktop Model from Excel

Now that we have the port number it is easy to connect to the model.

• Open Excel
• Click “From Other Sources” from “Data” tab from the ribbon
• Click “From Analysis Services”

• Enter the server name as “localhost:60575” where “60575” would be the port number you retrieved earlier then click Next

• Click Next one more time

• Click Finish

• Select the way you want to view data then click OK

• Now you can slice and dice your Power BI Desktop model data in Excel

Connecting to Power BI Desktop Model from SSMS

It’s really easy to connect to a Power BI Desktop model from SSMS after having the port number.

• Open SSMS
• Select “Analysis Services” for Server Type
• Enter the server name as “localhost:60575” which 60575 is the port number and click Connect

Now we are successfully connected to the Power BI Desktop Model. Expand the model elements and you’ll find bunch of interesting things different connections used in the model, tables and so on.

It’s interesting isn’t it?

The first thing that caught my eyes when I connected to a Power BI Desktop from SSMS was lots of extra LocalDate tables in the model. I reckon they are there for supporting automatically generated date hierarchy. I noticed that there is indeed a LocalDate table for each date column you have in your model. So if you have 10 columns of “Date” data type in your model, then you’ll have 10 LocalDate tables. Not too sure if it’s the most effective way to implement a date hierarchy, but, it is how it is!

Note: You can easily see how automatically generated date hierarchy works. You just need to have a date column in a table in your model. When you drag and drop the date column on to a visual, let’s say a table, you can see that date column will automatically generate a date hierarchy without having a real date dimension in your model.

Querying Power BI Desktop Model with DAX

You can run DAX queries against the model. In our sample I ran a simple DAX query to see the contents of the LocalDate tables.

• In SSMS, right click the database from Object Explorer
• Click “New Query” then click “MDX” (You’re right, there is no DAX here, but, we’ll write and execute DAX queries in an MDX query editor. Awesome!)

• • Now simply type the code below and press F5 to execute it and see the results:

       evaluate

              all (LocalDateTable_4535931b-fab6-4ea2-b2d7-0979ef27c9e5′)

Note: You obviously need to put your LocalDate table name in the “all()” function.

Running MDX expressions against Power BI Desktop Model

As you might know you can run MDX expressions against an SSAS Tabular Model database. Power BI Desktop Model is not an exception.

So far you got the idea how to connect to a Power BI Desktop Model from SSMS and query the tables with DAX. The same process applies for running MDX expressions.

The following MDX expression shows Total Internet Sales by Product for Calendar Year 2014 (I imported data from AdventureWorksDW2016CTP3 database into Power BI Desktop):

SELECT

  NON EMPTY {

    [Measures].[Total Sales]

  } ON COLUMNS,

  NON EMPTY {

    (

      [DimProduct].[EnglishProductName].[EnglishProductName].allmembers

    )

  } ON ROWS

FROM ( SELECT

       (

         {

           [DimDate].[CalendarYear].&[2014]

         }

       ) ON COLUMNS

     FROM [Model])

WHERE (

        [DimDate].[CalendarYear].&[2014]

      )

Browsing Power BI Desktop Model from SSMS

You can also browse the model from SSMS. To do so, just right click on the database from Object Explorer and select “Browse”.

Note: When browsing the model from SSMS, you might get nothing when you drag and drop a measure from a fact table into the grid. This is because you dragged an implicit measure rather than an explicit one. Generally speaking, an implicit measure is a type of measure you haven’t created. They are indeed numeric columns in your tables. But, an explicit measure is the measure you create using DAX functions. A best practice to create explicit measures and hide all implicit ones in the model. Check this out to learn more about measure types in DAX.

Browsing Power BI Desktop Model from DAX Studio

Browsing a Power BI Desktop model from DAX Studio is probably the easiest method in my experience. Not only can you browse the model from DAX Studio, but, also it is the best tool you can use for querying your Power BI Desktop or SSAS Tabular models. There are a bunch of facilities available in the tool for performance tuning and so forth.

• Open DAX Studio and connect to your Power BI Desktop model
• It immediately opens a query editor so that you can write your DAX
• Write some DAX codes and run it

There is a lot of interesting things about DAX Studio like the ability to see Query Plans, Server Timing and so on, so I encourage you to download and play with it. If you want to tune your DAX performance this is a must have tool.

Profiling Power BI Desktop Model using SQL Server Profiler

You can use SQL Server Query Profiler to capture query plans and use it for performance tuning.

• Open SQL Server Query Profiler
• Select Analysis Services as server type
• Enter “localhost:60575” as server name then click “Connect”

• Click “Events Selection” tab
• Tick “Show all events”

• Find and tick all of the following events from the list and deselect all other events, then untick “Show all events”

• Click Run
• Now go back to your Power BI Desktop and drag and drop a column to the canvas

• You’ll immediately see the traces in SQL Server Profiler

I’m not going to explain performance tuning and the reasons we selected those events in SQL Server Profiler it is out of scope of this post. If you’re interested in learning more about DAX performance tuning I encourage you to have a look at this amazing whitepaper from SQLBI.