Dynamic Management Views for the Analysis Service Db Monitoring

SQL Server 2008 has some of the cool DMV (Dynamic Management View) which can be used for analysis and monitoring of analysis server databases. You can in fact drill to the details to figure out when the cube and other objects like dimesions, measure groups etc were last processed or modified, whether hierarchy is being used etc.

You can run these DMV’s either in DMX query window or using link server on the relational database query window.

Here find list of DMV’s and their categories –

Analysis Service database Schema

$SYSTEM.DBSCHEMA_CATALOGS

$SYSTEM.DBSCHEMA_COLUMNS

$SYSTEM.DBSCHEMA_PROVIDER_TYPES

$SYSTEM.DBSCHEMA_TABLES

Data Mining Schema

$SYSTEM.DMSCHEMA_MINING_COLUMNS

$SYSTEM.DMSCHEMA_MINING_FUNCTIONS

$SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT

$SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT_PMML

$SYSTEM.DMSCHEMA_MINING_MODEL_XML

$SYSTEM.DMSCHEMA_MINING_MODELS

$SYSTEM.DMSCHEMA_MINING_SERVICE_PARAMETERS

$SYSTEM.DMSCHEMA_MINING_SERVICES

$SYSTEM.DMSCHEMA_MINING_STRUCTURE_COLUMNS

$SYSTEM.DMSCHEMA_MINING_STRUCTURES

Meta Data of Analysis Service Database objects like Cube and ther partitions, Hierarchies etc

$SYSTEM.MDSCHEMA_CUBES

$SYSTEM.MDSCHEMA_DIMENSIONS

$SYSTEM.MDSCHEMA_FUNCTIONS

$SYSTEM.MDSCHEMA_HIERARCHIES

$SYSTEM.MDSCHEMA_INPUT_DATASOURCES

$SYSTEM.MDSCHEMA_KPIS

$SYSTEM.MDSCHEMA_LEVELS

$SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS

$SYSTEM.MDSCHEMA_MEASUREGROUPS

$SYSTEM.MDSCHEMA_MEASURES

$SYSTEM.MDSCHEMA_MEMBERS

$SYSTEM.MDSCHEMA_PROPERTIES

$SYSTEM.MDSCHEMA_SETS

To run and get audit and monitoring details of Analysis Service Database, say find the details when the cubes were last updated and processed –

1. Open Sql Server Management Studio
2. Connect to your one of the analysis service database.
3. Right click on the database name and select DMX query.
   
4. Copy and paste following query in the DMX query window –
   SELECT * FROM $SYSTEM.MDSCHEMA_CUBES WHERE CUBE_SOURCE = 1
   
   Find below the result of above query. Only relevant and important columns are included -
   
   

   CUBE_NAME	LAST_SCHEMA_UPDATE	LAST_DATA_UPDATE
   Audit	5/19/2011 16:01	5/20/2011 6:46
   Cost Center	5/19/2011 16:01	5/20/2011 6:46
   Dashboard Metrics	5/19/2011 16:01	5/20/2011 6:46
   High Stability	5/19/2011 16:01	5/20/2011 6:46
   Manage Risk	5/19/2011 16:01	5/20/2011 6:46
   Resource Management	5/19/2011 16:01	5/19/2011 15:43
   SR Practices	5/19/2011 16:01	5/20/2011 6:46
   Superiror Delivery	5/19/2011 16:01	5/20/2011 6:46
   Top Programs	5/19/2011 16:01	5/19/2011 15:43
   World Class People	5/19/2011 16:01	5/20/2011 6:46

Alternatively you can run query connecting to the underline relation database query window. You would need to use link server for query to be able to connect to the analysis service database.

Above query can be run in the relation query window –

SELECT * FROM OPENQUERY(YOUR_LINK_SERVER_NAME,'select * from $SYSTEM.MDSCHEMA_CUBES WHERE CUBE_SOURCE = 1')