Querying Using the MDX Query Editor

Just to recap what MDX is, it is a query language that allows you to query multidimensional databases similar to SQL that is used to query relational databases. MDX is used to extract information from Analysis Services cubes or dimensions. Whereas SQL returns results along two axes — rows and columns — MDX returns data along multiple axes. You learn about MDX in depth in Chapters 3 and 7; for now, look at a simple MDX query to learn how to execute it and view the results.

The syntax of a typical MDX query is as follows:

     SELECT [<axis_specification>         [, <axis_specification>...]]      FROM [<cube_specification>]     [WHERE [<slicer_specification>]] 

The MDX SELECT statement contains a SELECT clause where you specify the data you need to retrieve across each axis and a FROM clause that is used to specify the cube from which you retrieve the data, with an optional WHERE clause that is used to slice a small section of data from which you need the results.

In Analysis Services 2000, an MDX Sample application was included with the shipping product; people used this application to send queries to cubes and retrieve results. In Analysis Services 2005, query editors are integrated right in the SSMS for sending queries to SQL Server and Analysis Services. These query editors have IntelliSense (dynamic function name completion) capabilities built in. When MDX queries are saved from the SSMS they are saved with the extension .mdx. You can open the MDX query editor in SSMS by selecting FileNewAnalysis Services MDX Query as shown in Figure 2-43 or by clicking on the MDX query icon as shown in Figure 2-44.

Figure 2-43

Figure 2-44

You will be prompted to connect to your Analysis Services instance. After you establish a connection to your Analysis Services instance, you can select the name of the database you wish to use from the available databases drop-down box shown in Figure 2-45. Select the AnalysisServicesTutorial2005 database that you created in this chapter. In this database you created one cube called Adventure Works DW, which is shown in the Cube drop-down box. The Query Editor is composed of two window panes, the Metadata pane on the left and the Query Construction pane on the right. In the Query Construction pane, you can make use of the IntelliSense feature by pressing Ctrl and Spacebar after typing in a few characters of an MDX keyword.

Figure 2-45

Now you can type the following query in the Query pane:

     SELECT [Measures].members on COLUMNS     FROM [Adventure Works DW] 

You can now execute the query by pressing the Ctrl+E key combination or clicking the Execute button. On execution, the query construction pane splits in two and the results from the server are shown in the bottom half. All MDX queries cited in this book can be executed using this method. Congratulations, you just ran your first MDX query! You can see the results of the MDX query in the result pane where you can see the members on axes and the corresponding cell values as shown in Figure 2-45.