Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions - Page 3
March 6, 2006
Preparation: Access SQL Server Management Studio
To reinforce our understanding of the basics we have covered so far, we will use each of the DrillDownLevelTop() and DrillDownLevelBottom() functions in practice exercises, to illustrate its operation upon sample data. We will do so in simple scenarios that place DrillDownLevelTop() and DrillDownLevelBottom() within the context of meeting business requirements similar to those we might encounter in our respective daily environments. The intent is to demonstrate the operation of the functions 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.
Click for larger image
The Connect to Server dialog appears.
4. Select Analysis Services in the Server type selector.
5. Type / select the server name (server name / instance, as 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 in the left upper corner 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 Analysis Services database(s) for our respective server, as depicted in Illustration 3.
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.
10. Expand the Cubes folder within the Adventure Works DW database.
The Cubes folder opens, and we see two cubes. The first of these (assuming only the samples are installed your environment may be different, of course), Adventure Works, is the sample cube with which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 5.
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.
The metadata pane for the Adventure Works cube appears, along with the query pane to its right, as depicted in Illustration 7.
We will be using the query pane in our practice session that follows, to construct and execute our MDX queries. For more information on the limited use of SQL Server Management Studio within this 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).