Execute a Trace to
Monitor Analysis Services Events
We will run the trace
we have established, while simultaneously generating events for it to capture,
in the steps that follow. First, we will open SQL Server Management Studio
for easy access in generating example events.
Prepare SQL Server
Management Studio to Generate Events
1.
Leaving the SQL
Server Profiler in its current state, click the Start button
on the PC, once again.
2.
Select Microsoft
SQL Server 2005 within the Program group of the menu.
3.
Click SQL
Server Management Studio, as depicted in Illustration 7.
Illustration 7: Opening
SQL Server Management Studio
The Connect to Server
dialog appears, as before, after the brief Management Studio splash
screen.
4.
Select Analysis
Services in the Server type selector.
5.
Type / select
the server name (server name / instance, if appropriate) in the Server name
selector.
6.
Supply
authentication information, as required in your own environment.
The Connect to Server
dialog appears similar to that we saw earlier when connecting via SQL Server Profiler.
7.
Click the Connect
button to connect with the specified Analysis Services server.
The SQL Server
Management Studio opens.
8.
In the Object
Explorer pane (it appears by default on the left side of the Studio),
expand the Databases folder (click the “+” sign to its immediate left),
appearing underneath the Analysis Server within which we are working.
The Databases folder
opens, exposing the detected Analysis Services database(s).
NOTE: The Analysis Services databases that appear will
depend, of course, upon the activities that have taken place in your own
environment. For purposes of this practice session, the Adventure Works DW
database must be present. If this is not the case, consult the Books
Online for the installation / connection procedures, and complete these
procedures before continuing.
9.
Expand the Adventure
Works DW database.
The Database expands,
exposing the folders for the various objects housed within the
Analysis Services database, as shown in Illustration 8.
Illustration 8: Exposing
the Object Folders in the Database ...
10.
Expand the Cubes
folder within the Adventure Works DW database.
The Cubes folder
opens. Adventure Works is the sample cube with which we will be
conducting our practice exercises. The cubes appear
similar to those depicted in Illustration 9.
Illustration 9: The
Cubes Appear ...
Execute the Trace
We are now ready to
execute our trace, and then to takes actions in SQL Server Management
Studio to examine as events within the SQL Server Profiler.
11.
Leaving the SQL
Server Management Studio in its current state, return
to SQL Server Profiler.
12.
Click the Run
button in the bottom right corner of the Trace Properties dialog.
The trace
viewer appears immediately, issuing primarily Server State Discover End
and Begin, as well as Notification, events, assuming an idle Analysis
Server. Let’s monitor the action when we kick off a cube processing
event.
13.
Shift back to Management
Studio and right-click the Adventure Works cube.
14.
Select Process
from the context menu that appears, as shown in Illustration 10.
Illustration
10: Select Process for the Adventure Works Cube ...
15.
Click OK
on the Process Cube – Adventure Works dialog that appears next.
The Process
Progress viewer appears, as processing begins.
16.
Leaving the
cube processing, if necessary, shift immediately back to the SQL Server
Profiler window.
Events
began to log
within the trace viewer almost immediately. Among them, we see several
new EventClasses appear, all related to cube processing. Many of the EventClasses
are of the Progress Report type, each of which generates an EventSubclass
that details various steps surrounding the preparation and execution off
the processing cycle.
17.
Allow the cube
to finish processing, if it has not already done so. As soon as the Process
Progress viewer indicates that processing is complete (via the “Process
succeeded” message in the Status bar of the viewer), click Close
to dismiss the Process Progress viewer, as depicted in Illustration 11.
Illustration
11: Click Close to Dismiss the Process Progress Viewer When Complete ...
18.
Click the Pause
Selected Trace button within the trace viewer toolbar, as shown in Illustration
12.
Illustration
12: Select the Pause Selected Trace Button
For
purposes of our practice exercise, let’s filter our trace to
display a couple of EventClasses, simply to make our steps a little more
convenient. Because dimension processing involves an initial ExecuteSQL
step, we will make this one of our filter criteria (we will thus achieve
the tandem objectives of looking at an example each of monitoring cube
processing and query processing). (We will later examine an
example of an event surrounding an MDX query, as well, to
illustrate our capabilities with regard to monitoring performance of our MDX
queries.)
» 
Comment and Contribute
