Analysis Services DMVs in SSMS

In the previous post we looked at using Registered Servers in SQL Server Management Studio (SSMS) to quickly and easily connect to Power BI Premium workspaces and potentially other servers. Today’s post describes the use of another feature in SSMS, script projects, to provide quick and easy access to Analysis Services Dynamic Management Views (DMVs). Additionally, a script project containing a query for each Analysis Services DMV is available for download.

First, why do this?

Just like with registered servers in SSMS, if you’re a BI administrator or developer that regularly needs to access and analyze Analysis Services models, whether that’s Power BI Premium, Azure AS, or SSAS, then quick access to the available DMVs aids your productivity.

For example you may want to quickly retrieve the DAX measures in a model including their expressions, the columns of a table, the largest columns in terms of memory consumption or cardinality, the row-level security roles and role memberships, etc. Obviously it wouldn’t be efficient to open the Power BI Desktop file or the Visual Studio solution to obtain this information and a tool like the Tabular Model Schema Reference v2.0 might be more than you need for short term and light ad hoc scenarios.

If you want to get started with Analysis Services Scripts projects for related DAX queries, TMSL processing/refresh scripts, etc you can launch the New Project dialog via the Ctrl+Shift+N keyboard shortcut or it’s just three clicks in SSMS (File-New-Project) to launch the New Project interface:

The ability to store and manage multiple script projects containing sets of related DAX, TMSL, DMV (DMX), MDX, etc scripts is a top reason I prefer to use SSMS in projects and for general purpose testing. For example I have an Analysis Services Scripts project with one or more DAX query files (.msdax) for each DAX function as this helps refresh my mind on the behavior and input parameters of functions.

You may notice that the popular DAX Studio tool exposes the Analysis Services DMVs in the same pane as the model metadata and DAX functions. Given certain advantages of SSMS such as registered servers and scripting projects it’s important to have equivalent DMV query/reference functionality without leaving SSMS.

Analysis Services DMV Project

An Analysis Services Scripts project containing a query for each DMV is available for download from my GitHub repository via the following link:

Analysis Services DMV Script Project

The project includes 113 DMX files for the different DMVs, each of which can be accessed via the Solution Explorer window in SSMS per the following image:

The following steps open the project in SSMS:

1. Download the zip file and extract all its files
2. Open SQL Server Management Studio (SSMS)
   • Optionally connect to an Analysis Services server or a Power BI Premium Workspace
3. In SSMS, either click the Open File icon or use the Ctrl+O keyboard shortcut to launch the Open File window.
4. From the Open File window, navigate through the extracted files and select the AnalysisServicesDMVs.ssmssln file:

With the solution file selected, click Open to expose each query file in the Solution Explorer. The first time you open the file, you’ll be prompted with a security warning to only open from trustworthy sources – click OK to confirm you’d like to open the project.

The script project doesn’t contain any connections to Analysis Services servers but you can quickly add these connections by right-clicking the Connections node below the AnalysisServicesDMVs project icon in the Solution explorer. Alternatively, if you’re already connected to an Analysis Services server, just double-click one of the DMV files to bring it’s query into the query pane and a connection to this server will be added to the project.

The following example uses the TMSCHEMA_MEASURES.dmx file to add the Select statement to the query pane for execution in SSMS:

Copying the Results window for pasting into another application like Excel includes the field names. As of this writing, copying from the Grid in DAX Studio doesn’t include the field names.

Maybe in this basic example for one common DMV the productivity gain is limited. If/when you’re going to need multiple DMVs including less common DMVs then having a script project with these files could be very useful.

Additionally, note that this is only one project in a solution that could contain several other projects. So if you wanted to add a project of DAX queries or TMSL scripts you could have these projects and files available from the same solution in SSMS.

Wrapping Up

That’s going to do it for this post. Between this post and the prior post regarding registered servers you have powerful and mature features in SSMS to help you become more efficient and productive working with Analysis Services models and Power BI Premium datasets.

Upcoming blog posts may target the differences (or ‘feature gaps’) between Azure Analysis Services and Power BI Premium and/or some practices in Power BI deployments you may want to avoid or limit if possible.

If interested in blog posts like this you can click ‘Follow’ on the Subscribe widget.