DataMartIn
  • Home
  • About
  • Services
    • App Development
    • Azure Administration
    • Business Intelligence
    • Office 365
  • Blog
  • Book A Consultation
We Know Data
  • Home
  • About
  • Services
    • App Development
    • Azure Administration
    • Business Intelligence
    • Office 365
  • Blog
  • Book A Consultation

What are the DMVs and how you can use them?

Home / Analysis Services / What are the DMVs and how you can use them?
What are the DMVs and how you can use them?

What are the DMVs and how you can use them?

By Nuric Ugarte
 Posted September 30, 2022
 In Analysis Services, Metadata, Power BI, SSAS, Tabular Models
What are the DMVs and how you can use them?2022-09-302022-09-30/wp-content/uploads/2022/08/datamartin.svgDataMartInhttps://datamartin.ca/wp-content/uploads/2022/09/post-image-1.jpg200px200px
0

The DMVs are queries that allow you to retrieve valuable metadata information about your data model, server operations, and server health.

What are the DMVs

The DMVs queries are an interface to schema rowsets based on SQL and use a SELECT statement and the $System schema with an XML/A schema rowset, however, they do not support the full syntax of a SELECT statement as: JOIN, GROUP BY, LIKE, CAST, and CONVERT.

Apply to: SQL Server Analysis Services, Azure Analysis Services, Power BI Desktop and Power BI Premium

Example of a DMV query:

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

WHERE OBJECT_TYPE = ‘MEASURE’

ORDER BY TABLE ASC

For more information visit the official documentation: https://docs.microsoft.com/en-us/analysis-services/instances/use-dynamic-management-views-dmvs-to-monitor-analysis-services?view=asallproducts-allversions

What are they for?

  • The DMVs provide information about the active sessions and connections, objects that consume the most CPU or memory at a specific point in time, such as:
DMVDescription
$system.discover_commandsProvides resource usage and activity information about the currently executing or last executed commands in the opened connections on the server.
$system.discover_sessionsThis query reports on active sessions, including session user and duration.
$system.discover_connectionsList the currently opened connections on the server.
$system.discover_memoryusageLists all memory consumption by object. Most information here: https://www.kasperonbi.com/new-ssas-memory-usage-report-using-power-bi/
  • To document your Power BI or SSAS data model, the main queries DMVs available for model documentation are:
    • TMSCHEMA_TABLES: List the Column objects in each table.
    • TMSCHEMA_COLUMNS: List the Columns objects in each table.
    • TMSCHEMA_MEASURES: List the Measure objects in each table.
    • TMSCHEMA_RELATIONSHIPS: List the Relationship objects in the model.
    • TMSCHEMA_ROLES: Shows information about the Role objects in the model.
    • TMSCHEMA_PARTITIONS: Provides information about the Partition objects in each table.

In the past I used to create my own documenters using the DMVs, but I recently discovered the excellent and very complete tool “Model Documenter” created by Marc Lelijveld (MVP).

This tool generates the documentation about your data model in an easy and quick way, you can get more information and download the tool in the page: https://data-marc.com/model-documenter/

 

DISCOVER_CALC_DEPENDENCY

With this DMV you can obtain the list of the dependencies among the objects in your model and extract the DAX expressions. This allows you to have more control over your data models as you can measure the impact of modify or remove any object.

I created the tool “Tabular model cleaner” using this DMV, which helps you to obtain all the dependences of the data model in an efficient and automated way and the usability of the model objects in the reports connected to the Power BI or SSAS Data model.

For more information visit: Tabular model cleaner

 

How to run the DMVs

1. DAX Studio: Open the Power BI Dataset that you want to document. Open DAX Studio and choose the “PBI / SSDT Model” data source option, then click on Connect:

If you have Power BI Premium you can connect to the dataset, to do this select the workspace, go to “Workspace settings” and copy the Workspace Connection and paste it into the “Tabular Server” option in the DAX Studio.

Go to DMV pane and click on the query you want consult:

2. From Power BI Desktop: You can run DMVs queries from DAX Studio but that will return you a table with the data, you should connect to the DMVs using Power BI, to view and analyze the information more easily.

– Open Power BI Desktop.

– Choose the “SQL Server Analysis Services database” source.

– Include the Server and Database.

Consider if you are connected to a local data model, the database name will change every time you open the Power BI Desktop file, so you will have to edit the connection parameter when you need to refresh the data.

DMV
Recent Posts
  • Step-by-step instructions to configure Incremental refresh
    Step-by-step instructions to configure Incremental refresh
  • Metadata-only deployment using ALM Toolkit
    Metadata-only deployment using ALM Toolkit
  • Essential checklist to keep your Power BI Solutions optimized. Part IV. Visualization Good Practices
    Essential checklist to keep your Power BI Solutions optimized. Part IV. Visualization Good Practices
  • Best Practices to Handle Multiple Fact Tables in Power BI
    Best Practices to Handle Multiple Fact Tables in Power BI
QUICK LINKS
  • Home
  • About
  • Our Services
  • Blog
SERVICES
  • Business Intelligence
  • Azure Administration
  • App Development
  • Office 365
CONTACT
18 King St E Suite 1400
Toronto, Ontario, Canada
M5C 1C4

info@datamartin.ca