Hands-On Procedure
Let's get some
hands-on exposure to the Analysis Services Query Log. To prepare, we
will create a new Analysis Services database, based upon the existing Adventure
Works DW sample database, to insulate the original sample database from
modifications we will make. We will accomplish the creation of the "clone"
database from within the SQL Server Management Studio, and then enable
query logging to prepare for our exploration of the Query Log.
Preparation
Create
a Clone Analysis Services Database in SQL Server Management Studio
We will
begin our preparation within SQL Server Management Studio, where we will
create a clone of the sample Adventure Works DW database. Adventure
Works DW can be installed by anyone installing MSSQL Server 2005 Analysis
Services.
1.
Click the Start
button.
2.
Select Microsoft
SQL Server 2005 within the Program group of the menu.
3.
Click SQL
Server Management Studio, as shown in Illustration 1.
Illustration 1: Opening
SQL Server Management Studio
The Connect to Server
dialog appears.
4.
Select Analysis
Services in the Server type selector.
5.
Type / select
the server name / instance, if appropriate) into the Server name
selector.
6.
Supply
authentication information, as required in your own environment.
The Connect to Server
dialog appears, with the appropriate input for our local environments, similar
to that depicted in Illustration 2.
Illustration 2: The
Connect to Server Dialog, with Representative Settings
7.
Click the Connect
button to connect with the specified Analysis Services server.
The SQL Server
Management Studio opens.
8.
Within the Object
Explorer (the leftmost pane of the Studio, by default), expand the
server in which we are working, if necessary, by clicking the "+"
sign to its immediate left.
9.
Expand the Databases
folder that appears underneath the expanded server.
10.
Right-click
the Adventure Works DW database.
11.
Select Back
Up... from the context menu that appears, as shown in Illustration 3.
Illustration 3:
Right-click the Adventure Works DW Database – Select Back Up ...
The Backup Database –
Adventure Works DW dialog appears.
12.
Replace the
default name that appears in the Backup file box with the following:
ANSYS049 Adventure Works DW.abf
13.
Uncheck the Apply
compression setting in the Options section.
14.
Uncheck the Encrypt
backup file setting that immediately follows.
The relevant portion of
the Backup Database – Adventure Works DW dialog appears, as depicted in Illustration
4.
Illustration 4: The
Backup Database – Adventure Works DW Dialog (Relevant Portion)
15.
Click OK to
begin the backup.
The Backup Database –
Adventure Works DW dialog grays, as the Executing symbol in the Progress
pane (lower left corner of the dialog) becomes active. The process may run
several minutes depending upon the resources available on the local system.
Once completed, the dialog closes, returning us to the Management Studio.
We will next restore the same
backup, to create a differently named copy of the existing sample database – a copy
wherein we can make modifications without impairing the existing sample, which
we may wish to use to complete tutorials included with MSSQL Server 2005
or elsewhere.
16.
Within the Object
Explorer, right-click the Databases folder underneath the Adventure
Works DW database.
17.
Select Restore...
from the context menu that appears, as shown in Illustration 5.
Illustration 5:
Right-click the Databases Folder – Select Restore ...
The Restore Database dialog
appears.
18.
Click the Browse
button to the right of the box (second from the top) labeled From backup
file.
The Locate Database
Files dialog appears.
19.
Navigate to
the following backup file (where we located it in our backup steps above):
ANSYS049 Adventure Works DW.abf
20.
Click the file
within the Select the file window, to place the file name into the File
name box, as depicted in Illustration 6.
Illustration 6: Locate
Database Files Dialog with Our Input ...
21.
Click OK
to accept the file path / name, and to close the Locate Database Files
dialog.
We return to the Restore
Database dialog, where we see the file we have selected appear in the From
backup file box.
22.
Type the
following into the Restore database box immediately above the From
backup file box:
ANSYS049 Adventure Works DW
The relevant portion of
the Restore Database dialog, with our input, appears as shown in Illustration
7.
Illustration 7: The
Completed Restore Database Dialog (Partial View)
23.
Click OK
to initiate the restoration.
The Restore Database dialog
grays, as the Executing symbol in the Progress pane, once again,
becomes active. The process runs, and, once completed, the dialog closes,
returning us to the Management Studio. Here we see the new ANSYS049
Adventure Works DW database appear in the Object Browser, as
depicted in Illustration 8.
Illustration 8: The New
Database Appears ...
NOTE: If the new database does not appear immediately,
right-click the Databases folder and select Refresh from the
context menu that appears, as shown in Illustration 9.
Illustration 9:
Refreshing as Required ...
Having
created the ANSYS049
Adventure Works DW
database, we can
now transition to the procedural portion of our session, and get some hands-on exposure
to the Query Log in Analysis Services 2005. In the next section,
we will take an additional preparatory step: enabling the logging of query
statistics.
» 
Comment and Contribute
