Chapter 3: Introduction to MDX

In Chapter 2 you ran a simple MDX query to retrieve data from Analysis Services 2005. Building on that, in this chapter you learn the fundamental concepts underlying MDX and how you can manipulate and query multidimensional objects within Analysis Services using MDX. This chapter forms the basis for much of the subsequent chapters in this book; in fact, in several places in this chapter and throughout the book it is shown how each interaction between client tools and the instance of Analysis Services results in the generation of MDX. You not only see the MDX that is generated, but also glean some insight as to what the MDX does.

SQL Server 2005 provides a sample Analysis Services project that contains the majority of the features supported by Analysis Services 2005. In this chapter you will use the sample Analysis Services project shipped with the product to learn MDX. The illustrations are limited to three dimensions for you to more easily understand the concepts. You can extend these concepts if you want to view data across additional dimensions. In this chapter you learn the basic concepts regarding cells, members, tuples, and sets. In addition, you learn how to create MDX expressions and MDX queries for data analysis from Analysis Services databases.

What Is MDX?

Just as SQL (Structured Query Language) is a query language used to retrieve data from relational databases, MDX (Multi-Dimensional eXpressions) is a query language used to retrieve data from multi-dimensional databases. More specifically, MDX is used for querying data from OLAP databases with Analysis Services and supports two distinct modes. When used as an expression, it can define and manipulate multidimensional objects and data to calculate values. As a query language, it is used to retrieve data from Analysis Services databases. MDX was originally designed by Microsoft and introduced along with Analysis Services 7.0 in 1998.

MDX is not a proprietary language; it is a standards-based query language used to retrieve data from OLAP databases. MDX is part of the OLEDB for OLAP specification sponsored by Microsoft. Many other OLAP providers support MDX, including Microstrategy's Intelligence Server, Hyperion's Essbase Server, and SAS's Enterprise BI Server. There are those who wish to extend the standard for additional functionality, and MDX extensions have indeed been developed by individual vendors. MDX extensions provide functionality not resident in the standard, but the constituent parts of any extension are expected to be consistent with the MDX standard. Analysis Services 2005 does provide several extensions to the standard MDX defined by the OLEDB for OLAP specification. In this book you learn about the form of MDX supported by Analysis Services 2005.

When one refers to MDX they might be referring either to the MDX query language or to MDX expressions. Even though the MDX query language has similar syntax as that of SQL, it is significantly different. Nonetheless, we will use SQL to teach you some MDX — we're not proud. Before you get into the details of MDX query language and MDX expressions, you need to learn some fundamental concepts.