Chapter 12 -- MDX Statements and ADO MD Objects

Chapter 12

About This Chapter

This chapter focuses on programming online analytical processing (OLAP) Services. When you write programs that work with OLAP Services, you will use Multidimensional Expressions (MDX) and ActiveX Data Objects Multidimensional (ADO MD). MDX is the language that you use to query an OLAP Services multidimensional schema through the PivotTable Service, and ADO MD is a set of objects that provide multidimensional capabilities to ADO. In this chapter, you will learn how to use these tools to query cubes, retrieve and manipulate multidimensional datasets, and create local cubes for offline data analysis.

Before You Begin

To complete the lessons in this chapter, you must

• Have installed SQL Server 7.0 and OLAP Services.
• Be able to log in to SQL Server as an administrator.
• Have completed the exercises in Chapter 8, "Advanced DTS" to create and populate the Northwind_Mart data warehouse database. If you have not created the Northwind_Mart database, you can execute the C:\SQLDW\Exercise\Ch08\MakeMart.BAT batch file to create and populate the database. You cannot perform the exercises in this chapter if you have not created and populated the Northwind_Mart database.
• Have completed the exercises in Chapter 9, "Microsoft SQL Server OLAP Services" to create and populate the Northwind_DSS OLAP Services database and the Sales cube. If you have not created the Northwind_DSS database and the Sales cube, you can execute the C:\SQLDW\Exercise\Ch09\ MakeMDdb.BAT batch file to create and populate the database and the cube. You cannot perform the exercises in this chapter if you have not created and populated the Northwind_DSS database and the Sales cube.
• Have installed Microsoft Excel 2000 and Microsoft Query 2000.
• Have installed Internet Information Server and Active Server Pages.
• Have a basic knowledge of Visual Basic for Applications or VBScript to complete the programming exercises in this chapter.



• Have installed the Exercise files from the Supplemental Course Materials CD-ROM to your hard disk drive. See the "Getting Started" section in "About This Book" for installation instructions.

In many of the exercises in this chapter, you will write queries against the Sales cube. The following tables contain dimension, measure, and hierarchy information about the Sales cube. Review this information to help you create MDX queries.

The following table describes the dimensions of the Sales cube.

Dimension	Levels	Description
Time	Year, Quarter, Month, Day	When the sale is made
Product	Category Name, Product Name	The category and name of each product that is sold
Customer	Country, Region, City, Company Name	Geographical hierarchy for each customer
Shipper	Shipper Name	A list of shippers
Employee	Employee Name	The employee who sells the product

The following table describes the measures of the Sales cube.

Measure	Description
Line Item Total	List price of items that are sold
Line Item Quantity	Number of units that are sold
Line Item Discount	The discount that is applied
Discounted Line Amount	The net sale amount after the discount