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:
   
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 
   localhost:52981
   
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: 
   

   
   
   
   
   1
   
   
   SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS
   
   
   
   
   
   

   
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:

1

2

3

4

5

6

7

8

9

10

11


let

    Source = AnalysisServices.Database(

     "localhost:52981", 

     "1107a5df-3dd9-4c16-80b6-cf571c4e794f", 

     [Query="SELECT dimension_name, attribute_name, DataType,

                   (dictionary_size/1024) AS dictionary_size 

                   FROM $system.DISCOVER_STORAGE_TABLE_COLUMNS"]),

    #"Filtered Rows" = Table.SelectRows(Source, 

     each ([dimension_name] = "FactInternetSales"))

in

    #"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.