SSAS DMV (Dynamic Management View)

What are SSAS DMVs?

SSAS Dynamic Management Views are Analysis Services schema rowsets exposed as tables that we can query using T-SQL SELECT statement. For example, to query discover_connections DMV, we can write:
select * from $system.discover_connections

DMVs are more convenient to use than schema rowsets because:
a)    we use SELECT statement rather than XMLA
b)    the output is a table rather than XML

Not all DMVs are schema rowsets. The DMVs which expose cube and dimension data are not schema rowsets. For example, to get the members of product dimension we can write:
select * from AW.[$Dim Product].[$Dim Product] where Color = ‘Red’

Even though they are T-SQL statements, in the Management Studio we write these SSAS DMV SELECT statements in the DMX query window, not in the SQL query window. This is because we need to connect to SSAS, rather than SQL Server relational engine. The SSAS DMVs are available in SSAS 2008 and 2008 R2; they are not available in SSAS 2005 and 2000.

What are they for?

SSAS DMV can be used to monitor the server resources such as:

• who are connecting to the SSAS server
• which database/cube they are using
• what MDX they are running
• how much memory and CPU is used by each command and each user
• how long they took to execute, and when did they run
• which aggregates are used/hit and which are missed
• which objects are locked and by whom
• how much memory and CPU is used by each dimension and each measure
• how much memory is used by each data cache and objects of SSAS engine
• which cubes, dimensions, partitions and measures are used most and which are rarely used (how many times they are read, how many rows returned, how many KB read)

SSAS DMVs can also be used to find out the structure of SSAS databases in the server:

• List of dimensions, hierarchies, measures, measure groups, data sources, cubes, actions and KPIs in each SSAS database
• Members of every hierarchy and level in every cube
• List of MDX functions, with their descriptions and parameters
• List of mining structures, models, and algorithms.

How do I use them?

dbschema_tables lists all the DMVs:
select * from $system.dbschema_tables

discover_connections lists who’s connecting to the server, since when, for how long, from which PC (IP) and using what client:
select * from $system.discover_connections

discover_sessions and discover_commands lists the MDX/command used by each user, which SSAS database/cube they are using, how much CPU and memory is used by each command:
select * from $system.discover_sessions
select * from $system.discover_commands

We can join discover_connections and discover_sessions on Connection_ID column. And we can join discover_sessions and discover_commands on Session_SPID column. To join them we can:

• Create a linked server from a SQL Server to the SSAS server, then use OpenQuery. See Midas Matelis post here.
• Use SSIS to query the 3 DMVs separately and the join them in SSIS using Merge Join transformation. See Chris Webb post here.
• Use ADOMD to query the DMV on the SSAS server, and then join them using DataSet. See my post here.

discover_object_memory_usage and discover_object_activity lists the memory usage and CPU time for each object i.e. cube, dimension, cache, measure, partition, etc. They also show which aggregations were hit or missed, how many times these objects were read, and how many rows were returned by them:
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity

The following DMVs provide information on the cube structure:
select * from $system.mdschema_cubes
select * from $system.mdschema_dimensions
select * from $system.mdschema_hierarchies
select * from $system.mdschema_levels
select * from $system.mdschema_measuregroups
select * from $system.mdschema_measuregroup_dimensions
select * from $system.mdschema_measures
select * from $system.mdschema_properties
select * from $system.mdschema_members
select * from $system.mdschema_sets
select * from $system.mdschema_kpis
select * from $system.mdschema_input_datasources

We can use where clause and order by:
select * from $system.discover_object_memory_usage where object_parent_path = ‘Global.ObjectPool’ order by object_id desc

We can use ‘and’, ‘or’ and ‘not’. But there is no ‘like’, ‘<>’ (not equal to), ‘in’ and ‘join’:
select * from AW.[$Dim Product].[$Dim Product] where Color = ‘Blue’ or Color = ‘Silver’
select * from $system.discover_object_activity where not object_rows_returned > 0

In 2008 R2 we can use ‘<>’ (not equal to):
select * from $system.dbschema_tables where table_catalog <> ‘Adventure Works DW’ (only works in 2008 R2)

We don’t have ‘like’ so use left or right instead. Remember we can use ‘not’:
select table_schema from $system.dbschema_tables where table_catalog = ‘Adventure Works DW’ and left(table_schema,1) = ‘$’
select * from … where … and not left(table_schema,1) = ‘$’

