Comparison of SQL and MDX Syntax

          Comparison of SQL and MDX Syntax
The Multidimensional Expressions (MDX) syntax is similar to the syntax of Structured Query Language (SQL). In many ways, the functionality supplied by MDX is also similar to that of SQL; with effort, you can even duplicate some of the functionality provided by MDX in SQL.
However, there are some striking differences between SQL and MDX. Here we provide a guide to these conceptual differences between SQL and MDX, from the perspective of an SQL developer.
Dimensions
The principal difference between SQL and MDX is the ability of MDX to reference multiple dimensions. Although it is possible to use SQL exclusively to query cubes in SQL Server 2000, MDX provides commands that are designed specifically to retrieve data as multidimensional data structures with almost any number of dimensions.
SQL refers to only two dimensions when processing queries: columns and rows. Because SQL was designed to handle only two-dimensional tabular data, the terms "column" and "row" have meaning in SQL syntax.
MDX, in comparison, can process one, two, three, or more dimensions in queries. Each dimension is referred to as an axis. The terms "column" and "row" in MDX are used only as aliases for the first two axis dimensions in an MDX query. Other dimensions are also aliased, but the aliases "column" and "row" hold no meaning to MDX. MDX supports such aliases for display purposes; many OLAP tools are incapable of displaying a result set with more than two dimensions. MDX can even process "zero-axis" queries that return only one cell from a cube, determined by a tuple constructed from the default member of each dimension in the cube.
SELECT and WHERE Clauses
In SQL, the SELECT clause is used to define the column layout for a query, and the WHERE clause is used to define the row layout. However, in MDX the SELECT clause can be used to define several axis dimensions, while the WHERE clause is used to r