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:
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.