With SQL Server 2005 onwards we are provided with a set of DMVs. These DMVs allow us to monitor facts like which index is being used and which are not , where are IO bottlenecks , what is cache hit ratio and other stuff. But what about analysis service.

What if I want to know which of my hierarchies are being used , which are the aggregations that are not being used etc etc.

Well from SQL 2008 onwards you can have this capability.

AS 2008 has

–>  4 DBSCHEMA (Database Schema DMVs)

1. $SYSTEM.DBSCHEMA_CATALOGS

2. $SYSTEM.DBSCHEMA_COLUMNS

3. $SYSTEM.DBSCHEMA_PROVIDER_TYPES

4. $SYSTEM.DBSCHEMA_TABLES

–> 10 DMSCHEMA DMVs

1. $SYSTEM.DMSCHEMA_MINING_COLUMNS

2. $SYSTEM.DMSCHEMA_MINING_FUNCTIONS

3. $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT

4. $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT_PMML

5. $SYSTEM.DMSCHEMA_MINING_MODEL_XML

6. $SYSTEM.DMSCHEMA_MINING_MODELS

7. $SYSTEM.DMSCHEMA_MINING_SERVICE_PARAMETERS

8. $SYSTEM.DMSCHEMA_MINING_SERVICES

9. $SYSTEM.DMSCHEMA_MINING_STRUCTURE_COLUMNS

10. $SYSTEM.DMSCHEMA_MINING_STRUCTURES

These DMVs describe data mining models in the Analysis Services database.

–> There are 13 DMVs which describe the Meta Data of Analysis service data base (cube,partitions,hierarchies etc)

1. $SYSTEM.MDSCHEMA_CUBES

2. $SYSTEM.MDSCHEMA_DIMENSIONS

3. $SYSTEM.MDSCHEMA_FUNCTIONS

4. $SYSTEM.MDSCHEMA_HIERARCHIES

5. $SYSTEM.MDSCHEMA_INPUT_DATASOURCES

6. $SYSTEM.MDSCHEMA_KPIS

7. $SYSTEM.MDSCHEMA_LEVELS

8. $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS

9. $SYSTEM.MDSCHEMA_MEASUREGROUPS

10. $SYSTEM.MDSCHEMA_MEASURES

11. $SYSTEM.MDSCHEMA_MEMBERS

12. $SYSTEM.MDSCHEMA_PROPERTIES

13. $SYSTEM.MDSCHEMA_SETS

 

These DMVs expose a Whole New gold mine for Analysis and monitoring of SSAS. Have a look at some of the DMX queries below which use these DMVs to expose some very useful information.

1. Open SQL Server Management Studio

2. Open a DMX query Window and connect to Adventure Works Cube .(if you dont have adventure works cube you can download it from codeplex.com)

3. paste this query

SELECT *
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME = ‘Adventure Works’
   AND MEASUREGROUP_NAME = ‘Internet Sales’
ORDER BY [MEASUREGROUP_NAME]

You will see that you have information about all the measures in the Measure Group

here is sample output (Only selected few column due to space constraint)

 

CATALOG_NAME	SCHEMA_NAME	CUBE_NAME	MEASURE_NAME	MEASURE_UNIQUE_NAME	DEFAULT_FORMAT_STRING
Adventure Works DW		Adventure Works	Internet Order Quantity	[Measures].[Internet Order Quantity]	#,#
Adventure Works DW		Adventure Works	Internet Extended Amount	[Measures].[Internet Extended Amount]	Currency
Adventure Works DW		Adventure Works	Internet Tax Amount	[Measures].[Internet Tax Amount]	Currency
Adventure Works DW		Adventure Works	Internet Freight Cost	[Measures].[Internet Freight Cost]	Currency
Adventure Works DW		Adventure Works	Internet Unit Price	[Measures].[Internet Unit Price]	Currency
Adventure Works DW		Adventure Works	Internet Total Product Cost	[Measures].[Internet Total Product Cost]	Currency
Adventure Works DW		Adventure Works	Internet Standard Product Cost	[Measures].[Internet Standard Product Cost]	Currency

 

Now that we know how useful can this DMV be here are some scripts that can help you play around with

select * from $system.dbschema_tables
select * from $system.dbschema_columns
select * from $system.dbschema_catalogs

select * from $system.mdschema_cubes
select * from $system.mdschema_hierarchies

select * from [Adventure Works].[$Product] –database dimension

select * from $system.discover_commands
select * from $system.discover_connections
select * from $system.discover_memoryusage
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity where object_reads > 0
select * from $system.discover_partition_stat

SQL Server MVP Darren Gosbell has blogged about it and I suggest you look at his blog http://geekswithblogs.net/darrengosbell/Default.aspx 

(even otherwise it is a very good blog)