Assess an Existing Dataset with Tabular Editor 3 - Step 2: Query DMVs

---

GETTING USEFUL INFORMATION ABOUT YOUR DATASET

…from dynamic model views, or DMVs, queried with a SQL-like syntax from DAX IDEs

---

In this series, we will learn how to effectively assess an existing or ‘inherited’ dataset.
We will learn how to…

1. Remember the person behind the model
   
   Conduct an efficient analysis with Tabular Editor 3

2. Analyze the model with VertiPaq Analyzer

3. Explore the model with Dynamic Management Views (DMVs)

4. Automate DMVs execution with C# scripts in Tabular Editor

---

Before we start, it is assumed that…

1. You have installed Tabular Editor 3 (TE3) and DAX Studio.

2. You have downloaded the VertiPaq Analyzer from sqlbi

3. You have a dataset to analyze

---

Inheriting a dataset

Working with a dataset you did not create is challenging.
There is rarely documentation that gives a good overview of the scope, logic and shortcomings of the dataset, necessitating deep dives into the model itself in order to understand it. This is particularly true when inheriting problem models that you need to “fix”, perhaps plagued by performance issues, inaccurate calculations, or absent functionality. It’s essential to be thorough in these investigations, but at the same time to also be efficient and objective in the analysis.

In this series, we look at how to handle “model inheritance”, with particular focus on how Tabular Editor 3 makes this very easy & efficient. In this article, we take a brief look at the Dynamic Management Views (DMVs) and how to query them from within Tabular Editor 3.

Part 3: Dynamic Management Views (DMVs)

What are DMVs & why are they useful?

The first thing you should do when getting a new dataset - other than making a back-up - is to analyze it with the VertiPaq Analyzer. We learned in the previous article that the analyzer gets useful summary statistics about our model using the Dynamic Management Views (DMVs) that exist in every analysis services server and database, including Power BI datasets. They exist to facilitate monitoring & optimization of servers / datasets, and are extremely helpful as a catalog to search for useful things. DMVs can be queried to get aggregate or detailed information, not only about the model and its contents, but also about the users connecting to it, and what they are doing.

If you have a background in SQL Server or have used SSAS/AAS, chances are you are already very familiar with DMVs. I do not, so I’m not going to be going so deep into what the DMVs are and how they work. Although, you may also be somewhat familiar with the DMVs if you used DAX Studio. DAX Studio is a tool that helps authoring & optimizing DAX code, where you can run DAX queries on your model. Using the same query windows, you can also query these DMVs. DMVs have a limited SQL-like syntax where you use simple SELECT statements to get what you need.

DAX Studio provides a library of the DMVs as a tab on the left navigation menu. If you double-click on one, you will see an example query appearing, for example for the TMSCHEMA_MEASURES DMV:

Result of the TMSCHEMA_MEASURES DMV

This information can be output in DAX studio to a .csv or Excel file that you can use for documentation, or to maintain a ‘master list’ of measures.

In Tabular Editor 3, you can also query DMVs

These DMVs can not only be queried from DAX Studio, but also Tabular Editor 3 with the ‘DAX Query’ window. Mind, that as of version 3.2.2, the SQL-like syntax to query the DMVs will not be recognized by the code-assist features in TE3, which will pick it up as a ‘DAX Error’.

Querying a DMV from Tabular Editor 3’s DAX Query UI to get the Power BI data sources

What kinds of information can you get?

There’s a lot that you can do with the DMVs. This information is very valuable for documentation, and used by a lot of tools. However, querying the DMVs is a manual task that costs time to execute and manage. While useful, there are now more efficient ways to find or handle most of this information (i.e. VertiPaq Analyzer, Tabular Editor). However, we can actually use these DMVs to extend the functionality of existing tools - for example to automatically tell us certain statistics upon a condition, or when we trigger a C# script (or macro).

In all, the DMVs available from the $System schema come in 4 flavours:

