Some queries can be easily expressed with MDX while it’s not possible to get the same result using SQL. For example, if we use Adventure Works sample cube and would like to display internet gross profits by country in a way so that we have separate column for each year it can be easily achieved with below MDX.

select
  non empty [Date].[Calendar Year].[Calendar Year] on 0,
  [Customer].[Country].[Country] * [Measures].[Internet Gross Profit] on 1
from [Adventure Works]

It displays column for each year which has data for internet gross profits. In SQL, however, all columns must be explicitly listed in a query (or derived from table structure in case of select *) – whether in select list or in pivot clause. Generically speaking, SQL is designed to return two-dimensional datasets with arbitrary number of rows and pre-defined number of columns while MDX can return data on multiple axes (dimensions) although it’s a tricky question how to visualize the output if we use more than 2 axes.

On the other hand, sometimes it quite challenging to implement the logic in MDX which can be expressed using SQL in a very trivial manner. Let’s consider a couple of cases.
Continue reading →