Database Reference
In-Depth Information
Monitoring SSAS instance using DMVs
In the previous section you learned about a few DMVs referenced by Activity Viewer.
Analysis Services supports many other DMVs not used by Activity Viewer. You can
query DMVs directly using the SSMS or ASCMD command-line utility.
How to do it...
You can query DMVs by executing simple
SELECT
statements in the MDX query win-
dow within SSMS by performing the following steps:
1. Navigate to
FILE
|
New
|
Analysis Services MDX Query
.
2. Connect to your SSAS instance as an administrator.
3. Paste the DMV queries you captured using SQL Server Profiler (see the pre-
vious section,
Monitoring SSAS instance using Activity Viewer
). The
SELECT
statements that you can use for querying DMVs are somewhat limited:
• You can only query one DMV at a time and cannot join multiple DMVs.
• You can extract a subset of all the rows by specifying the
TOP
keyword
and the
ORDER BY
clause.
• You can extract a subset of all the columns by explicitly listing column
names or get all the columns using the star (
*
) operator.
• You cannot use the
GROUP BY
clause to aggregate data. You can set
up a linked server from the SQL Server to Analysis Services and im-
port DMV data into a relational table first if you wish to use
GROUP BY
or any other syntax elements not supported by SSAS DMVs.
How it works...
In this section I will discuss a few DMVs that are particularly useful for monitoring and
troubleshooting Analysis Services, along with the
SELECT
statements used to query
them.
To get a list of all the cubes and dimensions within the current database, you can
query
$system.MDSCHEMA_CUBES
. Dimensions will be returned in the result set
because you can query each dimension as though it were a cube using the
$Di-
mension_Name
notation. To obtain additional dimension specific metadata, such as
Search WWH ::
Custom Search