MDX by George Hernandez

Multidimensional Expressions (MDX) is a language used to access and manipulate multidimensional (MD) information that is analagous to how SQL is used to access relational data.

MDX Uses
Basic MDX Syntax
Miscellany

MDX Uses

MDX is used to make either MDX Statements or MDX Expressions:

MDX Statement.
- Query language for MD structures, just as SQL is a query language for relational structures.
- Returns a MD set of data.
- Usually consumed by MD client applications.
MDX Expression
- Formula for MD, just as spreadsheets have formulas.
- Returns a single value: either a number, a string, or a null.
- Usually consumed by MDX statements, or otherwise used to make calculated members, default members, dimension properties, and cube properties.

Basic MDX Syntax

Here is the basic syntax for an MDX SELECT statement. A dimension can only be specified in an axis or the slicer but not both and not in multiple axes.

[WITH FormulaSpec1[... FormulaSpecN]
SELECT [AxisSpecs]
  FROM [Cube]
[WHERE [SlicerSpecs]]
[CellProperties]

The FormulaSpecs specfiy calculated or temporary members that can be used elsewhere in the MDX statement. Here is the syntax for a formula spec:

MEMBER ParentMember.MemberName AS 'ValueExpression'[, SOLVE ORDER = M][, CellProperty=ValueExpression ...]

The AxisSpecs specify the "edges" of a MD result set. An edge is a set assigned to an axis. The first five axes can be referenced by name or index (EG: AXIS(0) is COLUMNS), the rest are referenced by index. Up to 128 axes can be specified. The axes can be presented in any order but an axis cannot be skipped over, i.e. you can't present just Axis(0) and Axis (2). The axes are the mechanism for "dicing" the data set. Here is the syntax for axes:

[NON EMPTY] {Set} [DimensionProperties] ON {COLUMNS | ROWS | PAGES | SECTIONS | CHAPTERS | AXIS(Index)}, ...

Optionally the MDX keywords of NON EMPTY can be used to hide (i.e. not return) empty cells.

Optionally DimensionProperties, which can extend member information, may be included. Here is the syntax for DimensionProperties.

[DIMENSION] PROPERTIES property1 [, property2 ...]

The SlicerSpecs specify the "filter" of a MD result set. The SlicerSpecs must evaluate to a single tuple (i.e. not a set!). A set cannot be used in the WHERE clause. If a set is provided, then MDX will use the aggregate function on the set. The WHERE clause is the mechanism for "slicing" the data set. Note that the MDX function called "Filter" functions more like the SQL WHERE statement than the MDX WHERE statement. Here is the syntax for the WHERE statement:

WHERE tuple

Optionally CellProperties, which are used by MDX apps to visually represent the data, may be included. Here is the syntax for CellProperties.

CELL PROPERTIES property1 [, property2 ...]

EG Basic MDX query

The simplest possible MDX query has this syntax:

SELECT FROM MyCube

Here is another simple example:

WHERE MEMBER Measures.[Avg Price] AS 'Sales/[Units Sold]'
SELECT
   { [Measures].[Units Sold], [Measures].[Avg Price] } ON COLUMNS,
   { [Time].[1999], [Time].[2000] } ON ROWS
FROM Sales
WHERE ( [Store].[IL].[CA] )

Miscellany

Calculated Members do not store data in the cube but calculate values as needed. Calculated members are MDX expressions based upon existing measures and members. They are calculated at query time, after aggregations. Calculated members can slow query retrieval but they also do not require the cube to be reprocessed if the equation changes.

For a calculated measure that lasts for the duration of a session, use this syntax:
```
CREATE MEMBER CalculatedMemberTuple AS 'CalculatedMemberEquation'
```

For a calculated measure that lasts for the duration of a query, use this syntax:

WITH    MEMBER CalculatedMebmerTuple AS 'CalculatedMemberEquation' [, SOLVE_ORDER = n] ...
SELECT ....

Named Sets serve as aliases for sets.

For a calculated measure that lasts for the duration of a session, use this syntax:
```
CREATE SET SetAlias AS 'SetDefinition'
```
For a calculated measure that lasts for the duration of a query, use this synTax:
```
WITH    SET SetAlias AS 'SetDefinition'
SELECT ....
```

Comments in MDX are done in both SQL Server and c syntax.

-- . All text following until the end of the line are ignored.
// . All text following until the end of the line are ignored.
/* ... */ . All text between the pairs are ignored.

An OLAP object name must be bracketed if any of the following 3 conditions are met:

The name has a space. EG: [Net Profit].
The name starts with a number. EG: [2000].
The name is an MDX reserved word. EG: [Select].

Miscellany

The uniqueness of name and keys of members can be toggled on the dimension or level level.
A virtual cube can be created by joining other cubes.
Member properties is an attribute of a member. The values are related to columns in the fact table.
By default a cube has one partition with one data source. However a cube can be made up of multiple partitions, and each partition can have its own data source but the data structure of each must be the same.

Page Modified: (Hand noted: 2007-10-12 02:51:37Z) (Auto noted: 2007-11-17 06:46:00Z)