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.
Given below are some of the queries which I found particularly useful. A complete reference to the DMV schema rowset is available in msdn – http://msdn.microsoft.com/en-us/library/ms126079.aspx. Execute these queries from SQL Server Management Studio (SSMS) using MDX or DMX query editor.
1 |
--All Cubes in database |
2 |
SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME |
3 |
FROM $system.MDSchema_Cubes |
1 |
--All dimensions in Cube |
2 |
SELECT [CATALOG_NAME] as [DATABASE], |
3 |
CUBE_NAME AS [CUBE],DIMENSION_CAPTION AS [DIMENSION] |
4 |
FROM $system.MDSchema_Dimensions |
5 |
WHERE CUBE_NAME ='Adventure Works' |
6 |
AND DIMENSION_CAPTION 'Measures' |
7 |
ORDER BY DIMENSION_CAPTION |
2 |
SELECT [CATALOG_NAME] as [DATABASE], |
3 |
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION], |
4 |
HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE], |
5 |
HIERARCHY_IS_VISIBLE AS [VISIBLE] |
6 |
FROM $system.MDSchema_hierarchies |
7 |
WHERE CUBE_NAME ='Adventure Works' |
9 |
ORDER BY [DIMENSION_UNIQUE_NAME] |
01 |
--All Attributes with key and name columns |
02 |
SELECT [CATALOG_NAME] as [DATABASE], |
03 |
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION], |
04 |
LEVEL_CAPTION AS [ATTRIBUTE], |
05 |
[LEVEL_NAME_SQL_COLUMN_NAME] AS [ATTRIBUTE_NAME_SQL_COLUMN_NAME], |
06 |
[LEVEL_KEY_SQL_COLUMN_NAME] AS [ATTRIBUTE_KEY_SQL_COLUMN_NAME] |
07 |
FROM $system.MDSchema_levels |
08 |
WHERE CUBE_NAME ='Adventure Works' |
10 |
AND LEVEL_NAME <> '(All)' |
11 |
order by [DIMENSION_UNIQUE_NAME] |
1 |
--All Hierarchies (user-defined) |
2 |
SELECT [CATALOG_NAME] as [DATABASE], |
3 |
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION], |
4 |
HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [HIERARCHY], |
5 |
HIERARCHY_IS_VISIBLE AS [VISIBLE] |
6 |
FROM $system.MDSchema_hierarchies |
7 |
WHERE CUBE_NAME ='Adventure Works' |
9 |
ORDER BY [DIMENSION_UNIQUE_NAME] |
1 |
--All Hierarchies (Parent-Child) |
2 |
SELECT [CATALOG_NAME] as [DATABASE], |
3 |
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION], |
4 |
HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [HIERARCHY], |
5 |
HIERARCHY_IS_VISIBLE AS [VISIBLE] |
6 |
FROM $system.MDSchema_hierarchies |
7 |
WHERE CUBE_NAME ='Adventure Works' |
9 |
ORDER BY [DIMENSION_UNIQUE_NAME] |
01 |
--All Levels of Hierarchies (user-defined) |
02 |
SELECT [CATALOG_NAME] as [DATABASE], |
03 |
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION], |
04 |
[HIERARCHY_UNIQUE_NAME] AS [HIERARCHY], |
05 |
LEVEL_CAPTION AS [LEVEL], |
07 |
[LEVEL_NUMBER] AS [LEVEL NUMBER], |
08 |
[LEVEL_NAME_SQL_COLUMN_NAME] AS [NAME_COLUMN], |
09 |
[LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] AS [UNIQUE_NAME_COLUMN], |
10 |
[LEVEL_KEY_SQL_COLUMN_NAME] AS [KEY_COLUMN] |
11 |
FROM $system.MDSchema_levels |
12 |
WHERE CUBE_NAME ='Adventure Works' |
14 |
order by [DIMENSION_UNIQUE_NAME] |
01 |
--All Levels of Hierarchies (Parent-Child) |
02 |
SELECT [CATALOG_NAME] as [DATABASE], |
03 |
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION], |
04 |
[HIERARCHY_UNIQUE_NAME] AS [HIERARCHY], |
05 |
LEVEL_CAPTION AS [LEVEL], |
07 |
[LEVEL_NUMBER] AS [LEVEL NUMBER], |
08 |
[LEVEL_NAME_SQL_COLUMN_NAME] AS [NAME_COLUMN], |
09 |
[LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] AS [UNIQUE_NAME_COLUMN], |
10 |
[LEVEL_KEY_SQL_COLUMN_NAME] AS [KEY_COLUMN] |
11 |
FROM $system.MDSchema_levels |
12 |
WHERE CUBE_NAME ='Adventure Works' |
14 |
order by [DIMENSION_UNIQUE_NAME] |
2 |
SELECT [CATALOG_NAME] as [DATABASE], |
3 |
CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE], |
5 |
FROM $SYSTEM.MDSCHEMA_MEASURES |
6 |
WHERE CUBE_NAME ='Adventure Works' |
7 |
ORDER BY [MEASUREGROUP_NAME] |
1 |
--Calculated Measures/Members |
2 |
SELECT [MEMBER_UNIQUE_NAME] AS [CALCULATED_MEASURE], |
3 |
[MEMBER_CAPTION] AS [CAPTION], |
5 |
FROM $system.MDSCHEMA_MEMBERS |
6 |
WHERE CUBE_NAME ='Adventure Works' |
7 |
AND [MEMBER_TYPE]=4 --MDMEMBER_TYPE_FORMULA |
01 |
--Dimension Usage/Fact-Dimension Bus Matrix |
02 |
SELECT [MEASUREGROUP_NAME] AS [MEASUREGROUP], |
03 |
[MEASUREGROUP_CARDINALITY], |
04 |
[DIMENSION_UNIQUE_NAME] AS [DIM], |
05 |
[DIMENSION_GRANULARITY] AS [DIM_KEY], |
06 |
[DIMENSION_CARDINALITY], |
07 |
[DIMENSION_IS_VISIBLE] AS [IS_VISIBLE], |
08 |
[DIMENSION_IS_FACT_DIMENSION] AS [IS_FACT_DIM] |
09 |
FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS |
10 |
WHERE [CUBE_NAME] ='Adventure Works' |
11 |
AND [MEASUREGROUP_NAME] ='Internet Sales' |
Road to BI 