Analysis Services Server Meta Data And State With DMVs

Believe it or not! A Transact SQL - SELECT FROM WHERE ORDER BY and in a Multidimensional Expressions Query Window. Yep! You can write TSQL queries in the MDX Query window of you SQL Server Analysis Services instance. Yes you can! You really can. What's the purpose of those transact sql queries? Well you can mainly query the metadata and state of your sql server analysis services using those new DMVs. Before SQL Server 2008 you were exclusively able to do that with XMLA (eXtansible Markup Language for Analysis). Below i explore the Dynamic Management Views potential on my local instance of sql server 2008 R2 exposing the $SYSTEM schema: