What is MDX? 

MDX stands for 'multi-dimensional expressions'. It is the main query language implemented by Mondrian.

MDX was introduced by Microsoft with Microsoft SQL Server OLAP Services in around 1998, as the language component of the OLE DB for OLAP API. More recently, MDX has appeared as part of the XML for Analysis API. Microsoft proposed MDX as a standard, and its adoption among application writers and other OLAP providers is steadily increasing.

What is the syntax of MDX? 

A basic MDX query looks like this:

SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,
  {[Product].members} ON ROWS
FROM [Sales]
WHERE [Time].[1997].[Q2]

It looks a little like SQL, but don't be deceived! The structure of an MDX query is quite different from SQL.

Since MDX is a standard language, we don't cover its syntax here. (The Microsoft SQL Server site has an MDX specification; there's also a good tutorial in Database Journal.) This specification describes the differences between Mondrian's dialect and the standard dialect of MDX.

Mondrian-specific MDX 

StrToSet and StrToTuple 

The StrToSet() and StrToTuple() functions take an extra parameter.

Parsing 

Parsing is case-sensitive.

Parameters 

Pseudo-functions Param() and ParamRef() allow you to create parameterized MDX statements.

Cast operator 

The Cast operator converts scalar expressions to other types. The syntax is

Cast(<Expression> AS <Type>)

where <Type> is one of:

• BOOLEAN
• NUMERIC
• DECIMAL
• STRING

For example,

Cast([Store].CurrentMember.[Store Sqft], INTEGER)

returns the value of the [Store Sqft] property as an integer value.

IN and NOT IN 

IN and NOT IN are Mondrian-specific functions. For example:

SELECT {[Measures].[Unit Sales]} ON COLUMNS,
   FILTER([Product].[Product Family].MEMBERS,
          [Product].[Product Family].CurrentMember NOT IN
              {[Product].[All Products].firstChild,
               [Product].[All Products].lastChild}) ON ROWS
FROM [Sales]

MATCHES and NOT MATCHES 

MATCHES and NOT MATCHES are Mondrian-specific functions which compare a string with a Java regular expression. For example, the following query finds all employees whose name starts with 'sam' (case-insensitive):

SELECT {[Measures].[Org Salary]} ON COLUMNS,
   Filter({[Employees].MEMBERS},
          [Employees].CurrentMember.Name MATCHES '(?i)sam.*') ON ROWS
FROM [HR]

Visual Basic for Applications (VBA) functions 

Since the first implementation of MDX was as part of Microsoft SQL Server OLAP Services, the language inherited the built-in functions available in that environment, namely the Visual Basic for Applications (VBA) specification. This specification includes functions for conversion (CBool, CInt, IsNumber), arithmetic (Tan, Exp), finance (NPer, NPV), and date/time (DatePart, Now). Even though Mondrian cannot interface with Visual Basic, it includes a large number of VBA functions to allow MDX queries written in a Microsoft environment to run unchanged.

This document describes which VBA functions are available in mondrian; for more detailed descriptions of all VBA functions, see Visual Basic Functions. Note that that document includes some VBA functions which are not implemented in mondrian.

Comments 

MDX statements can contain comments. There are 3 syntactic forms for comments:

// End-of-line comment

-- End-of-line comment

/* Multi-line
comment */

Comments can be nested, for example

/* Multi-line
comment /* Comment within a comment */
*/

Format Strings 

Every member has a FORMAT_STRING property, which affects how its raw value is rendered into text in the user interface. For example, the query

WITH MEMBER [Measures].[Profit] AS '([Measures].[Store Sales] - [Measures].[Store Cost])',
FORMAT_STRING = "$#,###.00"
SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,
{[Product].CurrentMember.Children} ON ROWS
FROM [Sales]

yields cells formatted in dollar and cent amounts.

Members defined in a schema file can also have format strings. Measures use the formatString attribute:

<Measure name="Store Sales" column="store_sales" aggregator="sum" formatString="#,###.00"/>

and calculated members use the <CalculatedMemberProperty> sub-element:

<CalculatedMember name="Profit" dimension="Measures" formula="[Measures].[Store Sales] - [Measures].[Store Cost]">
  <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>

Format strings use Visual Basic formatting syntax; see class mondrian.olap.Format for more details.

A measure's format string is usually a fixed string, but is really an expression, which is evaluated in the same context as the cell. You can therefore change the formatting of a cell depending upon the cell's value.

The format string can even contain 'style' attributes which are interpreted specially by JPivot. If present, JPivot will render cells in color.

The following example combines a dynamic formula with style attributes. The result is that cells are displayed with green background if they are less than $100,000, or a red background if they are greater than $100,000:

WITH MEMBER [Measures].[Profit] AS
   '([Measures].[Store Sales] - [Measures].[Store Cost])',
  FORMAT_STRING = Iif([Measures].[Profit] < 100000, '|#|style=green', '|#|style=red')
SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,
  {[Product].CurrentMember.Children} ON ROWS
FROM [Sales]

Order of sets

MDX sets are ordered and may contain duplicates. (Both of these properties are at odds with the mathematical definition of 'set', so it would have been better if they were called 'lists', but we're stuck with the term 'set'.)

For most functions that return sets, Microsoft's documentation for SQL Server Analysis Services 2008 (the de facto MDX standard) does not specify the order of elements in the result set, and one might assume that MDX server could return the results in any order and still comply with the standard. However, Mondrian's implementation of MDX gives stronger guarantees: a function's result set will be in the obvious order.

For most functions, the definition of 'obvious' is obvious, so we won't spell it out in detail. For example, Filter returns elements in the same order as the set expression; Crossjoin returns the results in the order of the first set expression, then within that, by the second second expression. Similarly Generate, Union, Except. The sorting functions (Order, TopCount, BottomCount, TopPercent, Hierarchize, etc.) use a stable sorting algorithm. Metadata methods such as <Hierarchy>.Members return their results in natural order.

If you do not care about the order of results of a set expression (say because you are sorting the results later), wrap the expression the Unorder function, and mondrian may be able to use a more efficient algorithm that does not guarantee order.

Function List

These are the functions implemented in the current Mondrian release.

Author: Julian Hyde; last modified September 2008.
Version: $Id: //open/mondrian/doc/mdx.html#21 $ (log)
Copyright (C) 2005-2008 Julian Hyde