Sets
in MDX Queries
We will begin our
hands-on exposure to MDX by taking advantage of the Sample Application to
initially generate a simple query, then to move into queries that are more
sophisticated. In this way, we will gain an understanding of MDX queries "from
the ground up" in an efficient manner, expanding our grasp of both MDX and
our cube's metadata simultaneously.
Basic Query Components
Let's begin by creating a rudimentary
query using the Sample Application. We will begin by taking the following
steps:
1. Go to the Start
button on the PC, then navigate to Microsoft SQL Server --> Analysis Services, then to the MDX Sample
Application.
We are
initially greeted by the Connect dialog, shown in Illustration 1.
Illustration 1: The
Connect Dialog for the MDX Sample Application
The
illustration above depicts the name of my server, MOTHER, and properly
indicates that we will be connecting via the MSOLAP provider (the
default).
2. Click OK.
(We might also choose
to cancel the dialog box and connect later by clicking Connect on the File
menu.)
The MDX
Sample Application window appears.
3. Clear the top
area (the Query pane) of any remnants of queries that might appear.
4. Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
5.
Select the Warehouse
cube in the Cube drop-down list box.
The MDX
Sample Application window should resemble that shown in Illustration 2,
complete with the information from the Warehouse cube displaying in the Metadata
tree (left section of the Metadata pane).
Illustration 2: The MDX
Sample Application Window (Compressed)
Many similarities
exist between the arrangements of objects in the Metadata pane and the Calculated
Member Builder, which we have explored in earlier lessons of this series.
We will discuss various attributes of the MDX Sample Application when they are
relevant to the exercises we undertake; it might prove highly useful to explore
the Books Online for a wealth of detail about the application.
6.
Type the
following query into the Query pane:
SELECT
FROM Warehouse
7.
Click the Run
Query button on the toolbar (the button sporting the green arrowhead-shaped
icon - a tool tip will alight when the cursor is placed upon the button to
positively identify it for us).
We see
the number 102,278.41 appear in the in the Results pane. This amount
represents the "grand total" of all measures in the Warehouse
cube, because the query we have input is simple, and specifies no
members from any dimensions within the cube. We are thus awarded the grand
total, because the query supplies the default member for each dimension;
this is standard behavior when no specifics are stipulated in the query.
8.
Save the query
by selecting File ->
Save As (the
Save Query File button on the toolbar will work equally well), and call
the file MXAS06-1.
9.
Click Save
to save the file, after navigating to a meaningful location of your choice.
Note: I typically prefer to save files
to a context-oriented directory / folder (for example, a folder I have created
for a client for whom I am writing MDX queries as a part of an engagement, or
for a class I am teaching). This is obviously a point of personal taste; our
objective is to keep track of where the queries are so that we can find
them in time of need. Much rewriting and confusion between altered versions
can be avoided by storing the queries in a logical system of some sort to keep
organized. My favorite way to do this is to create a database within which to
store the query strings, together with descriptions, author and keyword
information, along with creation / modification datetime data and "version"
information, if applicable.
» 
Comment and Contribute
