Ranet OLAP

MDX Definition

Multidimensional Expressions or MDX is a calculation/query language to express queries for online analytical processing - OLAP, in a database management system. MDX is an SQL extension to query data stored in a multidimensional structure. As for multidimensional databases they relate to OLAP cubes intended for reporting and analysis.

What is MDX query? The MDX query basics contribute to working with dimensions, hierarchies, members etc. in the OLAP application.

The MDX queries contribute to working with dimensions, hierarchies, members etc. in the OLAP application.
It’s possible to exploit MDX to query data maintained in an SQL Server. As a result, an MDX expression returns a dataset containing cell and axis data. The main MDX query sample is the statement SELECT. The MDX statement determines a result set to return, possesses specific syntax, and composes the MDX query.

Multidimensional expressions

Short History of MDX

The MDX language was developed in the 1990s in Panorama company which was later taken over by Microsoft. Thereby it is generally accepted that Microsoft designed the multidimensional expressions language setting it a proprietary standard for Microsoft production. MDX was rapidly applied by multiple companies developing multidimensional databases, OLAP systems as later Microsoft renounced its control of the MDX standard. Nowadays the MDX language constitutes a non-proprietary standard. Nowadays the MDX query language constitutes a non-proprietary standard.

Data Classification

Multidimensional Expressions include several types of OLAP data to work with and return through the MDX queries:

  • Scalar is either a number or a string.
  • Dimension is a dimension in a cube and it’s the initial part to arrange measure and attribute. Dimensions are organized independently. They include members arranged in hierarchies whilst hierarchies contain levels.
  • Hierarchy is primarily a dimension hierarchy and is organized inside dimensions and can’t be determined by unique names.
  • Level belongs to a dimension hierarchy and in its turn is organized in a dimension hierarchy.
  • Members belong to a dimension hierarchy and are arranged in an OLAP dimension hierarchy. They can be determined by unique names like WITH member in MDX query. All levels are specified depending on a hierarchy hence there can be 2 various members to coordinate in sets if one member belongs to 2 various hierarchies.
  • Tuple is a collection of members from various dimensions and can be determined by several members.
  • Set in its turn is a collection of tuples from identical dimensions/hierarchies.
  • Other: there are also member properties as an alternative to data attributes.

Data Retrieval

In order to retrieve the requisite data one has to enter MDX queries into the system connected with a multidimensional database - OLAP. The MDX query doesn’t determine one object in the group but typically a number of objects reply the query though with various relevance degree. Thereby the results or replies of MDX are usually ranked in the way they match the information needed by a user. According to the system used the MDX database objects can be images, text docs, audio, videos etc.

Select Statement

As it was mentioned previously, the basic query in MDX is the SELECT statement. The SELECT statement specifies a data set, which contains a subset of multidimensional data. The SELECT keyword here points the query beginning and specifies what exactly you want to select. The query can be both simple and complex.

A simple MDX query example can be as following:

1
2
3
4
SELECT
FROM
[Adventure Works]
CELL PRORERTIES BACK_COLOR, CELL_ORDINAL, FORE_COLOR, FONT_NAME, FONT_SIZE, FONT_FLAGS, FORMAT_STRING, VALUE, FORMATTED-VALUE, UPDATEABLE, ACTION_TYPE

As a result, we see the total amount from all the categories:

simple MDX query example

More complex SELECT query must contain the following info:

  • Axes quantity – in a single query you can specify up to 128 axes;
  • A list of dimension members for each axis;
  • The name of the cube to which the query is made;
  • The list of slice members.

Thus, the example of more complex query can be the following:

1
2
3
4
SELECT
{[Measures].[Internet Sales Amount]} ON 0,
Non empty {[Product].[Product Categories].[Category].Members} ON 1
FROM [Adventure Works]

And here, as a result we see amounts according to the categories we specified in the query:

complex MDX query

It is also important to define axis dimensions. We build them when defining a SELECT statement. A SELECT statement specifies a set for each dimension; COLUMNS, ROWS, and additional axes. Axis dimensions retrieve and save data for multiple members, not just single ones.

MDX Query Structure

As the SELECT statement in MDX specifies a result set that contains a subset of multidimensional data returned from cube, to define this set an MDX query must contain the following clauses:

  • WITHclause: allows to calculate the named sets during the processing of the SELECT and WHERE clauses;
  • SELECTclause: identifies which dimension members will be included in each axis for the MDX query structure;
  • FROMclause: names the queried cube and determines which multidimensional data source to use for filling MDX SELECT statement result set;
  • WHEREclause: defines which dimension or member is used as a slicer dimension (the slicer usually refers to the axis formed by the WHERE clause).

Let’s take a closer look of MDX query at the following example:

MDX query

MDX Query Syntex

The basic SELECT statement syntax can have the following form:

1
2
3
4
5
SELECT [< axis_specification>
       [, < axis_specification>...]]
  FROM [< cube_specification>]
[WHERE
[< slicer_specification>]]

With the next meanings:

  • axis_specification — contains cube axes description;
  • cube_specification — contains cube name;
  • slicer_specification — contains cube slicer description.

The following syntax guidelines could be useful when generating and editing MDX query:

  • The member name must be enclosed in a set of square brackets ([])
  • We should balance the brackets used in MDX queries like: [ ], ( ), and { }.
  • Single and double quotation marks should be balanced too.

Ranet OLAP MDX Query Builder

You can meet MDX in BI Ranet OLAP. This perfect MDX tool is called Query builder. It is available for both IT-specialist (or users with technical skills) and for users without any technical background. It has two modes and suits two groups of users according to their skills:

  • Design mode: in this mode the MDX query is generated automatically by the program;
  • Custom Query mode: the query can be generated by the user manually and the system won’t change it.

Ranet OLAP MDX Query Builder

There are several aspects to remember when using Ranet OLAP MDX query builder and working with MDX in Ranet OLAP software.

The tuple based on settings in the Rows and Columns areas is framed by Hierarchize function for each field. The function is used for each axis (columns and rows);

Several properties are automatically included in the MDX query:
Dimension properties: PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0, MEMBER_TYPE

Cell properties: BACK_COLOR, CELL_ORDINAL, FORE_COLOR, FONT_NAME, FONT_SIZE, FONT_FLAGS, FORMAT_STRING, VALUE, FORMATTED_VALUE, UPDATEABLE, ACTION_TYPE
All other properties have to be added manually.

If you use Non Empty for the axes in the custom query mode, the state of the corresponding buttons on the Toolbar has to be set accordingly. For example, if you use Non Empty for the columns axis in the MDX query, the Hide empty columns button has to be active.

The pivot table can be initialized using a subcube that limits the data slice used for the report. Filter settings from the Filters area form an embedded cube that is automatically added to the subcube. If there is only one element in the filter settings for the column, it additionally will be added to the Where condition in the MDX expression. Multiple filters are only added to the embedded cube.

Filters applied to cells in the visible rows and columns together with the filters in the Filters area form the embedded cube (subcube). Filters set for cells in the visible rows and columns cannot be added to the Where condition in the MDX query. Only hierarchies that are not on the visible axes of the pivot table (rows and columns axes) can be added to the Where condition.

If there is only one measure in the Data area, it is included in the Where condition in the MDX query. The measure will not appear in the names of rows and columns in the pivot table (See example from Ranet for HTML below).

It’s possible to work with multidimensional expressions in online demos of Ranet OLAP in HTML and Silverlight using OLAP MDX tutorial if needed.

 



Write US