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

As can be seen, the sum of the SalesAmount measure is multiplied by the
percentage to account for the double-counting problem.
6.4 Comparison of MDX and SQL
In the two preceding sections, we used MDX and SQL for querying the
Northwind cube. In this section, we compare the two languages.
At a first glance, the syntax of both languages seems similar. As we
have shown, the functionality of both languages is also similar. Indeed, we
expressed the same set of queries in both languages. However, there are some
fundamental differences between SQL and MDX that we discuss next.
The main 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, MDX provides commands that are designed specifically
to retrieve multidimensional data with almost any number of dimensions.
On the other hand, SQL refers to only two dimensions, columns and
rows. Nevertheless, this fundamental difference between the two languages
somehow disappears since most OLAP tools are incapable of displaying a
result set with more than two dimensions. In our example queries, we used
the cross join operator to combine several dimensions in one axis when we
needed to analyze measures across more than two dimensions.
In SQL, the SELECT clause is used to define the column layout for a
query. However, in MDX the SELECT clause is used to define several axis
dimensions.
In SQL, the WHERE clause is used to filter the data returned by a
query, whereas in MDX, the WHERE clause is used to provide a slice of
the data returned by a query. While the two concepts are similar, they are
not equivalent. In an SQL query, the WHERE clause contains an arbitrary
list of items, which may or may not be returned in the result set, in order
to narrow down the scope of the data that are retrieved. In MDX, however,
the concept of a slice implies a reduction in the number of dimensions, and
thus, each member in the WHERE clause must identify a distinct portion of
data from a different dimension. Furthermore, unlike in SQL, the WHERE
clause in MDX cannot filter what is returned on an axis of a query. To filter
what appears on an axis of a query, we can use functions such as FILTER ,
NONEMPTY ,and TOPCOUNT .
Let us compare the queries in MDX of Sect. 6.2 with those in SQL of
Sect. 6.3 .
Consider Query 6.1. A first observation is that, in SQL, the joins between
tables must be explicitly indicated in the query, whereas they are implicit in
MDX. Also, in SQL, an inner join will remove empty combinations, whereas
in MDX, NON EMPTY must be specified to achieve this. On the other hand,
outer joins are needed in SQL if we want to show empty combinations.