We can use distinct:
select distinct object_parent_path from $system.discover_object_activity

On some DMVs we need to write ‘SystemRestrictSchema’. For example if we write:
select * from $system.discover_partition_stat
SSAS will respond with:
The ‘CUBE_NAME’ restriction is required but is missing from the request.  Consider using SYSTEMRISTRICTSCHEMA to provide restrictions.

This is because discover_partition_stat requires 4 restrictions: database_name, cube_name, measure_group_name and partition_name. In other words, we need to specify these 4 parameters. So we need to write:
select * from SystemRestrictSchema($system.discover_partition_stat, CUBE_NAME = ‘Adventure Works’, DATABASE_NAME = ‘Adventure Works DW’, MEASURE_GROUP_NAME = ‘Internet Orders’, PARTITION_NAME = ‘Internet_Orders_2004’)

Another example on restriction:
select * from systemrestrictschema($system.discover_performance_counters, PERF_COUNTER_NAME = ‘\MSAS 2008:Connection\Current connections’, PERF_COUNTER_NAME = ‘\MSAS 2008:MDX\Total Cells Calculated’)

If we have a SSAS database with 1 cube, 1 dimension and 1 measure group, when we execute: “select * from $system.dbschema_tables”, at the top of the list we will see 4 rows with table_catalog = ‘DatabaseName’:

No	table_catalog	table_schema	table_name	table_type
1	DB Name	$Dim1	Dim1MG	system_table
2	DB Name	$Dim1	$Dim1	table
3	DB Name	Cube1	MG1	system_table
4	DB Name	Cube1	$Dim1	table

Each of these 4 tables is a DMV that we can query using SELECT statement.

• No 2 is a database dimension
• No 4 is cube dimension (a database dimension that is used in a cube).
• No 3 is the measure group.
• No 1 is the ‘dimension cube’. For every dimension, SSAS create a cube. This cube is called ‘dimension cube’. This cube contains only 1 dimension with no measure. This cube can’t be accessed from the client. It is only available internally within SSAS engine.

So to query these 4 DMVs, we need to fully qualify the table name, i.e. table_catalog.table_schema.table_name:

#1 select * from [DB Name].[$Dim1].[$Dim1]  –database dimension
#2 select * from [DB Name].[Cube1].[$Dim1]  –cube dimension
#3 select * from [DB Name].[Cube1].[MG1]    –-measure group
#4 select * from [DB Name].[$Dim1].[Dim1MG] –dimension cube

#1 returns the members of Dim1.
#2 also returns the members of Dim1.
#3 returns the fact rows for measure group1.
#4 returns nothing

DMV on Books Online

The only mention about DMV in the SQL Server Books Online is on the Data Mining Schema Rowsets page of the November 2009 edition:

“In SQL Server 2008, the data mining schema rowsets are also exposed as tables in the Transact-SQL language, in the $SYSTEM schema. For example, the following query on an Analysis Services instance returns a list of the schemas that are available on the current instance: SELECT * FROM [$system].[DBSCHEMA_TABLES]”

The Books Online explains every column of the 46 schema rowsets in SSAS: 16 XMLA, 4 OLE DB, 16 OLE DB for OLAP and 10 Data Mining. However, there are 54 DMVs in 2008. The following 12 discover_* DMVs are not mentioned in Nov 2009 SQL Server Books Online’s schema rowsets documentation: traces, trace_definition_providerinfo, trace_columns, trace_event_categories, memoryusage, memorygrant, transactions, master_key, performance_counters, partition_dimension_stat, partition_stat, dimension_stat. But, they are mentioned in the SSAS Protocol Specification section 3.1.4.2.3, about Discover Request Types.

There are 3 schema rowsets which are not made into DMV: mdschema_actions, discover_datasources and discover_xml_metadata. To use them we still need to use XMLA. Those of you who wonder about the numbers don’t tally (54 <> 46+12-3), that’s because there is 1 duplicate: discover_keyword is available both as XMLA and OLE DB for OLAP.

In 2008 R2 (I’m using Nov CTP), there 3 new DMVs: discover_storage_tables, discover_storate_table_columns, and discover_storage_tables_column_segments. Again, they are not mentioned in SQL Server Books Online’s schema rowsets documention, but they are mentioned in the SSAS Protocol Specification section 3.1.4.2.3.