Practice
Preparation: Access SQL
Server Management Studio
To reinforce our understanding of
the basics we have covered so far, we will use the DrillDownLevel()
function in a couple of ways that illustrate its operation. We will do so in
simple scenarios that place DrillDownLevel() within the context of
meeting business requirements similar to those we might encounter in our
respective daily environments. The intent, as always, is to demonstrate the
operation of the DrillDownLevel() function in a straightforward,
memorable manner.
We will turn to the SQL Server
Management Studio as a platform from which to construct and execute the MDX
we examine, and to view the results datasets we obtain.
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 2.
The Connect to Server dialog
appears.
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.
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 with which we are working.
The Databases folder opens,
exposing the detected Analysis Services database(s), as depicted in Illustration
3.
Illustration 3: Exposing the Analysis Services Databases
in the Object Browser ...
NOTE: The Analysis Services databases
that appear will depend upon the activities that have taken place in your own
environment, and will likely differ from those shown in Illustration 3
above. 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 an Analysis Services database,
as shown in Illustration 4.
Illustration 4: Exposing the Object Folders in the
Database ...
10.
Expand the Cubes
folder within the Adventure Works DW database.
The Cubes folder opens. We see two
cubes, the first of which, Adventure
Works, is the sample cube with
which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 5.
Illustration 5: The Cubes Appear ...
11.
Click the Adventure Works cube to select it.
12.
Click the New Query
button just under the main menu, in the upper left corner of the Management
Studio, as shown in Illustration 6.
Illustration 6: Click the New Query Button with the
Adventure Works Cube Selected
The metadata pane for the Adventure Works cube
appears, along with the query pane to its right, as depicted in Illustration
7.
Illustration 7: Adventure Works Cube Metadata Appears ...
We will be using the Query pane in the practice
session that follows, to construct and execute our MDX queries.
In addition to its role of providing an environment for the
management of all server types in the SQL Server family, including Analysis
Services, Reporting Services and DTS servers, many additional
functions can now be performed from the SQL Server Management Studio.
Among those functions, I find the capabilities to easily browse data, and to issue
queries, highly convenient. We can accomplish querying in several other ways
within the Microsoft integrated BI solution, but this is certainly one of the
most direct.
We will be using the appropriate query editor in this
and many prospective articles for the purpose of issuing MDX queries within the
practice exercises of the MDX Essentials
series. The query editors within the Management Studio afford
us the capability to not only execute MDX queries against our cubes, but handle
DMX (queries against our data mining models) and XMLA (covering
all command types that can be sent to Analysis Services), as well. The MDX
and DMX editors each include a Metadata pane for the
convenient display of the metadata for the currently selected data source, a
dramatic enhancement over the native tools provided in earlier versions. To
some extent, the "drag and drop" capabilities afforded us via the Metadata
pane might be useful, but we will typically limit our exercises to "straight
MDX" queries, as the focus of this series is the MDX functions,
properties, techniques and so forth. (Articles within my other series explore
other capabilities and features of the SQL Server Management Studio, as
well as the SQL Server Business Intelligence Studio).
» 
Comment and Contribute
