Procedure: Prepare MSSQL Server Management Studio to Query Analysis Services
October 31, 2006
Use this procedure to prepare SQL Server Management Studio for writing, modifying and executing MDX queries, and examining the results returned, within a practice session.
1. Click the Start button on the PC.
2. Select Microsoft SQL Server 2005 within the Program group of the menu.
3. Click SQL Server Management Studio, as shown in Illustration 1.
The Connect to Server dialog appears, after the brief Management Studio splash screen.
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.
The Connect to Server dialog appears similar to that depicted in Illustration 2.
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 within which we are working.
The Databases folder opens, exposing the detected Analysis Services database(s), as shown 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 the Analysis Services database, as depicted in Illustration 4.
10. Expand the Cubes folder within the Adventure Works DW database.
The Cubes folder opens. We may see multiple cubes here. Adventure Works is the sample cube with which we will be conducting our practice exercises. The cubes appear similar to those shown 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 depicted in Illustration 6.
The Metadata pane for the Adventure Works cube appears, along with the Query pane to its right, as shown in Illustration 7.
We will be using the Query pane to construct and execute MDX queries within our practice session.