Connect to Power BI Desktop Model from Excel and SSMS

Power BI Desktop Excel SSMSPower 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.

Find msmdsrv.exe in Power BI Desktop Folder

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”

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

  • You should see something like this

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

  • 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”

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

  • 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.

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

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”

Finding Power BI Desktop local port using DAX Studio

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

Finding Power BI Desktop local port using DAX Studio

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

Power BI Desktop Temp Folder

  • 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

Finding Power BI Desktop Local Port Number in Temp Dir

Power BI Desktop Local Port Number

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”

Connecting to Power BI Desktop Model from Excel

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

Connecting to Power BI Desktop Model from Excel

  • Click Next one more time

Connecting to Power BI Desktop Model from Excel

  • Click Finish

Connecting to Power BI Desktop Model from Excel

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

Connecting to Power BI Desktop Model from Excel

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

Connecting to Power BI Desktop Model from 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

Connecting to Power BI Desktop Model from SSMS

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.

Connecting to Power BI Desktop Model from SSMS

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.

Power BI Desktop Generate Date Hierarchy

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!)

Querying Power BI Desktop Model with DAX

    • 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.

Querying Power BI Desktop Model with DAX

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]

      )

MDX and Power BI Desktop

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”.

Browsing Power BI Desktop Model from SSMS

Browsing Power BI Desktop Model from SSMS

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

Browsing Power BI Desktop Model from DAX Studio

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”

Profiling Power BI Desktop Model using SQL Server Profiler

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

Profiling Power BI Desktop Model using SQL Server Profiler

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

Profiling Power BI Desktop Model using SQL Server Profiler

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

Power BI Desktop

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

Profiling Power BI Desktop Model using 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.

