SQL Server 2012 Analysis Services (SSAS) DMVs

Problem

What are the SQL Server Analysis Services (SSAS) 2012 DMV’s and how can they be used?

Solution

In my previous tip on XMLA both the Execute and Discover methods were discussed in context of running XMLA queries. The discover method exposes metadata about a SSAS database; however the data is returned in XML form. As an alternative to the discover method, SSAS provides a group of dynamic management views (DMV’s for short) which can be queried to return the same data as the discover method. However, by using these DMV’s, the data is returned in a tabular format which is generally easier to read and use as a basis for reports. The queries are DMX, but they have the look and feel of SQL with some caveats. The DMV’s can be broken up into two main categories:

1. the DBSCHEMA/MDSCHEMA DMV’s which retrieve metadata about the SSAS database,  such as cube structure and dimension structure and
2. the Discover DMV’s which retrieve monitoring data such a current connections and locks.

SSAS MDSCHEMA and DBSCHEMA DMV’s

The SSAS DMV’s act in many ways like regular SQL DMV’s and return data in a table format, at least in most cases. The information that can  be queried covers everything from the connections that are currently active to the amount of memory being used to what dimensions are part of the cube.  Furthermore, you can even query a dimension to get its members. Even though the queries are SQL-like, you can not use the following:

• Joins
• Group By
• Like
• Cast / Convert
• Multiple Order By

Last, and probably equally important, in order to run queries against the SSAS database, system administrator permissions are required.

The best way to show what the views can do is to review several number of examples which will in turn convey the limitations that come into play with several of the DMV’s. Probably the best place to start is with a query to get a list of the DMV’s available to query.  The below MDX queries will display the list of “views” which can be queried. Note that the word “views” is used very loosely as these DMV’s are SQL-like but not pure SQL.

SELECT * FROM $SYSTEM.DBSCHEMA_TABLES WHERE TABLE_SCHEMA = '$SYSTEM' 
-- returns only system DMV's 
OR
SELECT * FROM $SYSTEM.DBSCHEMA_TABLES 
-- returns all tables that can be queried including dimension and fact tables 
-- (will discuss more about these items later) 

In order to run these queries, open SSMS and connect to your SSAS database as displayed below. For our examples, we will be using the AdventureWorks 2012 DataWarehouse sample database. Be sure to select the AdventureWorksDW2012 database and verify the query type is set to MDX. If MDX is not selected, you can set the query type by clicking on the MDX button in the tool bar.

The query results are partially displayed in the below screen print. This table list is roughly equivalent to the DMV’s that are available to query against the SSAS database. For details on each of these row sets, MSDN has a DMV reference sheet available at: http://msdn.microsoft.com/en-us/library/hh230820.aspx#bkmk_ref.

Next, we can run the following query to get a list of cubes in a particular database.

SELECT 
[CATALOG_NAME] AS SSAS_Database_Name,
[CUBE_NAME] AS Cube_or_Perspective_Name,
[CUBE_CAPTION] AS Cube_or_Perspective_Caption,
[CUBE_TYPE] AS Cube_Type,
[BASE_CUBE_NAME] AS Base_Cube
FROM 
$SYSTEM.MDSCHEMA_CUBES
WHERE
CUBE_SOURCE=1
ORDER BY CUBE_NAME

Of course there are a few caveats with the query results shown below.

First you will notice that only two cubes are displayed in the objective explorer (left side of above screen print), while the query results show seven rows.  The reason for this discrepancy is that the results include Perspectives in addition to the regular cubes.

Thus, a simple way to return just the cubes and not the perspectives is to adjust our query as shown in the next illustration. In this case, the criteria in the where clause is a bit of cheat in that it looks for a blank value for the Base_Cube_Name by using the less than operator.

SELECT 
[CATALOG_NAME] AS SSAS_Database_Name,
[CUBE_NAME] AS Cube_or_Perspective_Name,
[CUBE_CAPTION] AS Cube_or_Perspective_Caption,
[CUBE_TYPE] AS Cube_Type,
[BASE_CUBE_NAME] AS Base_Cube
FROM 
$SYSTEM.MDSCHEMA_CUBES
WHERE
CUBE_SOURCE=1
AND
[BASE_CUBE_NAME] < ''

Next we can get a list of dimensions using the MDSCHEMA_DIMENSIONS DMV.

SELECT 
[CATALOG_NAME] AS SSAS_Database_Name,
[CUBE_NAME] AS Cube_or_Perspective_Name,
[DIMENSION_NAME] AS Dimension_Name,
[DIMENSION_UNIQUE_NAME] AS Dimension_Real_Name,
[DIMENSION_CAPTION] AS Dimension_Caption,
[DIMENSION_IS_VISIBLE] AS Dimension_Visible
FROM 
$SYSTEM.MDSCHEMA_DIMENSIONS
ORDER BY DIMENSION_NAME

Of course a few caveats exists with the query results shown below. The dimensions are listed multiple times; for instance, the Account dimension is listed seven times. One dimension exists for each cube / measure group; additionally, one dimension, the one whose cube name begins with the $ is the cube level dimension. Furthermore, the dimension caption displays the name that the end users see.

Drilling into the dimension, we can next use the MDSCHEMA_MEASUREGROUP_DIMENSIONS DMV, as displayed below. This DMV breaks out the dimensions at the measure group granularity level. Notice that I have included two items in the Order By clause.

