MDX - Multi-Dimensional eXpressions - is a query language for OLAP databases, somewhat analogous to SQL. Indeed, MDX AND SQL often look so alike, you'd be forgiven for thinking of them as identical twins. Take the following MDX query, selecting total sales quantity for each product in IL:
SELECT
Quantity ON COLUMNS,
[All Product].Product ON ROWS
FROM Sales
WHERE State.[IL]
With SELECT, FROM and WHERE clauses, it appears friendly enough; but this familiarity is a double-edged sword. The truth is that the experienced SQL developer will often find themselves driven to the brink of frustration by the differences between MDX and SQL; and the anguished cries of "why can't I just..." are likely to be heard several miles away. In particular, the WHERE clause does NOT do quite what you think it does - it is a "slicer", identifying a single layer of your cube to return - it is NOT a "filter" to which you can attach SQL-style conditions! In the above example, it is saying "give me the slice of the cube where the state is IL", rather than "give me all the rows where the value of 'State' is equal to IL". (Note the lack of an "=" sign in the MDX!)
One important thing to note is that although MDX is multi-dimensional, you will in reality rarely work with more than the two basic axis - COLUMNS and ROWS.
The following examples are intended to present some basic MDX "recipies" with which a competent SQL developer can beging working with MDX. They are based on a simple "Sales" cube.
"Sales" Cube
Measures:
Dimensions:
- Customer Region (Customer Name -> City -> State)
- City + Population (Attribute)
- Product
- Order Date (Time Dimension)
Quantity by State/ Product
SELECT
[State].State ON COLUMNS,
[All Product].[Product] ON ROWS
FROM Sales
WHERE MEASURES.[Quantity]
This query can be represented by the diagram below. We are requesting that "States" be displayed as columns, and "Products" as rows; while (with the WHERE clause) we "slice off" just the "Quantity" Measure.
Quantity by State=IL/ Product
SELECT
[State].State.[IL] ON COLUMNS,
[All Product].[Product] ON ROWS
FROM Sales
WHERE MEASURES.[Quantity]
With this query, we introduce a further restriction - to return only the data from IL. Note that we do this not by adding to the WHERE clause, but by slicing the COLUMNS axis - zeroing-in on just the column pertaining to IL.
Quantity by State (StateID=2)/ Product
SELECT
[State].State.&[2] ON COLUMNS,
[All Product].[Product] ON ROWS
FROM Sales
WHERE MEASURES.[Quantity]
As with the query above, we select a single state by zeroing-in on just one slice of the COLUMNS axis; but to filter by Key rather than Name, we introduce the "&" (ampersand) character.
No Of Orders by Product/ Quarter, Excluding NULL
SELECT
[All Product].Product ON COLUMNS,
NONEMPTY ([Order Date].Quarter.Quarter) ON ROWS
FROM Sales
WHERE MEASURES.[No Of Orders]
In this query, we introduce the concept of "NONEMPTY" - telling the server to only return cells containing an actual value. This ensures, in this case, that Quarters for which there ARE no orders don't clutter-up our results-set.
Quantity by City With Population Between 100000 and 200000
SELECT
[Measures].[Quantity] ON COLUMNS,
FILTER
(
Descendants([City].[City], [City].[Population]),
[City].[Population].CurrentMember.MemberValue >= 100000
AND [City].[Population].CurrentMember.MemberValue <= 200000
)ON ROWS
FROM Sales
This query relies on the fact that the "City" Dimension has been assigned an Attribute of "Population".
We can then use the FILTER() function on the dimension to narrow the scope of our query - returning only those items in th first parameter which match the condition in the second parameter.
Quantity by States With Total Quantity > 100
WITH MEMBER [Measures].HighVolume
AS IIF([Measures].[Quantity] > 100, [Measures].[Quantity], NULL)
SELECT NONEMPTY ([State].[State]) ON COLUMNS
FROM Sales
WHERE [Measures].HighVolume
To execute this query, we introduce a temporary, calculated member to the cube, setting the value to NULL if the Quantity is less than 100. The NULL values are then excluded from the results-set using the NONEMPTY function.