Photo by Josie Lopez on Unsplash
Dynamic Management Views (DMVs)
Analysis Services Dynamic Management Views (DMVs)
Analysis Services Dynamic Management Views (DMVs) are queries that return information about model objects, server operations, and server health. The query, based on SQL, is an interface to schema rowsets. Schema rowsets are predescribed tables that contain information about Analysis Services objects and server state, including database schema, active sessions, connections, commands, and jobs that are executing on the server.
For Power BI Premium datasets, DMVs for querying through the XMLA endpoint are limited to those that require database admin permissions. Some DMVs are not supported because they require Analysis Services server admin permissions.
DMV queries are an alternative to running XML/A Discover commands. For most administrators, writing a DMV query is simpler because the syntax is based on SQL. In addition, the result is returned in a table format that is easier to read and copy.
DMV queries return information about server and object state at the time the query is run. To monitor operations in real-time, use tracing instead. To learn more about real-time monitoring using traces, see Use SQL Server Profiler to Monitor Analysis Services.
Query syntax
The query engine for DMVs is the Data Mining parser. The DMV query syntax is based on the SELECT (DMX) statement. Although DMV query syntax is based on a SQL SELECT statement, it does not support the full syntax of a SELECT statement. Notably, JOIN, GROUP BY, LIKE, CAST, and CONVERT are not supported.
Tools and permissions
DAXStudio
Go to the DMV tab in DAX Studio
To run a DMV query from SQL Server Management Studio
Connect to the server and model object you want to query.
Right-click the server or database object > New Query > MDX.
Type your query, and then click Execute, or press F5.
Schema rowsets
Not all schema rowsets have a DMV interface. To return a list of all the schema rowsets that can be queried using DMV, run the following query.
SELECT * FROM $System.DBSchema_Tables
WHERE TABLE_TYPE = 'SCHEMA'
ORDER BY TABLE_NAME ASC
If a DMV is not available for a given rowset, the server returns error: The <schemarowset> request type was not recognized by the server. All other errors indicate problems with the syntax.
Schema rowsets are described in two SQL Server Analysis Services protocols:
[MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol — Describes schema rowsets for tabular models at the 1200 and higher compatibility levels.
[MS-SSAS]: SQL Server Analysis Services Protocol — Describes schema rowsets for multidimensional models and tabular models at the 1100 and 1103 compatibility levels.
Examples and scenarios
A DMV query can help you answer questions about active sessions and connections, and which objects are consuming the most CPU or memory at a specific point in time. For example:
— This query reports on object activity since the service last started.
Select * from $SYSTEM.discover_object_activity— This query reports on memory consumption by object.
Select * from $SYSTEM.discover_object_memory_usage— This query reports on active sessions, including session user and duration.
Select * from $SYSTEM.discover_sessions— This query returns a snapshot of the locks used at a specific point in time.
select* from $SYSTEM.discover_locks— Query to retrieve all tables in a model
select* from $SYSTEM.discover_object_memory_usage— Query to get all columns (note the SortByColumnId column)
select* From $SYSTEM.TMSCHEMA_COLUMNS— Query to get all calculated columns
select* From $SYSTEM.TMSCHEMA_COLUMNS Where [Type] = 2
— Query to get all measures
select * from $SYSTEM.TMSCHEMA_MEASURES
— Query to get all dependencies
select * from $SYSTEM.discover_calc_dependency
— Get unique row counts for all tables and columns
select * from $SYSTEM.DISCOVER_STORAGE_TABLES order by rows_count desc
— Query to get all the roles, associated permissions and role memberships defined in the model
select * from $SYSTEM.TMSCHEMA_Roles
select * from $SYSTEM.TMSCHEMA_TABLE_PERMISSIONS
select * from $SYSTEM.TMSCHEMA_Role_Memberships
— Query to get all the KPIs defined in the model
select * from $SYSTEM.TMSCHEMA_KPIS
— Query to get session information
select * from $SYSTEM.DISCOVER_SESSIONS
— Query to get all relationships
select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS
— Queries to get hierarchy information
select * from $SYSTEM.TMSCHEMA_ATTRIBUTE_HIERARCHIES
select * from $SYSTEM.TMSCHEMA_ATTRIBUTE_HIERARCHY_STORAGES
— Query to get information about each model:
select * from $SYSTEM.TMSCHEMA_MODEL
— Query to get information about each partition:
select * from $SYSTEM.TMSCHEMA_PARTITIONS
— Query to get perspective information:
select * from $SYSTEM.TMSCHEMA_PERSPECTIVES
— Query to get catalog information (especially compatibility level):
select * from $SYSTEM.DBSCHEMA_CATALOGS