Create calculated members that aggregate sets of values.
Start the lesson
Start Analysis Manager. Right-click the server, and click Restore Database.
Navigate to the folder containing the sample files for this book, select Chapter 7, and click Open. The archived Chapter 7 OLAP database is identical to the one used in Chapter 6, "MDX Values." Click Restore, and then close the Restore Database Progress dialog box.
Open the Chapter 7 HTML file included with the sample files for this book. This HTML file contains a Microsoft Office PivotTable list that retrieves values from the Sales cube of the Chapter 7 OLAP database.
Have you ever noticed that no matter how many times you travel to a location as a passenger, the route seems entirely different the first time you have to navigate-especially if you're alone. The Microsoft Excel PivotTable report and the Office PivotTable list features create multidimensional expressions (MDX) statements to retrieve values from a cube, but creating a report yourself can give you a tremendous appreciation for how Microsoft SQL Server 2000 Analysis Services works.
An MDX query statement is different from an MDX expression. An expression is a formula that calculates a single value. A query is a command that populates a report with many values from a cube. The Office PivotTable list, the Excel PivotTable report, and the browser included in Analysis Manager all generate MDX queries to retrieve values from a cube. You can use tools like these to create reports without writing any MDX statements of your own. Unless you're a developer creating a custom report generator, you'll probably have little occasion to write MDX query statements. So, why should you learn how to create an MDX query statement? Because the most interesting MDX expressions involve creating a single result based on a large set of values from a cube. Those expressions, in effect, create a subquery behind the scenes. Learning how to create an MDX query will enable you to understand clearly what the subquery is doing when you create complex MDX expressions. In the first part of this chapter, you'll learn how to create explicit queries. In the latter part of the chapter, you'll use the concepts learned in creating the reports to create calculated members based on large sets of values.
Preparing to Create MDX Queries
The purpose of an MDX query is to extract values from an OLAP cube into a report. As explained in Chapter 1, "A Data Analysis Foundation," a cube has dimensions (up to 64, if you count Measures as a dimension). A report does not have dimensions; it has axes (typically, a row axis, a column axis, and a filter axis). An axis can include labels from more than one dimension. A cube contains all possible values for all members of all levels of all dimensions. A report contains only selected values from selected levels of selected dimensions. An MDX query statement consists of the instructions for extracting a report from a cube.
Use a PivotTable list to understand MDX terms
An MDX query statement uses several new terms. Seeing the terms in a familiar context will make them easier to learn and understand. Browsers such as the Office PivotTable list use MDX query statements to populate a report. You can use the Office PivotTable list to learn MDX terminology. The initial report in the Chapter 7 HTML file shows only a single cell showing the total value for the Sales Dollars measure. The grand total is a single value from the cube. To retrieve this value, Analysis Services used the default member for each dimension.
In the Web browser, click the Field List toolbar button. Drag the Product, State, Employee, and Time Calendar dimensions from the PivotTable Field List window to the filter area of the report. Each dimension displays the default member in the filter area, and the total value never changes as you add the dimensions. (Since the Time Fiscal and Time Calendar dimensions are really hierarchies of the same dimension, you can include only one in the report at a time. The report still uses the default member of the unused dimension.)
The default member allows a query to ignore some of the dimensions in a cube. If a dimension does not appear on the filter axis, it always uses the default member. If a dimension does appear on the filter axis, it can either use the default member or a different member. Putting a dimension on the filter area of a report overrides the default member for that dimension.
Click the arrow next to the Product dimension list, select the Meat check box, and click OK to show only the total for meat.
If you use nothing but the filter area, you create a report that displays only a single value, which is rarely useful. The row and column axes of a report allow you to display more than one member from a dimension. More than one member from a single dimension is called a set.
Drag the State dimension to the row axis. The three countries-plus the total-appear. The row axis now displays a set that contains four positions. Each position in the set corresponds to a member from the State dimension.
Click the Country level label, and click the Expand toolbar button.
The regions appear. The row axis now displays a set that contains eight positions. Even though the labels are split into two columns-to show the levels of the dimension-each position in the set still contains only a single member from the State dimension.
Drag the Sales Units measure to the data area. The column axis now displays a set that contains two positions, each of which corresponds to a single member from the Measures dimension.
For each cell value you see in the report, you can determine a complete tuple that includes one member from each dimension in the cube. For the cell in the Sales Dollars column with the value 6233.27, the Product dimension contributes Meat, the Employee Dimension contributes Sheri Nowmer, the Time Calendar dimension contributes All Time, and the State dimension contributes North West. Every value in an MDX report requires a single member from each dimension. The filter axis can include only a single member for any one dimension. The row and column axes can include only sets, which include multiple values from a single dimension.
It's not unusual for a cube to contain at least six or seven dimensions. Not counting the filter axis, a report based on an OLAP cube typically contains only two axes-columns and rows. Often, it's useful to combine multiple dimensions from the cube onto a single axis of the report.
In the PivotTable List document, drag the Region and State level headings away from the row area, leaving only the countries and Grand Total on the axis.
Drag the Product level from the filter area to the row area, to the left of the State level. Drag the Subcategory and Product Name labels away from the report. Select the Category label, and click the Expand toolbar button. The report now shows both categories and countries on the row axis. The row axis still contains a set-a set with 13 positions. But each position within the set now contains a two-member tuple-a coordinate from more than one dimension.
Drag the Time Calendar dimension from the filter area to the row area, to the left of the Category label. Drag the Calendar Quarter and Calendar Month labels away from the report. Then select the Calendar Year label, and click Expand. The report now shows years, categories, and countries on the row axis. The row axis still contains a set-now a set with 17 positions. But each position in the set now contains a three-member tuple.
A tuple is a way to specify coordinates from more than one dimension on a single axis. The filter axis can contain only a single tuple. Row and column axes can each contain a set of tuples. The bottom line remains: for each value in the report, there's a single member from each dimension that contributed to the complete coordinate for that cell in the cube. The member for a dimension can come from any of the axes-and more than one dimension can be represented on a single axis. Any dimension not found on the column, row, or filter axis uses the default member...