SSAS DMVs for Monitoring & Querying Meta-Data

-- List of Dimensions.

SELECT

[CATALOG_NAME] AS Database,

[CUBE_NAME] AS [Cube],

[DIMENSION_NAME] AS [Dimension]

FROM

$SYSTEM.MDSCHEMA_DIMENSIONS

ORDER BY DIMENSION_NAME

-- List of Measures, along with MDX Formula for Calculated Measures.

SELECT

[CUBE_NAME] AS [Cube],

[MEASUREGROUP_NAME] AS [MeasueGroup],

[MEASURE_NAME] AS [Measure],

[EXPRESSION] AS [Calculation]

FROM

$SYSTEM.MDSCHEMA_MEASURES

ORDER BY

[MEASURE_NAME]

-- Dimension-MeasureGroup Relation.

SELECT

[CUBE_NAME] AS [Cube],

[MEASUREGROUP_NAME] AS MeasureGroup,

[DIMENSION_UNIQUE_NAME] AS Dimension_Real_Name,

[DIMENSION_IS_VISIBLE] AS Dimension_Visible,

[DIMENSION_GRANULARITY] AS Dimension_Granularity

FROM

$SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS

ORDER BY

[DIMENSION_UNIQUE_NAME]

-- Current Sessions along with Resource Usage & Last Executed Command.

-- This can also be used to identify long running queries.

select * from $system.discover_sessions ORDER BY SESSION_LAST_COMMAND_ELAPSED_TIME_MS DESC

-- TO kill a long running query, note the SESSION_SPID from the above query and execute the following XMLA command

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

       <SPID>341157</SPID>

</Cancel>

-- Current Connections.

select * from $system.discover_connections

-- Current Commands. For Currently executing command, Command_End_Time will be blank.

select * from $system.discover_commands order by COMMAND_END_TIME