1. DISCOVER method: Requires admin privileges; returns information about model but also connected sessions.

2. DMSCHEMA: Data-mining (DM); not used as often in Power BI, but usually in SSAS/AAS

3. MDSCHEMA: Multi-dimensional (MD); provides information from the model how it is understood from MDX (XML information).

4. TMSCHEMA: Tabular model (TM); provides information from the tabular model (TMSL information).

Some DMVs may fail if you do not have the appropriate permissions

Example use-cases for DMVs

Click below and copy the SELECT statements into a DAX query window, in TE3 or DAX Studio

• Get all measures, with expressions & properties

  SELECT * FROM $SYSTEM.TMSCHEMA_MEASURES

  or (less preferable)

  SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES

• Get all calculation items, with expressions & properties

  SELECT * FROM $SYSTEM.TMSCHEMA_CALCULATION_ITEMS

• Get all Power Query M code, with properties

  Partitions - Data loaded in-memory:

  SELECT * FROM $SYSTEM.TMSCHEMA_PARTITIONS

  Expressions - Not loaded in model (i.e. parameters)

  SELECT * FROM $SYSTEM.TMSCHEMA_EXPRESSIONS

• Get all tables with RI Violations

  Return all tables:

  SELECT * FROM $SYSTEM.DISCOVER_STORAGE_TABLES WHERE RIVIOLATION_COUNT > 0

• Get Extended Properties (i.e. TE deployment metadata)

  SELECT * FROM $SYSTEM.TMSCHEMA_EXTENDED_PROPERTIES

  For Tabular Editor, this lets you see information like:

  • The user who deployed (their local machine ID)

  • The time of deployment

  • The client machine

  • Deployment mode

  • TE version #

• Get model refresh policies (i.e. from incremental refresh)

  SELECT * FROM $SYSTEM.TMSCHEMA_REFRESH_POLICIES

• Get all Columns or Tables*

  *This is more efficiently done from the VertiPaq Analyzer.

  Columns - Basic Info & Properties:

  SELECT * FROM $SYSTEM.TMSCHEMA_COLUMNS

  Columns - Further info (statistics, storage/encoding info, usage):

  SELECT * FROM $SYSTEM.TMSCHEMA_TABLES

• Get all model data sources

  SELECT * FROM $SYSTEM.DISCOVER_POWERBI_DATASOURCES

• Get all object (inter) dependencies

  SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY

---

FURTHER READING

• Marco Russo: Last Process Date in SSAS Tabular

• Meagan Longoria: Documenting your Tabular or Power BI Model

• Darren Gosbell: The easy way to Generate a DAX query for all measures

• Chris Webb: Extracting All The M Code From A Power BI Dataset Using The DISCOVER_M_EXPRESSIONS DMV
  Chris Webb: Creating Excel “Data Dump” Reports from Power BI
  Using this method, you can enter the DMV query and have documentation that automatically updates whenever you refresh the query in Excel.

---

DMVs: Query with SQL-like syntax for useful metadata

1. DMVs return data / metadata to help monitor, document & optimize a server / database, not only about the model, but in some cases, about the users & active sessions connected to it.

2. Some DMVs require admin privileges (TMSCHEMA, DISCOVER method)

3. Querying DMVs is handy for getting an extract of specific things for documentation purposes, though many tools have exploited this to make it easier or even automated.

4. We can extend & exploit the DMVs in tools like Tabular Editor 3 to give us convenient information on-demand, or to alert us when certain things arise in our model.

---

NEXT UP: EXTEND DMVs WITH MACROS IN TABULAR EDITOR

In the next article of this series, we exploit these DMVs ourselves in C# scripts & macros of TE3, to automate detection of data issues while we work. This is an extremely powerful aspect of the tool, combining these methods and everything we’ve learned so far to empower us to save time and make better models — this is the power of Tabular Editor 3.

Click here for the next article in the series

---