- 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
Key Concepts and Terminology
To begin exploring MDX queries, we'll work with a PivotTable list to gain an appreciation for the concepts involved in limiting the values extracted from a cube to precisely defined levels of specific dimensions.
Quick review of the basic terminology: A cube is a multidimensional structure. The intersections of the dimensions are where data resides, in single or multiple elements called measures. MDX uses a reference system called tuples to identify and extract data, whether the data is in a single cell or a block of cells. Tuples list dimensions and their members (which include levels) to "address" individual cells, as well as sections of cells, within the cube. Because any given cell is an intersection of all the dimensions in the cube, tuples can uniquely identify every cell in the cube. (As a means of reference, measures themselves are treated as a special sort of dimension, named Measures.) Because tuples uniquely identify sections of the cube, based on the dimensional intersections that define each section's address, they don't need to refer to any specific cell or cells. They represent subsets of the multiple dimensions of the cube, and provide slices that encompass more than one cell.
Later in this article, we'll work with ordered groups of tuples, referred to as sets. Common examples of set usage include axis dimensions (the dimensions and members to be returned, used by the SELECT statement in the query) and slicer dimensions (the specific dimension and member criteria to which the returned data is restricted, used by the WHERE statement). The axis dimension returns data for multiple members; the slicer dimension returns data for a single member. The axis and slicer dimensions work in conjunction to define, in terms of the source cube (identified in the FROM clause of the query), the subset of dimensions that make up the result cube, which itself can be composed of multiple dimensions. Out of the potentially large number of possible values held at all the intersections within the cube, any report you want to produce contains only values from specific intersections, which you must define precisely.
We'll explore the components of a query statement in a largely visual environment, focusing on concepts rather than coding until you understand the basics. The visual environment will use MDX queries to extract data for reports, but in a way that will be less cumbersome from the perspective of a beginner. First, let's set up an empty Office PivotTable list connected to the Sales cube (one of the OLAP cube samples that are installed in the Typical installation of Microsoft SQL Server 2000 Analysis Services).
We'll begin with a new, blank page in Microsoft FrontPage:
Click Start, Programs, Microsoft FrontPage.
Choose Insert, Component, Office PivotTable (see Figure 1).
Figure 1 Inserting an Office PivotTable in FrontPage.
As Figure 2 shows, the result is an empty PivotTable list with no data source specified.
Figure 2 The empty PivotTable list.Click the Property Toolbox button on the PivotTable list toolbar to open the Property Toolbox.
Expand the Data Source section and select Connection (see Figure 3).
Figure 3 The Data Source section of the Property Toolbox.Click the Connection Editor button.
The Data Link Properties dialog box appears, displaying the Connection tab.
Click the Provider tab and select Microsoft OLE DB Provider for OLAP Services 8.0 (see Figure 4).
Figure 4 The Provider tab of the Data Link Properties dialog box.Click Next to display the Connection tab of the Data Link Properties dialog box.
Enter localhost as the data source (or use a specific server name, depending on the environment).
Select FoodMart 2000 in box 3 (see Figure 5).
Figure 5 The Connection tab of the Data Link Properties dialog
box.Click the Test Connection button to ascertain that the database (called a catalog) is connected (see Figure 6).
Figure 6 The test connection succeeded.Click OK twice to close the verification message box and then the Data Link Properties dialog box, returning to the PivotTable Property Toolbox.
In the Data Member drop-down list, select Sales (see Figure 7).
Figure 7 The completed PivotTable Property Toolbox and the "map"
for the new PivotTable.Close the PivotTable Property Toolbox.
Click the Field List toolbar button (see Figure 8).
Figure 8 The Field List toolbar button initializes the PivotTable field list.From the PivotTable field list, drag the Store Sales field to the drop area marked Total or Detail Fields.
The PivotTable list now displays a grand total for Store Sales, as shown in Figure 9.
Figure 9 The initial report: a grand total for store sales.Drag the Store Type, Store, Product, and Time dimensions to the filter area at the top of the "map," just under the PivotTable caption bar (see Figure 10).
Figure 10 The results are the same as in Figure 9, because we simply
specified the default members.Click open the drop-down list in the Store Type dimension label, and select the Deluxe Supermarket member (see Figure 11).
Figure 11 Select the Deluxe Supermarket member in the drop-down
list.Click OK to apply the filter to the report. Figure 12 shows the PivotTable list, now filtered to report on the Deluxe Supermarket store type.
Figure 12 The total for the Deluxe Supermarket store type.
)
Figure 1)
Figure 2)
Figure 3)
Figure 4)
Figure 5)
Figure 6)
Figure 7)
Figure 8)
Figure 9)
Figure 10)
Figure 11)
Figure 12