MDX Queries

MDX queries are to SQL Server Analysis Services (SSAS) multidimensional data as T-SQL queries are to relational data. Although the syntax of a dimensional query varies from that of a relational query, working with MDX queries can become as comfortable as working with T-SQL queries over time.

To illustrate, let’s look at how to code a simple MDX statement to query data from the SSAS cube created in the articles “Creating Dimensions in SSAS, Part I,” InstantDoc ID 98510 and “Creating Dimensions in SSAS, Part II,” InstantDocID 98699.

Before creating the MDX statement, you need to know a couple of key syntaxelements for MDX. A basic MDX structure takes the format of

Select  on Columns,      on RowsFrom Where 

This example starts with a Select statement that should look vaguely familiar to ADO.NET developers. The defines the first dimension, for columns. A cube allows you to reference how data behaves across its dimensions; the first part of the Select statement defines a dimension that will form the horizontal axis of a grid. By laying out these elements horizontally, you define the columns of that grid, as specified by the on Columns qualifier.

The on Columns qualifier is followed by the definition of the second dimension, for rows. This is the vertical dimension of your grid, which defines the rows of that grid, as specified by the on Rows qualifier. (You can also use an MDX query to define other types of structures; this example introduces the MDX syntax for only the Select statement. For articles that give you more information on MDX, see the web Learning Path at InstantDoc ID 99908.)

Next, the From clause names the location the data is retrieved from. In an MDX query, this location is typically the name of the cube you’re using. Finally, the Where clause defines the data that will be applied. Most SQL developers see a Where clause as a way to exclude results. The MDX Where clause allows you to screen by identifying the data that should be included. You arrive at a similar end result with T-SQL. Think of screening that explicitly includes the data that meets a specified condition. For most MDX queries, the condition is simply that it exists in a given location.

When you understand how a simple query description works, you can try your hand at creating a query. The most basic method is to type the complete MDX query with its specific dimension- and fact-related information. However, you can also do this using a toolset with a graphical UI, such as the SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007. The Data Mining Client for Excel allows you to graphically add data elements and generate the resulting MDX query. You can use this tool to build your MDX query and then copy and paste that query into your custom application. More information on this package and a link to the download is available at www.microsoft.com/sql/technologies/dm/addins.mspx.