Administering and Monitoring Analysis Services - SQL Server Analysis Services 2012 Cube Development - page 281

the dimension's default hierarchy, dimension type, and whether the dimension is vis-
ible, check the $system.MDSCHEMA_DIMENSIONS DMV. Additional metadata for
dimension hierarchies is available in $system.MDSCHEMA_HIERARCHIES . You can
query all the MDSCHEMA_* DMVs as well as most other DMVs by simply running the
SELECT * FROM DMV_NAME query.

To get a list of traces that currently monitor your SSAS instance, check $sys-
tem.DISCOVER_TRACES as follows:

SELECT * FROM $system.DISCOVER_TRACES

You can find additional trace related metadata in DISCOVER_TRACE_COLUMNS and
DISCOVER_TRACE_EVENT_CATEGORIES . To list the transactions running on the
current instance, check DISCOVER_TRANSACTIONS .

You can use $system.DISCOVER_DIMENSION_STAT to get the member counts
for each attribute. You must specify the database name and dimension name when
querying this DMV. For example, the following query gets attribute member counts
for the Promotion dimension in the AdventureWorks_Sample database:

SELECT * FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_DIMENSION_STAT,
DIMENSION_NAME = 'promotion'
, DATABASE_NAME='AdventureWorks_Sample')
ORDER BY Attribute_Count DESC

The following table shows the results:

DATABASE_NAME DIMENSION_NAME ATTRIBUTE_NAME ATTRIBUTE_COUNT

AdventureWorks_Sample Promotion Discount Per-

cent

11