25 thoughts on “Connect to Power BI Desktop Model from Excel and SSMS

  1. Hi Soheil,

    This is wonderful post. I was looking for it for long time.
    Finally I am able to connect to data model in Power Bi Desktop from Excel.

    Thanks a lot

    Jan

  2. A wonderful and very handy post. Thank you! Here is a batch script to automate the process of getting the port:

    “`
    @ECHO OFF & SETLOCAL
    FOR /F “tokens=2 delims=,” %%F IN (‘TASKLIST /NH /FI “IMAGENAME EQ msmdsrv.exe” /FI “sessionname eq console” /FO CSV’) DO (
    SET pid=%%F
    )
    FOR /F “tokens=2 delims=:” %%F IN (‘NETSTAT /ANO ^| FINDSTR %pid% ^| FINDSTR “127.0.0.1”‘) DO (
    SET ipport=%%F
    )
    FOR /F “tokens=1” %%F IN (“%ipport%”) DO (
    SET port=%%F
    )
    ECHO Local Power BI instance running on port:
    ECHO %port%
    “`

    1. Hi Zach,

      For someone a bit less techy, how do you deploy this script?

      I’m using SSIS to move data from Power BI to SQL tables and hoping to remove the step of updating the port number.

      Thanks
      Richard

    2. Thank you Zach for that script, worked perfectly! Note to less technical folks: you will have to change all the directional (left/open, right/close) double quotes to generic double quotes and single quotes to generic single quotes, but that is all. If you edit it in notepad and save it as .bat file like getPowerBI.bat, you can just execute that from the c prompt/command line by typing getPowerBI.bat and voila.

  3. Thank you so much Soheil Bakhshi!! Such a great write-up. Also thank you to Zach for the script to get the port. I tried to run the batch script by pasting the code into a text editor and saving as a batch file to my desktop. Then I double clicked on the file and it opened up a command prompt for a second, but then disappeared, and now I’m not sure what to do next. I’m really new at this. Can you tell me what I’m missing?

    1. Hi Taylor,
      Thanks for your comment.

      I’m not too sure why you want to store the port number in your local drive as:
      1- the port number is temporary stored in your local drive. You just need to open the file. Look at the “Finding Power BI Desktop local port from Power BI Desktop temp directory” section for more details. NOTE: If you’re using Power BI Desktop App from Microsoft Store then the local file stored in “%USERPROFILE%\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces\AnalysisServicesWorkspacesXXXXXXXXXX\Data
      2- the port number is a random port number which is only valid for the duration your Power BI file is open. So after you close the file, that port number is no longer valid.
      3- if you want to copy the port number from CMD (command shell) you press Ctrl+M, select the port number with your mouse then press Enter

      If none of the above make any sense then please explain what you actually want to achieve.

      Cheers.

  4. Thank you so much.

    Next step:

    Using the above batch example to automatically update/connect to a given power bi file (*.pbix)
    Beforr starting: Power bi must be running and corresponding pbix file must be opened, right?
    Only then I would be able to read the port and receiving data/connect to the data model via excel, right?

    If yes, I will give it a try to make a connection/updating the connection parameter automatically.

    Let’s see how this could works

    1. Hi Sebastian,

      Welcome to BIInsight.com.
      The answer of your questions is YES! You have to run Power BI Desktop first and then you’ll be able to read the port number.
      Update us with the results of your plan. Curious to see how it goes.

      Cheers.

  5. Thanks Soheil for sharing this trick. Unfortunately i’m unable to see any data under the database after i connect to my localhost with the correct port. I see empty connections, tables, and roles. And when i script it out in XMLA, i get nothing…
    Although when i open in MDX, i can see the cube measures and dims. The pbix is still running with import setting.

    1. Hi Kaushik,
      Welcome to BIInsight.com.
      Have you tried from SSMS or Excel?
      If you’re using SSMS I suggest you use the latest version.
      Saying that, this method has always worked for me for a long time with many different versions of SSMS.
      Can you provide more information please?
      Thanks

  6. Highly recommend process explorer to identify the msmdsrv.exe process and its port number. (shown on the TCP/IP tab).

    1. Hi David,

      Welcome to BIInsight.com.
      That’s a good suggestion indeed that may have usecases for people having access to Process Explorer or they car/are allowed to install. it.

      Cheers.

  7. This is really informative post, For I cannot find the Power BI desktop folser under Microsoft, is that available at some where else?

    1. Which edition of Windows are you using? This must work if you’re using Windows 10:
      %USERPROFILE%\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces\AnalysisServicesWorkspacesXXXXXXXXXX\Data
      Where the “XXXXXXXXXX” is a random number.
      Cheers.

  8. Hello,
    This is a nice post and i have been using this for quite a while… however recently numerical fields are not loading into the Values panel of the Pivot table.

    Do you know how to fix this?

    cheers

  9. Hi Sohail,
    Thanks for the writeup. But is it possible to do this from powerbi.com? I have premium capacity. My data gets batch processed and some measures and columns are created from Power BI side. I want to be able to connect to this Power BI service instance and be able to view it from SSAS side. How do I get about doing this?

  10. Hi Sohail,

    Is there a way to see the raw SQL that gets generated for steps in Power Query for flat files like csv, txt or for Excel files? I know Native Query is disabled for flat files. To optimize power queries, is there a alternative to see which steps are taking a lot of time and which execute quickly?

    1. Hi Sifar,

      Welcome to BIInsight.com.
      As you mentioned query folding only applies to certain data sources (mostly relational database systems like SQL Server, Oracle, OData, etc.).
      But regardless of query folding, you can always see what step is taking more time to process using the Diagnostics Tool as follows:

    2. In the Power Query Editor, from the Tools tab, click the Diagnostic Options button
    3. From the Diagnostics Level tick Aggregated and Detailed (we don’t need the additional diagnostics options, it’s up to you. You can keep either tick or untick them)
    4. Click OK
    5. Power BI Diagnostics Options

    6. Select the desired query from the Queries pane
    7. Click Start Diagnostics button from the Tools tab from the ribbon
    8. Start Diagnostics in Power BI

    9. Click the Home tab
    10. Click Refresh All
    11. Refreshing a Query in Power BI Desktop Power Query Editor

    12. Go back to the Tools tab and click Stop Diagnostic
    13. Stop Diagnostics in Power BI
      Click the Diagnostics_Aggregated query, you can find the steps timings under the Exclusive Duration column
      Reading Diagnostics Queries in Power Query Editor in Power BI Desktop
      You can find out more details on the Diagnostics_Details query.

      Hopefully, that helps.
      Cheerds.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.