Analysis Services DMVs

DMV Overview

Analysis Services DMVs are a bit like the SQL DMVs. They allow you to view the inner workings and structure of an Analysis Services cube

Solution

Here's a useful link that gives quite a lot of info about them:

http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

• You run them from an MDX query window in SSMS
• They look like SQL statements
• You can use a WHERE clause and and ORDER BY clause
• You can use Instr(Column name) > 0 instead of 'LIKE'
• You can't use '<>'

A couple of useful ones:

The Analysis Services version of sp_who2

select * from $system.discover_connections

or a similar one:

select * from $system.discover_sessions

or

select * from $system.discover_commands

How to take a look at objects in the cube:

select * from $system.discover_object_activity

To see which partitions are being processed

select mid(command_text, instr(command_text, '') + 13, 25) from $system.discover_commands where instr(command_text, 'ProcessFull') > 0