Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 175

Chapter 6
Querying Data Warehouses
Just as SQL is a language for manipulating relational databases, MDX
(Multi-Dimensional eXpressions) is a language for defining and querying
multidimensional databases. Although at first sight it may appear that MDX
resembles SQL, they are significantly different from each other. While SQL
operates over tables, attributes, and tuples, MDX works over data cubes,
dimensions, hierarchies, and members (at the instance level). MDX is a de
facto standard supported by many OLAP tool providers.
MDX supports two distinct modes. On the one hand, it can be used as an
expression language to design OLAP cubes, that is, to define and manipulate
data in order to calculate values, to add business logic to the cubes, to define
custom roll-ups and actions, to define security settings, and so on. On the
other hand, it can be used as a query language to retrieve data from cubes.
In this chapter, we address MDX as a query language.
In Sect. 6.1 , we describe the main functionalities of MDX. Starting
from simple queries, we progressively introduce more complex features,
like navigation capabilities that are used to drill down or roll up along
hierarchies. Functions that are used to analyze time series are also discussed.
Finally, aggregation functions are addressed, going from the typical ones to
more involved functions like moving averages, for instance. We apply all
these functions and concepts in Sect. 6.2 , where we query the Northwind
data cube using MDX. Generally speaking, MDX queries over cubes can
also be expressed as SQL queries over the underlying data warehouse.
Thus, in Sect. 6.3 we write the same queries presented in Sect. 6.2 as SQL
queries over the Northwind data warehouse instead of over the Northwind
multidimensional data cube. In Sect. 6.4 , we compare the main features of
both languages, based on an analysis of the alternatives discussed in the two
formerly mentioned sections.