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.

4 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

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