Vidas Matelis Analysis Services Blog

Today I was reading Chris Webb post “Visualising Analysis Services Trace Information in Reporting Services” where at the end he talks about tools to monitor SSAS, that made me thinking about SSAS 2008 DMVs again. So I went back to my SSAS 2008 installation to see what do we have new in CTP6. Just after release Darren Gosbell already blogged about new DMVs in his post “SSAS 2008: What’s new in the Schema Rowsets?“. But just after I started to run queries I realized how cool these new DMVs are. After quick test on them, I decided to blog today about my favorite DMV so far: $SYSTEM.DISCOVER_OBJECT_ACTIVITY.

The best thing about this new $SYSTEM.DISCOVER_OBJECT_ACTIVITY DMV is the fact that it provides resource usage per object since the start of the service. When querying $SYSTEM.DISCOVER_SESSIONS DMV, as soon as user disconnects, all information is lost. But this new DMV has aggregated statistic for all users since Analysis services start. So now quick queries can give you idea where Analysis Services works hardest.

First, BOL provides this information about fields in this DMV:

So now lets run some queries:

1. Question: Where SSAS spends most of CPU time?

Query:

 SELECT TOP 20 OBJECT_PARENT_PATH, OBJECT_ID, OBJECT_CPU_TIME_MS AS CPU, OBJECT_READS AS Reads, OBJECT_ROWS_SCANNED AS [Rows]
   FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY
  ORDER BY OBJECT_CPU_TIME_MS DESC; 

Result:

2. What measure group/partition needs more aggregations?

Query:

SELECT  TOP 20 OBJECT_PARENT_PATH, OBJECT_ID, OBJECT_CPU_TIME_MS AS CPU, OBJECT_AGGREGATION_MISS AS MISS
   FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY
  ORDER BY OBJECT_AGGREGATION_MISS DESC;

Results:

Similar query ordered by OBJECT_MISS column will show objects that are have most cache misses.

And object information is very detail – you can see info on dimension, MDX script, cube, partitions and even each aggregation. So there is a lot of information to analyze.

I got an error when I was trying to run query to return information about all aggregations:

SELECT *
  FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY
 WHERE LEFT(OBJECT_ID,11) = ‘Aggregation’

Error was:

Executing the query …
Internal error: An unexpected exception occurred.
Execution complete 

Not a big deal, I am sure this will be fixed in the next CTP.

Conclusion: Very very useful DMV, and it can be used to build quite a few nice reports.

Question I have right now – can I actually query these DMVs as a SSAS cube from Excel or other front end? Can I somehow specify cube name as $System and slice and dice info in these rowsets? Anyone knows this?

I’ll keep testing this and other new DMVs and I’ll post my findings here in my blog.