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,” InstantDoc
ID 98699.
Before creating the MDX statement, you need to know a couple of key syntax
elements for MDX. A basic MDX structure takes the format of
Select <row_axis, mdx_set,> on Columns,
<column_axis, mdx_set,> on Rows
From <from_clause, mdx_name,>
Where <where_clause, mdx_set,>
This example starts with a Select statement that should look vaguely familiar
to ADO.NET developers. The <row_axis, mdx_set,> 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.