Dynamic Management Views (DMVs)

Dynamic Management Views (DMVs) are special system views that expose internal server state for monitoring and troubleshooting. In the context of Power BI and Analysis Services, they are a powerful tool for extracting model metadata — tables, columns, measures, relationships, hierarchies, and more — that is often hidden from the graphical interface.

SQL Server vs. Analysis Services DMVs

• SQL Server DMVs — support standard JOIN syntax; can be queried directly in SSMS with familiar T-SQL
• Analysis Services DMVs — have restricted syntax; joins must be performed in application code by running separate queries and combining results

For Power BI models, you are working with Analysis Services DMVs.

What You Can Query

DMVs retrieve metadata about:

• Tables and columns
• Calculated columns and measures
• Relationships and their cardinality
• Hierarchies
• Storage partitions
• Roles and permissions
• KPIs
• Dependencies between measures
• Active sessions and queries

This information is invaluable for generating model documentation, auditing models before deployment, or understanding the impact of changes.

Querying in DAX Studio

DAX Studio is the recommended tool for querying Power BI DMVs.

Steps:

1. Connect to your Power BI Desktop model or Power BI Service dataset
2. Click the DMV tab to browse available views
3. Execute your query
4. Review results and export if needed

Example — list all tables:

SELECT * FROM $SYSTEM.TMSCHEMA_TABLES

Example — list all measures:

SELECT [TableID], [Name], [Expression], [Description]
FROM $SYSTEM.TMSCHEMA_MEASURES

Example — list all relationships:

SELECT [FromTableID], [FromColumnID], [ToTableID], [ToColumnID], [IsActive]
FROM $SYSTEM.TMSCHEMA_RELATIONSHIPS

Example — measure dependencies:

SELECT [OBJECT], [OBJECT_TYPE], [REFERENCED_OBJECT], [REFERENCED_OBJECT_TYPE]
FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY
WHERE [OBJECT_TYPE] = 'MEASURE'

Querying in SSMS

For more advanced queries that require joining across DMV results, SSMS with ad-hoc distributed queries enabled is the better choice.

Enable ad-hoc distributed queries:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Example — join table and column information via OPENROWSET:

SELECT t.[Name] AS TableName, c.[ExplicitName] AS ColumnName, c.[DataType]
FROM OPENROWSET(
    'MSOLAP',
    'Data Source=localhost\MSSQL;Initial Catalog=ModelName;',
    '$SYSTEM.TMSCHEMA_TABLES'
) AS t
INNER JOIN OPENROWSET(
    'MSOLAP',
    'Data Source=localhost\MSSQL;Initial Catalog=ModelName;',
    '$SYSTEM.TMSCHEMA_COLUMNS'
) AS c ON t.[ID] = c.[TableID]

Practical Uses

• Documentation generation — export all tables, columns, measures, and descriptions to Excel or a wiki
• Pre-deployment audits — verify model structure before publishing to Power BI Service
• Impact analysis — find all measures that reference a specific column before renaming it
• Performance investigation — query partition sizes and storage modes to identify optimisation opportunities
• Role review — list all RLS roles and their filter expressions

DMVs complement the Power BI graphical interface by surfacing information that the UI either hides or makes difficult to export in bulk. For any serious model documentation or governance workflow, they are an essential tool.