How to use Dynamic Management Views against on your Desktop PowerBI reports

Power BI contains a local instance of Analysis Services tabular model. By querying Dynamic Management Views (DMVs) query against PowerBI desktop, we can get metadata information about your PowerBI model.

Here are the steps to do so:

Step 1: Open your Power BI report

Step 2: Find your Power BI Analysis Model Instance Port ID.

There are two ways to do that:

Option 1: Open up DAXStudio (a great free tool to help you develop DAX). And then connect to the Power BI report you opened.


Then, Find the local Analysis Service instance address of this Power BI report on the right bottom of the DAX studio window


Option 2: Fine the Power BI temp directory
C:\Users\username\AppData\Local\Microsoft\Power BI Desktop SSRS\AnalysisServicesWorkspaces\…\Data

PowerBI Port


Step 3. Open SQL Server Management Studio. And connect to the local instance of Analysis

Get DAX 3


Step 4: Create an new query against the only Database under the local instance

Get DAX 4

Step 5: In the query window, you can then run the Dynamic Management Views (DMVs) to Monitor your PowerBI local instance.

The ones I use often including the following:

  • The DMV provide the DAX query behind the report:
Select * from $System.discover_sessions
  • This DMV Provide you all the fields in your model
Select  * from $system.MDSchema_hierarchies
  • This DMV Provide you all the measures in your model
Select * from $System.MDSCHEMA_MEASURES

Find relationships:

Select [ID], [ModelID], [IsActive], [Type], [CrossfilteringBehavior], [FromTableID], [FromColumnID], [FromCardinality], [ToTableID], [ToColumnID], [ToCardinality], [ModifiedTime]
Select [ID], [ModelID], [Name]from $SYSTEM.TMSCHEMA_TABLES
Select [ID], [TableID], [ExplicitName] from $SYSTEM.TMSCHEMA_COLUMNS

For More DMV, there is a good post:


If you do not have SSMS, you can use Power Query in Excel or Power BI to query those DMVs, here is an sample M query.

    Source = AnalysisServices.Database(TabularInstanceName, TabularDBName, [Query=”Select [ID], [TableID], [Name], [Description], [DataSourceID], [QueryDefinition], [Type], [Mode], ModifiedTime from $SYSTEM.TMSCHEMA_PARTITIONS”]),
    #”Renamed Columns” = Table.RenameColumns(Source,{{“ID”, “ID”}, {“DataSourceID”, “Data Source ID”}, {“QueryDefinition”, “Query Definition”}, {“ModifiedTime”, “Modified Time”}})
    #”Renamed Columns”


Your friend, Annie



2 thoughts on “How to use Dynamic Management Views against on your Desktop PowerBI reports

Add yours

  1. Good post Annie! Take into account that Power Query is case sensitive:

    Source = AnalysisServices.Database("DESKTOP-0IN1TQ2\MSSQLSERVER2019", "AdventureWorksDW2017", [Query="SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES"])


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

Up ↑

%d bloggers like this: