Enable Query Logging to Gather Statistics
Usage-based optimization is based upon usage. We
therefore must capture usage statistics to have a basis for
optimization. We do this by enabling query logging within the Analysis
Server, as we shall see in the steps that follow.
1.
Right-click
the Analysis Server with which we are working.
2.
Select Properties
from the context menu that appears, as depicted in Illustration 10.
Illustration 10: Select
Properties from the Context Menu ...
The Analysis Services
Properties dialog appears. A busy place, indeed, many settings of a default
and operational nature are maintained here. (My advice is to learn all that we
can about each entry within this table-like dialog, so as to know of its
existence when the time comes to address the utility that it offers). First,
we need to enable the logging of query statistics.
3.
Scroll down to
the Log \ QueryLog \ CreateQueryLogTable entry in the table within the
dialog.
4.
Change the Value
setting for the entry, which is defaulted to false, to true,
via the selector provided, as shown in Illustration 11.
We next need to direct Analysis
Services as to where we wish to host the Query Log. Analysis
Services 2005 offers us a great deal of flexibility (an improvement over Analysis
Services 2000's provision of an MS Access database for this purpose,
which could be migrated to MSSQL Server). We will direct that the Query
Log database be created within the sample AdventureWorksDW
relational database, which underlies our new clone Analysis Services
database, ANSYS043 Adventure Works DW. This is a logical place to put
the Query Log for this session, but we are free to put it into any OLE-DB
/ .NET compatible data source.
5.
In the Log
\ QueryLog \ QueryLogConnectionString row, immediately below the CreateQueryLogTable
row (where we assigned the value of true above), click the Value
box, and then click the box that appears to its immediate right (marked "..."),
as shown in Illustration 12.
Illustration 12: Beginning
Connection Setup for the Query Log Data Source
Connection Manager appears.
6.
Leave the Provider
setting at its default of Native OLE DB \ SQL Native Client.
7.
Select / type
the appropriate Server or Server / Instance combination into the Server
name box.
Mine is MOTHER1\M1MSSQL2K5,
as we see in illustrations throughout recent articles of the series.
8.
In the Log on
to the server section of the Connection Manager dialog, make the
authentication selections appropriate to your environment.
I am using Windows
Authentication, and therefore select the respective radio button.
9.
In the Connect
to a database section in the lower half of the dialog, in the selector box
labeled Select or enter a database name, select the AdventureWorksDW relational
database.
The Connection Manager
dialog appears, with our input, similar to that depicted in Illustration 13.
Illustration 13: Connection
Manager Dialog, with our Input
10.
Click the Test
Connection button to ascertain connectivity to the database.
Assuming correct
settings, confirmation of connectivity appears in a message box, as shown in Illustration
14.
Illustration 14: "Test
Connection Succeeded"
11.
Click OK
to close the message box.
12.
Click OK
to accept settings and to close the Connection Manager dialog.
We are returned to the Analysis
Services Properties dialog, where we see that the Value box of the Log
\ QueryLog \ QueryLogConnectionString row now contains a connection
string, courtesy of the Connection Manager. We have enabled the collection
of query statistics and established the location of their collection.
Next, we will make a couple of additional adjustments before putting the Usage-Based
Optimization Wizard, which depends upon the Query Log to perform its
work, into action.
13.
In the Log
\ QueryLog \ QueryLogSampling row, which appears three rows below the Log
\ QueryLog \ QueryLogConnectionString row, change the Value from the
default of 10 to 1.
Here we are merely
increasing the sampling rate from "capture statistics from every
tenth query" to "capture statistics for each query,"
a step similar to those we took in previous articles with the Usage-based
Optimization Wizard as it existed in Analysis Services 2000.
(Although the setting was managed a bit differently, in the Write to log
once per [number] box within Analysis Services 2000, our intent
then, as it is now, was to simply allow the log to capture enough data to make the
procedural steps of our practice exercise meaningful.)
14.
In the Log
\ QueryLog \ QueryLogTableName row, which appears in the row immediately
below the Log \ QueryLog \ QueryLogSampling row, modify the Value
from its default of OLAPQueryLog to the following:
MSASQueryLog
Our settings, within the
rows of the Analysis Services Properties dialog we have visited, appear
as depicted in Illustration 15.
Illustration 15: Modified
Settings within the Analysis Services Properties Dialog
15.
Click OK
to enact the settings we have made above.
The "Executing"
indicator appears in the bottom left corner of the dialog momentarily, and then
the Analysis
Services Properties
dialog closes. We are returned to the SQL Server Management Studio.
We can confirm the
creation of the new MSASQueryLog table within the AdventureWorksDW relational
database easily from the SQL Server Management Studio, where we can
access the relational world in combination with the OLAP world, as we shall see
in the next steps.
16.
Click the
Connect button atop the Object Explorer.
17.
Select Database
Engine from the menu that appears, shown circled in Illustration 16.
Illustration 16: Connecting
to the SQL Server 2005 Database Engine
The Connect
to Server dialog appears, just as we saw earlier when connecting to Analysis
Services.
18.
Enter the
correct Server or Server / Instance combination into the Server
name box.
19.
Select the
appropriate authentication mode into the Authentication box that
immediately follows (with related details in the Password box, as
appropriate).
The Connect to Server
dialog appears similar to that depicted in Illustration 17.
Illustration 17: The
Connect to Server Dialog Appears
We return to the SQL
Server Management Studio, where we see the instance of the Database
Engine appear within the Object Explorer, underneath the Analysis
Server with which we have been working, as shown in Illustration 18.
Illustration 18: The
Database Engine Instance Appears within Object Explorer
21.
Expand the Database
Engine Server by clicking the "+" sign to its immediate left, if
required.
22.
Expand the Databases
folder underneath the expanded server.
23.
Expand the AdventureWorksDW
database.
24.
Expand the Tables
folder within the AdventureWorksDW database.
The tables within the
folder appear. Among them, we see the new MSASQueryLog table that we
created in our earlier steps, as depicted in Illustration 19.
Illustration 19: The
MSASQueryLog Table Appears in the AdventureWorksDW Database
The table is in place and
query logging is enabled. We are positioned to conclude our preparations, by
generating some sample queries, and to begin getting some hands-on practice
with the Usage-Based Optimization Wizard.
» 
Comment and Contribute
