Pages

Monday, March 7, 2016

Dynamic Management Views for SSAS

Analysis Services (SSAS) Dynamic Management Views (DMV) are query structures that expose information about local server operations and server health. The query structure is an interface to schema row sets that return metadata and monitoring information about an Analysis Services instance.

SSAS DMVs can be used to monitor the server resources (e.g. connections, memory, CPU, users, aggregation etc. ) and find out the structure of SSAS databases (e.g. hierarchy , dimensions, hierarchies, measures, measure groups, data sources, cubes, actions and KPIs etc.) .

SQL statements can be used to query the row sets, but have following limitation in SQL 2008 version.
  • SELECT DISTINCT does not return DISTINCT values
  • ORDER BY clause accepts just one field to order by. Only one order expression is allowed for TOP Expression at line 1, column 1″
  • COUNT, SUM does not work
  • ORDER BY <number> does not ORDER, but no error
  • JOINS appear not to work
  • LIKE does not work
  • String functions like LEFT do not work
Usefull DMVs
SELECT * FROM $system.DBSCHEMA_CATALOGS -- list of the Analysis Services databases on the current connection.
SELECT * FROM $system.DBSCHEMA_COLUMNS
SELECT * FROM $system.DBSCHEMA_PROVIDER_TYPES
SELECT * FROM $system.DBSCHEMA_TABLES

SELECT * FROM $system.DISCOVER_SCHEMA_ROWSETS
SELECT * FROM $system.DISCOVER_COMMANDS
SELECT * FROM $system.DISCOVER_CONNECTIONS
SELECT * FROM $system.DISCOVER_JOBS
SELECT * FROM $system.DISCOVER_LOCKS
SELECT * FROM $system.DISCOVER_MEMORYUSAGE ORDER BY MemoryUsed DESC
SELECT * FROM $system.DISCOVER_OBJECT_ACTIVITY
SELECT * FROM $system.DISCOVER_OBJECT_MEMORY_USAGE 
    ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC
SELECT * FROM $system.DISCOVER_SESSIONS

SELECT * FROM $system.MDSCHEMA_CUBES
SELECT * FROM $system.MDSCHEMA_DIMENSIONS
SELECT * FROM $system.MDSCHEMA_FUNCTIONS
SELECT * FROM $system.MDSCHEMA_HIERARCHIES
SELECT * FROM $system.MDSCHEMA_INPUT_DATASOURCES
SELECT * FROM $system.MDSCHEMA_KPIS
SELECT * FROM $system.MDSCHEMA_LEVELS
SELECT * FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS 
    -- WHERE MEASUREGROUP_NAME = 'NSA NZ Sales'
SELECT * FROM $system.MDSCHEMA_MEASUREGROUPS
SELECT * FROM $system.MDSCHEMA_MEASURES
SELECT * FROM $system.MDSCHEMA_MEMBERS
SELECT * FROM $system.MDSCHEMA_PROPERTIES
SELECT * FROM $system.MDSCHEMA_SETS

1 comment: