MDX Basics

The Multidimensional Expressions (MDX) language allows users to describe queries and manipulate multidimentional information, such as the data stored in cubes.

The MDX query was designed specifically to retrieve multidimensional data structures with almost any number of dimensions.

Each dimension in MDX is referred to as an axis, and the terms column and row are simply used as aliases for the first two axis dimensions in an MDX query (the alias itself holds no real meaning to MDX).

A member in MDX refers to the specific row and column of a dimension table.

A basic MDX query uses the SELECT statement to identify a data set that contains a subset of multidimensional data. The SELECT statement is composed of the following clauses:

  • WITH clause (optional): Allows calculated members or named sets to be computed during the processing of the SELECT and WHERE clauses.
  • SELECT clause: Defines the axes for the MDX query structure by identifying the dimension members to include on each axis. The number of axis dimensions of an MDX SELECT statement is also determined by the SELECT clause.
  • FROM clause: Names the cube that is being queried, and determines which multidimensional data source will be used when extracting data to populate the result set of the MDX SELECT statement. The FROM clause (in an MDX query) can list only a single cube. Queries are restricted to a single data source or cube.
  • WHERE clause (optional): Determines which dimension or member is used as a slicer dimension (the slicer usually refers to the axis formed by the WHERE clause). This restricts the extracting of data to a combination of dimension members. Any dimension that does not appear on an axis in the SELECT clause can be named on the slicer.

An MDX query, and specifically the SELECT statement, can have up to 128 axis dimensions. The first five axes have aliases. Furthermore, an axis can be referred to by its ordinal position within an MDX query or by its alias.

The SELECT clause can use MDX functions to construct different members in a set on axes. The WITH clause can use MDX functions to construct calculated members, which are used in an axis or slicer.

There are six primary data types in MDX

  • Scalar. Scalar is either a number or a string. It can be specified as a literal, e.g. number 5 or string “OLAP” or it can be returned by an MDX function, e.g. Aggregate (number), UniqueName (string), .Value (number or string) etc.
  • Dimension/Hierarchy. Dimension is a dimension of a cube. A dimension is a primary organizer of measure and attribute information in a cube. MDX does not know of, nor does it assume any, dependencies between dimensions- they are assumed to be mutually independent. A dimension will contain some members (see below) organized in some hierarchy or hierarchies containing levels. It can be specified by its unique name, e.g. [Time] or it can be returned by an MDX function, e.g. .Dimension. Hierarchy is a dimension hierarchy of a cube. It can be specified by its unique name, e.g. [Time].[Fiscal] or it can be returned by an MDX function, e.g. .Hierarchy. Hierarchies are contained within dimensions. (OLEDB for OLAP MDX specification does not distinguish between dimension and hierarchy data types. Some implementations, such as Microsoft Analysis Services, treat them differently.)
  • Level. Level is a level in a dimension hierarchy. It can be specified by its unique name, e.g. [Time].[Fiscal].[Month] or it can be returned by an MDX function, e.g. .Level.
  • Member. Member is a member in a dimension hierarchy. It can be specified by its unique name, e.g. [Time].[Fiscal].[Month].[August 2006], by qualified name, e.g. [Time].[Fiscal].[2006].[Q3].[August 2006] or returned by an MDX function, e.g. .PrevMember, .Parent, .FirstChild etc. Note that all members are specific to a hierarchy. If the self-same product is a member of two different hierarchies ([Product].[ByManufacturer] and [Product].[ByCategory]), there will be two different members visible that may need to be coordinated in sets and tuples (see below).
  • Tuple. Tuple is an ordered collection of one or more members from different dimensions. Tuples can be specified by enumerating the members, e.g. ([Time].[Fiscal].[Month].[August], [Customer].[By Geography].[All Customers].[USA], [Measures].[Sales]) or returned by an MDX function, e.g. .Item.
  • Set. Set is an ordered collection of tuples with the same dimensionality, or hierarchality in the case of Microsoft’s implementation. It can be specified enumerating the tuples, e.g. {([Measures].[Sales], [Time].[Fiscal].[2006]), ([Measures].[Sales], [Time].[Fiscal].[2007])} or returned by MDX function or operator, e.g. Crossjoin, Filter, Order, Descendants etc.

References:

Reference 1 and the official MDX documentation by MSDN.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s