SELECT 
[CATALOG_NAME] AS SSAS_Database_Name,
[CUBE_NAME] AS Cube_or_Perspective_Name,
[MEASUREGROUP_NAME] AS MeasureGroup_Name,
[DIMENSION_UNIQUE_NAME] AS Dimension_Real_Name,
[DIMENSION_IS_VISIBLE] AS Dimension_Visible,
[DIMENSION_GRANULARITY] AS Dimension_Granularity
FROM 
$SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
ORDER BY 
[DIMENSION_UNIQUE_NAME],
[CUBE_NAME]

Unfortunately, including more than one item in the order clause is not supported and produces the following error.

Using just one order by field and adding the cube name to the where clause produces better results which are illustrated below. Again, notice we have duplicates as we did before with the MDSCHEMA_DIMENSIONS DMV.

Before moving on to some of the monitoring DMV’s, let’s review the measure MDSCHEMA_MEASURES DMV.

SELECT
[CATALOG_NAME] AS SSAS_Database_Name,
[CUBE_NAME] AS Cube_or_Perspective_Name,
[MEASUREGROUP_NAME] AS MeasureGroup_Name,
[MEASURE_NAME] AS Measure_Name,
[MEASURE_Caption] AS Measure_Caption,
[MEASURE_IS_VISIBLE] AS Dimension_Visible,
[MEASURE_AGGREGATOR] AS Measure_Aggregator,
[DEFAULT_FORMAT_STRING] AS [Format_String],
[EXPRESSION] AS Calculated_Measure_Expression
FROM
$SYSTEM.MDSCHEMA_MEASURES
ORDER BY
[MEASURE_NAME]

As illustrated below, in addition to the normal name and visibility information available from the dimension DMV’s, the MDSCHEMA_MEASURES DMV conveys the format used for the measure and the aggregation method, which tells how to aggregate the measure such as Sum, Average, or Custom (see http://msdn.microsoft.com/en-us/library/ms126250.aspx ). The DMV also displays the formula used in a calculated measure in the Expression field which is helpful when checking multiple calculated values at one time; non calculated measure have no data in the expression field.

SSAS Discover DMV’s

The SSAS Discover DMV’s retrieve data used to monitor a SSAS database.  Some data points that can be retrieved include the query execution times, current locks, CPU and memory usage, and current connections. We will start with the DISCOVER_CONNECTIONS DMV.

SELECT
[SESSION_COMMAND_COUNT],
[SESSION_CONNECTION_ID],
[SESSION_CPU_TIME_MS],
[SESSION_CURRENT_DATABASE],
[SESSION_ELAPSED_TIME_MS],
[SESSION_ID],
[SESSION_IDLE_TIME_MS],
[SESSION_LAST_COMMAND],
[SESSION_LAST_COMMAND_CPU_TIME_MS],
[SESSION_LAST_COMMAND_ELAPSED_TIME_MS],
[SESSION_LAST_COMMAND_END_TIME],
[SESSION_LAST_COMMAND_START_TIME],
[SESSION_PROPERTIES],
[SESSION_READ_KB],
[SESSION_READS],
[SESSION_SPID],
[SESSION_START_TIME],
[SESSION_STATUS],
[SESSION_USED_MEMORY],
[SESSION_USER_NAME],
[SESSION_WRITE_KB],
[SESSION_WRITES]
FROM 
$SYSTEM.DISCOVER_SESSIONS

This DMV provides us with a wealth of details about the current sessions including the Last Command to be run, how long the command took to run, how much memory the session used, and how many reads and writes were used.

This DISCOVER_COMMANDS DMV provides us with similar information at the command level. You will also notice that you can do a Select * to retrieve all columns from the DMV; however, just as with other select statements, I would recommend only retrieving the columns needed.

SELECT
 *
 FROM
$SYSTEM.DISCOVER_COMMANDS

Some of the Discover DMV’s require the use of SYSTEMRESTRICTSCHEMA and also require passing in additional parameters. In essence, this means that instead of writing a select directly against the DMV, we query the SYSTEMRESTRICTSCHEMA and then specify the DMV and any other parameters required when writing the query.  For example, the below query integrates the DISCOVER_INSTANCES DMV.  In this case, only one parameter is required, INSTANCE_NAME. Notice how the “from” uses the SYSTEMRESTRICTSCHMEA.

SELECT 
*
FROM SYSTEMRESTRICTSCHEMA
(
$SYSTEM.DISCOVER_INSTANCES
, INSTANCE_NAME='SQL2012'
)

The DISCOVER_INSTANCES DMV returns the following data about the instance.

The instance status can be looked up at: http://msdn.microsoft.com/en-us/library/ms126228.aspx.

Using the $SYSTEM.DBSCHEMA_TABLES DMV, you can explore many of the other Discover SSAS DMV’s.

Conclusion

Using the SSAS DMV’s can return a plethora of information about a SSAS cube’s metadata and a cube’s current state, from a monitoring standpoint. These DMV’s are easier to use than XMLA related queries as the data is returned in tabular format and the queries are written, in most cases, in a SQL-like manner. The results, depending on the DMV, can provide much of the information needed for a SSAS administrator to profile and monitor the SSAS cubes.

Next Steps

• Review this article about using SSRS to report the SSAS 2008 database structure using DMV’s — http://www.ssas-info.com/VidasMatelisBlog/144_using-ssrs-to-report-ssas-2008-database-structure-using-dmvs#more-144
• Review additional tips about SQL Server Analysis Services