Start the Lesson
1. | Start Analysis Manager. Right-click the server, and click Restore Database. |
2. | 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. |
3. | 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.