Power BI · Power BI Desktop

Analysing Power BI DMV Queries In Power BI Desktop

Experienced Analysis Services and Power Pivot developers will know all the interesting things you can learn from DMV queries. For example, in this blog post Kasper de Jonge shows how to use a DMV to find out which columns are using the most memory in a Power Pivot model; here’s an older post from me on using DMVs to show the dependencies between DAX calculations. There’s plenty of other interesting stuff about how data is stored and so on that you can find with a bit of digging, and there’s some reasonably up-to-date documentation on DMVs for SSAS here.

However, running DMV queries against a Power BI Desktop model (which of course runs a local version of the same engine that powers Analysis Services Tabular and Power Pivot) and more importantly doing something useful with the information they return, isn’t straightforward. You can run DMV queries from DAX Studio but that will only give you the table of data returned; you need to copy and paste that data out to another tool to be able to analyse this data. Instead it’s possible to use Power BI Desktop’s own functionality for connecting to Analysis Services to connect to its own local data model and run DMV queries.

If you’re connecting to an instance of Analysis Services in Power BI Desktop you need a server name and a database name and the same goes when you’re connecting Power BI Desktop to itself. This episode of Adam Saxton’s excellent Guy In A Cube YouTube show details how to connect SQL Server Profiler to Power BI Desktop in order to run a trace, and the method he uses to find the connection details also works for our purpose here; it’s definitely worth a watch because it goes into a lot of detail. However it’s much easier to get the connection details from DAX Studio using the following steps (thanks to Darren Gosbell for pointing this out):

  1. Open Power BI Desktop and load data as normal.
  2. Open DAX Studio and choose the Power BI Designer data source option:image
  3. Look in the bottom right-hand corner of the screen and you’ll see the server name, including the all-important port number, that you have connected to. In this case it’s
  4. Run the following DMV query in a DAX query window. This will give you the nasty GUID that is the name of the only database in the Power BI data model:

    [sourcecode language=”text” padlinenumbers=”true”]


  5. Now go back to Power BI Desktop, click the Get Data button and choose Analysis Services. In the connection dialog enter just the server name and port number and the database name found in the previous steps, as well as your DMV query:


Once you have done this, you can load the results of your DMV query into the data model and analyse the results just like any other data. Here’s an M query using the DMV from the blog post by Kasper referenced earlier that looks at the memory used by just one table in the model:

[sourcecode language=”text”]
Source = AnalysisServices.Database(
[Query="SELECT dimension_name, attribute_name, DataType,
(dictionary_size/1024) AS dictionary_size
#"Filtered Rows" = Table.SelectRows(Source,
each ([dimension_name] = "FactInternetSales"))
#"Filtered Rows"

And here’s a bar chart built from that query showing clearly that the SalesOrderNumber column in the FactInternetSales table is very expensive:

Before you get too excited about this, there are two major problems you need to be aware of when using this technique:

  1. The port number used to connect to the local data model and the database name will change every time you open the Power BI Desktop file, so you will have to edit the connection information manually if you want to refresh the data after reopening.
  2. You won’t be able to make this connection refresh once you have published the file to PowerBI.com – so this will only work on the desktop.

That said, I think this is still very useful for development purposes. At some point I expect we’ll probably get a range of management reports in PowerBI.com that show similar data on published reports – just like we used to have in the old Office 365 Power BI.

11 thoughts on “Analysing Power BI DMV Queries In Power BI Desktop

  1. Great article! I am curious if you are aware of anyone that has written an explanation of how to do the following. We largely base our pbi datasets on sql sources and have written custom sql for most of them. I need a report to show all the datasets’ sql queries. We have a few different uses for this information. I know using the Partitions DMV I can return this information but I haven’t found a case where anyone is scraping ALL published pbi datasets and returning all partition definitions. Is there an easier way to do this that I am not familiar with?

  2. Been a while since this article – Just wondering I want, if I wanted to connect to a power bi end point in power bi but I dont want to connect to the data, but to the meta data ie the dmv views. Do you know if it is possible to do this? and if so how do i do that. Which source do I use and the step to extract the metadata. A Bit stuck! thanks.

  3. Hello, people!

    Say I have 4 tables: A, B, C, D.

    Table A has a relationship 1 to many to table B.
    Table B has a relationship 1 to many to Table C.
    Table C has a relationship 1 to many to table D.
    There isn’t any direct link / relationship between table A and table C, or between B and D or between A and D.

    Is there an dmv command or a method of finding the ‘path’ from table A to table D ?

    Like for instance I have a Tabular Model which consists of 300 Tables and I need to know the path between Table A and Table D…
    I know that I can get it manually in Power BI or Visual Studio dragging all the 300 tables into a canvas and exploring their relationships by eyes… But its very unproductive and finally there can be the case that these two tables A and D have no connection to each other …

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.