Monitoring Cube Performance and Usage - Expert Cube Development with SSAS Multidimensional Models - page 341

• Progress Report Begin ( 5 )/ Progress Report End ( 6 ): This contains
several subclass events that apply to both processing and query operations.
We list only the subclass events relevant to processing, as follows:
° Process ( 1 )
° ExecuteSQL ( 25 )
° WriteData ( 16 )
° ReadData ( 17 )
° BuildIndex ( 20 )
° BuildAggsAndIndexes ( 28 )
It is important to note there is a sort of nesting of events that can be seen in trace
data. For example, the Process event for a database initiates several other Process
events for related objects, such as the cubes and dimensions in that database. The
outermost events have an execution time (the column Duration , in milliseconds),
which includes the time taken for all the operations executed within those events.
Therefore, the values in the Duration column for different events cannot easily
be summed, because we have to be careful not to sum events that might include
each other. A good approach is to filter rows by event class, event subclass and
ObjectPath (which uniquely identifies the object that the event refers to). For
example, if we want to find how long it took to process the AdventureWorks cube,
we need to find the row in the trace that has the event class Progress Report End ,
the event subclass 1 Process and the object path ServerName.Adventure Works DW
2008.Adventure Works .
ASTrace
Using SQL Server Profiler to capture trace data is a good option if we want
to create a trace manually, but it is not the best way to automate trace data
capture on a production server. A useful tool is ASTrace , which is part of
Microsoft SQL Server Community Samples for Analysis Services, available
from http://sqlsrvanalysissrvcs.codeplex.com . ASTrace captures an
Analysis Services trace and logs it into a SQL Server table.
This utility runs as a Windows service that connects to Analysis Services, creates
a trace, and logs trace events into a SQL Server table using the SQL Server Profiler
format. To customize the trace (for example, to filter on certain events), we can use
a standard trace template authored using SQL Server Profiler. Running as a service,
this tool does not require a logged in user, unlike SQL Server Profiler.