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:

Analysis Services Script Project

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:

Analysis Services DMV Project in SSMS

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:
Analysis Services SSMS Solution 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:

Query Analysis Services DMVs 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.

6 comments

  1. One of the biggest gaps for me between DMVs in SSAS and PBI Premium via XMLA is the lack of support for role memberships. In SSAS I can write a query to show the role definitions and memberships but in PBI Premium i can only expose the role definitons.

    Like

    1. Thanks Fran –

      Yes, you can import the results of DMV queries into Power BI datasets on a schedule. You can use the Azure Analysis Services connector in Power BI Desktop and your Power Query (M) expression should look like the following:

      let
      Source = AnalysisServices.Database(“powerbi://api.powerbi.com/v1.0/mybigcompany.com/mypremiumworkspace”, “mypremiumdataset”, [Query=”select * from $SYSTEM.TMSCHEMA_MEASURES”])
      in
      Source

      You’ll of course need to modify the tenant name and the name of the workspace for the analysis services server as well as the name of the dataset for the database. My Tabular Schema Reference v2.0 template works with premium datasets via XMLA endpoints. This being said, XMLA is still in preview so you probably shouldn’t have anything critical that depends on the XMLA-based refresh yet.

      Like

  2. Running SSMS 18.4 when double-clicking one of the files I always get the error message “Mining models were not found” (translated) due to the .dmx extension. Is there a workaround?

    Like

    1. Hi Frank,
      You should still be able to run the DMV queries. After double-clicking a DMV file from the Solution Explorer window on the right, click F5 on your keyboard to execute the query. The results of the DMV query should appear at the bottom of the code window.

      The Mining model graphical interface on the left will advise that no mining models were found but this doesn’t prevent you from running the queries. You can reduce the width of the mining model menu by hovering over its right edge and dragging to the left. This will help maximize screen space for the code/query and results window in the middle, the registered servers and object explorer windows on the left, and the Solution Explorer and properties on the right.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s