Assess an Existing Dataset with Tabular Editor 3 - Part 3: Extend DMVs with C# Scripts & Macros
TABULAR EDITOR SCRIPTS FOR MODEL ANALYSIS & DOCS
…get information and documentation with the push of a button
In this series, we will learn how to effectively assess an existing or ‘inherited’ dataset.
We will learn how to…
Remember the person behind the model
Conduct an efficient analysis with Tabular Editor 3Analyze the model with VertiPaq Analyzer
Explore the model with Dynamic Management Views (DMVs)
Automate DMVs execution with C# scripts in Tabular Editor
Before we start, it is assumed that…
You have installed Tabular Editor 3 (TE3) or Tabular Editor 2 (TE2)
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 use the special features of Tabular Editor 3 together with DMVs to do some cool things to provide us with useful information.
Part 3: Extending DMVs with C# Scripts & the Best Practice Analyzer
Why C# scripts?
Tabular Editor 3 (TE3) is an extremely powerful tool for developing tabular data models. Among the many features present in TE3 is the ability to perform advanced scripting with C# scripts. This lets more advanced users be able to manipulate more directly certain aspects of the tabular model, doing things not even possible in the native user-interface by leveraging C# and LINQ. As explained in the docs of TE2, a scripting API provides access to two top-level objects <Model> and <Selected>, through which you can interact with the various properties.
These scripts have many use-cases. Among them, you can save scripts as custom actions (TE2) or macros (TE3) which can be re-used with the push of a button. The scripts can also be context-dependent, referring to a selected object instead of all objects, or an explicitly named one. An example of a C# script is this one used to format all DAX in a tabular model. Using the command-line interface (CLI) of TE2, these scripts can even facilitate automation of tasks using tabular editor. A CLI is not yet available for TE3, but is a feature on the roadmap TBD.
Can you evaluate a DAX Query with a C# script or macro?
While pouring over the TE2 docs, one might notice there are script snippets for executing TMSL (tabular model scripting language) and evaluating DAX queries. Now, connecting the dots with the previous articles, we might wonder a few things:
Can the DMVs be executed from within a C# script?
If yes to #1, then can we make a context-dependent macro that tells us if a selected table has RI violations?
If yes to #1, then can we make a macro that gives us a dump of all the documentation in a file?
The answer is yes. With some considerations:
When using the DAX functions in the C# script, the session can time out. Any C# script or macro from that XMLA session (thus from TE) will use the same session ID; so if it times out, you have to close & re-open (or restart) the session in order to be able to evaluate DAX queries or query a DMV. I’m not sure if adding a daxReader.Close(); within the scope of the reader will ensure this doesn’t happen.
To use or visualize the results, you need to do some data processing of the output. This isn’t so difficult, but just requires working with dictionaries / arrays and finding a way to get the object you want, using keys or indexes.
Here’s the code:
Example 1: Get last refresh time
This script is a proof-of-principle - query the DMV to get the last process time of the model.
Shows the last time the model was refreshed
Example 2: Generate documentation files
This script evaluates the DMVs from within TE3 and returns .tsv outputs.
It outputs 9 DMVs and 1 DAX Query evaluation result. They can be customized or extended to return whatever you want.
Generates an export of any DMV or DAX query
An example output for measures
You can save the script as a macro and add it to your toolbar - docs with 1 click within TE3.
Example 3: Find the number of referential integrity (RI) violations - for the whole model or a selected table
This script gets the number of referential integrity (RI) violations in the model, showing the tables they come from.
Check for RI violations - this will tell you if you should run the VertiPaq Analyzer for deeper analysis
Below is a version that can be used as a macro on a selected table, to see the RI violations for relationships from that table.
Run a context-dependent macro to for RI Violations on a specific table
TO SUMMARIZE
When inheriting a dataset, try to remember the person behind the model.
Be aware of your internal biases, and that you don’t have the full story; reserve your judgement to the model, and not the person who made it
Construct & communicate your criticism in a helpful, empathetic way to facilitate learning
Use the VertiPaq Analyzer to explore the model’s statistics, and find opportunities for optimization
Get a feel for the overall number & type of objects
Look for abnormal or atypical things (many-many relationships, bidirectional relationships, etc.)
Look for outliers in table & column statistics (cardinality, size…)
Pay attention to RI violations and examine their impact & missing members
Query DMVs or use tools that leverage DMVs to document the model
Extract a list of all M expressions, Measures, Calculation Items, and data sources
C# scripts in TE3 can be used to execute DAX code.
You can query DMVs with the push of a button to give you context-dependent information
The Best Practice Analyzer can notify you when models are breaking conventions, and direct you to specific objects.
