- About the Series
- Introduction
- Introducing MDX Queries
- Key Concepts and Terminology
- Introducing Sets
- Understanding Metadata
- Working with MDX Queries
- Working with Member Sets
- Specifying Members in a Set
- Using the CrossJoin Function to Combine Multidimensional Sets
- Looking Ahead to Calculated Members
- Next in This Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Working with MDX Queries
Next, expand your hands-on exposure to MDX by using the sample application to replicate some of your earlier efforts with the PivotTable list. This will give you a chance to understand the underlying MDX queries that were invisible as you constructed the earlier report visually.
Creating a Simple Query
Let's begin by creating a rudimentary query using the sample application.
Click Start, Microsoft SQL Server, Analysis Services, MDX Sample Application.
The Connect dialog box opens. Figure 19 shows the name of my server, MOTHER, and properly indicates that we'll be connecting via the MSOLAP provider (the default setting).
Figure 19 The Connect dialog for the MDX sample application.Click OK. (If you prefer, cancel the dialog box and connect later by choosing File, Connect.)
In the resulting MDX Sample Application window, clear the top area (the Query pane) of any remnants of queries that might appear there.
In the DB box of the toolbar, select FoodMart 2000 as the database name.
Select Sales in the Cube drop-down list.
The MDX Sample Application window should resemble that shown in Figure 20, complete with the information from the Sales cube displaying in the metadata tree (the left section of the metadata pane).
Figure 20 The MDX sample application window.
NOTE
Many similarities exist between the arrangements of objects in the metadata pane and the Calculated Member Builder, which we explored in earlier lessons in this series. (We'll discuss various attributes of the MDX sample application when they're relevant to the exercises, but it's also useful to explore the Books Online for a wealth of detail about the application.)
Type the following query into the query pane:
SELECT FROM Sales
Click the Run Query button (the button sporting the green arrowhead).
The number 266773.00 appears in the results pane. (You can verify that this is the grand total of Unit Sales by referring to the PivotTable list created earlier. Simply return to the last exercise and remove the filter for Store Type; the grand total at the bottom of the Unit Sales column equals 266733.00.)
The value represents the total for the entire cube, as expected, because the query we've input is simple and specifies no members from any dimensions within the cube. We get the grand total because the query uses all the cube's default members to achieve its objectives. It still supplies a member for every dimension in the cube—only it supplies the default member for each dimension because no specifics are stipulated in the query.
Save the query by selecting File, Save As or clicking the Save Query File button on the toolbar. Name the file MDX04-1.MDX, as shown in Figure 21.
Figure 21 Saving the MDX query.NOTE
I typically prefer to save files to a context-oriented directory or folder. (For example, I use a folder I've created for a client for whom I'm writing MDX queries as a part of an engagement, or for a class I'm teaching.) This is obviously a point of personal taste; the objective is to keep track of where the queries are so that we can find them later. Much rewriting and confusion between altered versions can be avoided by storing the queries in a logical system of some sort to keep them 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 date/time data and "version" information, if applicable.
)
Figure 19)
Figure 20)
Figure 21