Process
the Database Clone and Generate Statistics to be Captured by the Query Log
We need some log entries
upon which to base the work of the Usage-Based Optimization Wizard in
our practice example, so we will populate the table with some rows in a "quick
and dirty" manner – by doing quick manipulations of
the data in the cube. First, we will process the new Analysis Services
database copy we have created.
1.
Right-click
the new Analysis Services database, ANSYS043 Adventure Works DW, which
we created earlier. (The database can be found within the Databases
folder of the Analysis Server instance, within Object Explorer.)
2.
Select Process
from the context menu that appears, as shown in Illustration 20.
The Process Database
dialog appears for Analysis Services database ANSYS043 Adventure Works
DW, as depicted in Illustration 21.
3.
Leaving all
settings on the dialog at default, click OK to begin processing.
The Process Progress viewer
appears, logging the events of database processing as they occur. Once all
database objects are processed, we receive a Process Succeeded message
in the Status bar at the bottom of the viewer, as shown in Illustration
22.
Illustration 22: Successful
Processing Completion is Indicated ...
4.
Click the Close
button to dismiss the Process Progress viewer.
We are returned to the SQL
Server Management Studio, where next we will perform a few actions to
generate statistics in the Query Log table.
5.
Expand the Analysis
Services database ANSYS043 Adventure Works DW, by clicking the "+"
sign to its immediate left within Object Explorer.
6.
Expand the Cubes
folder that appears within the ANSYS043 Adventure Works DW tree.
7.
Right-click
the Adventure Works cube that appears within the Cubes folder.
8.
Select Browse
from the context menu that appears, as depicted in Illustration 23.
Illustration 23: Select
Browse from the Context Menu ...
The Adventure
Works [Browse] tab appears within SQL Server Management Studio.
Here we will do a few browses to generate sample query statistics within the Query
Log.
9.
Expand Measures
within the Metadata pane by clicking the "+" sign to its
immediate left.
10.
Expand the Internet
Sales measure folder that appears underneath the expanded Measures level.
The measures
related to Internet Sales appear.
11.
Drag measure Internet
Sales Amount into the Data pane, dropping it into the area marked Drop
Totals or Detail Fields Here, as shown in Illustration 24.
Illustration 24: Drag
and Drop the Measure into the Data Pane ...
12.
Drag measure Internet
Order Quantity from the Metadata pane to the Data pane,
dropping it to the right of the Internet Sales Amount.
13.
Expand the Date
dimension within the Metadata pane.
14.
Expand the Calendar
folder that appears underneath the expanded Date dimension.
15.
Expand the Date.Calendar
hierarchy that appears within the expanded Calendar folder.
16.
Drag member Calendar
Year into the Data pane, dropping it onto the area marked Drop
Column Fields Here, as depicted in Illustration 25.
Illustration 25: Drag
and Drop Calendar Year into the Column Fields of the Data Pane ...
17.
Expand the Product
dimension within the Metadata pane.
18.
Expand the Product
Categories hierarchy that appears underneath the expanded Product dimension.
19.
Drag the Category
level, appearing underneath the Product Categories hierarchy, into
the Data pane, dropping it onto the area marked Drop Row Fields Here,
as shown in Illustration 26.
Illustration 26: Drag
and Drop Product Categories into the Row Fields of the Data Pane ...
The Data pane appears,
after our insertions, as partially depicted in Illustration 27.
Illustration 27: Our
Initial Browse in the Data Pane (Partial View)
20.
Drill down on each
of the Product Categories by clicking the "+" sign to its
immediate left, as shown in Illustration 28.
Illustration 28: Drilling Down on
Product Categories ...
21.
Drill down on
the Calendar Year CY 2003, to display the bi-annual levels H1 CY 2003
and H2 CY 2003.
22.
Drill down on
level H2 CY 2003 one level further, to expose the underlying quarterly
levels Q3 CY 2003 and Q4 CY 2003, as depicted in Illustration
29.
Illustration 29: Drilling
Down on Select Date Dimension Levels ...
23.
In a manner
similar to the steps above, perform several browses involving different dimensions
/ dimension hierarchies and measures.
24.
Close the Adventure
Works [Browse] tab, when finished.
Having provided for the
collection of a representative set of query statistics, we can now call the Usage-Based
Optimization Wizard into action and get some exposure to its operation.
» 
Comment and Contribute