Preparation: Access SQL Server Management Studio
To reinforce our understanding of the basics we have covered
so far, we will use the IS operator in a couple of queries that
illustrate its operation. We will do so in simple scenarios that place IS
within the context of meeting basic requirements similar to those we might
encounter within our respective daily environments. The intent is to
demonstrate the use of the operator 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.
Click the Start
Select Microsoft SQL Server
2005 within the Program group of the menu.
Click SQL Server Management Studio,
as shown in Illustration 2.
Illustration 2: Opening SQL Server Management Studio
The Connect to Server dialog
appears, after the brief Management Studio splash screen.
Services in the Server type selector.
Type / select the server name
(server name / instance, if appropriate) in the Server name selector.
information, as required in your own environment.
Click the Connect button
to connect with the specified Analysis Services server.
The SQL Server Management Studio opens.
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
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.
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 4.
Illustration 4: Exposing the Object Folders in the
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 ...
Click the Adventure Works cube to select it.
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
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.
As we discover in articles throughout my Introduction to MSSQL Server Analysis Services
series, among my other series' at Database Journal, the SQL Server
Management Studio serves us in providing a point of interface with all
server types in the SQL Server family, including Analysis Services, Reporting
Services and Integration Services servers, as well as supporting
many additional functions. 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 integrated Microsoft BI solution, but
this is certainly one of the most direct. For more information on the use of
the Query Editor within SQL Server Management Studio for issuing
MDX queries within the practice exercises of the MDX
Essentials series, see Set
Functions: The DRILLDOWNMEMBER() Function. (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).