This page provides an overview of the MDX query, compares it to an SQL query, and describes the basic MDX query structure. It also includes a basic usage example for MDX using the Quartet ActivePivot OLAP server.

The general information in this page is taken from major developer sites on the web, with links to the original articles. This content is provided courtesy of Quartet FS, makers of the ActivePivot OLAP Server, a robust and powerful object-based tool that provides realtime analytics capabilities through its transactional engine and multi-threaded processing capabilities.

MDX Query Basics

Overview

The Multidimensional Expressions (MDX) language allows users to describe queries and manipulate multidimentional information, such as the data stored in cubes. MDX functions can define calculated members and query cube data.

Comparing an MDX Query to an SQL Query

The MDX and SQL languages have a similar structure, and include some of the same keywords. However, one of the significant conceptual differences between the two languages is that MDX builds a multidimensional view of the data, where SQL builds a relational view. Although it is possible to use SQL exclusively to query cubes, the MDX query was designed specifically to retrieve multidimensional data structures with almost any number of dimensions. Additionally, SQL was designed to handle only two-dimensional tabular data when processing queries, where MDX can process one, two, three, or more dimensions in queries. 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).

Additional to the conceptual differences between MDX and SQL, both languages use different terms to describe their basic concepts. For example, the cube concept explained above is actually a table in the SQL language; and the multidimentional term level is a column in SQL (referring to a string or discrete number). Additionally, a dimension in MDX refers to what would be understood in relational terms as several related columns or a dimension table; and a measure in MDX refers to a discrete, continuous or numeric column in SQL. Finally, a member in MDX refers to the specific row and column of a dimension table.

For more details on MDX query basics and MDX-SQL comparison, see the MDX for Everyone guide, or the official MDX documentation by MSN.

MDX Query Structure

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. Syntax errors might occur when a member name containing a single quotation mark is used for a calculated member in an MDX query. To prevent this, include an additional single quotation mark in the member name.
• 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.

For the full article regarding MDX query structure, see the SAS documentation website.

ActivePivot MDX Usage Example

The following example allows you to perform an MDX query, and get the Gross. Profit measure for brand LG and the category LCD on May 2011, broken down by an Amazon Standard Identification Number (ASIN). The results are displayed in a Pivot Table using Quartet FS's ActivePivot Real time OLAP server.

The ActivePivot OLAP server can aggregate data from multiple data sources, process high volumes of data, and analyze multi-dimensional events to display business metrics and key performance indicators (KPIs) with zero latency.

The dataset being queried in this example is Quartet FS's eCommerce demo project, which contains traffic data based on Amazon.

SELECT {[ASIN].[ASIN].Members} ON ROWS, NON EMPTY {[Category].[Category].[LCD]} ON COLUMNS FROM [Amazon] WHERE ([Measures].[Gross.Profit], [Time].[ALL].[AllMember].[2011].[5], [Brand].[Brand].[LG])

Using NON EMPTY, you can filter empty cells out:

SELECT NON EMPTY {[ASIN].[ASIN].Members} ON ROWS, NON EMPTY {[Category].[Category].[LCD]} ON COLUMNS FROM [Amazon] WHERE ([Measures].[Gross.Profit], [Time].[ALL].[AllMember].[2011].[5], [Brand].[Brand].[LG])

TOPCOUNT allows you to select the top n elements:

SELECT NON EMPTY TOPCOUNT({[ASIN].[ASIN].Members}, 5, ([Measures].[Gross.Profit], [Category].[Category].[LCD])) ON ROWS, NON EMPTY {[Category].[Category].[LCD]} ON COLUMNS FROM [Amazon] WHERE ([Measures].[Gross.Profit], [Time].[ALL].[AllMember].[2011].[5], [Brand].[Brand].[LG])

Here you take the top 5 ASINs, with regards to the LCD category (still in the context of LG and May 2011):

Finally, you can select the ASIN with the smallest gross profit, for which the cumulative Gross.Profit is less than 200k, using BOTTOMSUM:

SELECT NON EMPTY BOTTOMSUM({[ASIN].[ASIN].Members}, 200000, ([Measures].[Gross.Profit], [Category].[Category].[LCD])) ON ROWS, NON EMPTY {[Category].[Category].[LCD]} ON COLUMNS FROM [Amazon] WHERE ([Measures].[Gross.Profit], [Time].[ALL].[AllMember].[2011].[5], [Brand].[Brand].[LG])

back to top of page