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

 

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

 

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

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]
from $SYSTEM.TMSCHEMA_RELATIONSHIPS

Select [ID], [ModelID], [Name]from $SYSTEM.TMSCHEMA_TABLES

Select [ID], [TableID], [ExplicitName] from $SYSTEM.TMSCHEMA_COLUMNS

For More DMV, there is a good post: https://datasavvy.me/2016/10/04/documenting-your-tabular-or-power-bi-model/

Bonus

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.

let

    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”}})

in

    #”Renamed Columns”

Thanks

Your friend, Annie