Multidimensional Expressions

In BI world such tools as OLAP and multidimensional data analysis still play the leading roles. The reasons are clear: they are fast, accurate and efficient. But how does it work on practise? The key to this knowledge is MDX query language. Today we would like to tell you in detail about its syntax, structure and of course provide MDX query examples plus bonus in the end.

MDX Definition

To begin with what does MDX stand for? Multidimensional Expressions or MDX is a calculation/query language to express queries for online analytical processing - OLAP, in a database management system. Multidimensional expression 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 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  expression returns a dataset containing cell and axis data. The main MDX query sample is the statement SELECT. The statement determines a result set to return, possesses specific syntax, and composes the MDX query.

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 MDX 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:

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:

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:

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:

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

Calculated members

One of the most popular questions regarding our topic is how to use a calculated member in MDX? Calculated member in MDX is based on other members and helps defining an expression which is attached to a new dimension's member. There are two basic types of them:

1. MDX calculated measure: as it is clear from the name based on other measures;
2. Non-measure MDX member: a group of records in the fact table which aggregates together the other non-measure members.

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 SELECT MDX 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:

There is also an opposite to WHERE clause called MDX subquery. This feature allows querying an MDX query instead of a cube and change the hierarchies structure. An example of subquery looks the following:

SELECT

FROM [NON VISUAL]

( SELECT [Geography].[Switzerland] ON 0 FROM [SALES] )

MDX Query Syntax

So in order to know how to write MDX query let’s learn its syntax firstThe basic SELECT statement syntax can have the following form:

SELECT [
       [,  ...]]
FROM []
[WHERE
[]]

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. It is called MDX Query builder tool. This perfect MDX tool 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 MDX builder;
• Custom Query mode: the query can be generated by the user manually and the system won’t change it.

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 next point: how to apply filter in MDX query? 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).

An example of how to create custom MDX calculation you can find in our article.

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

Top questions about MDX

As promised we prepared a bonus for you. We’d decided to get ahead of your questions and gathered the top of them here.

What is MDX query language? 

Multidimensional Expressions is a 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 the use of MDX query? 

The MDX query basics 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 expression returns a dataset containing cell and axis data.

What is MDX set and tuple? 

Basically, those elements are core in MDX language. MDX tuple is an ordered composition of members taken from different dimensions. A tuple is specified by commas and parentheses. It can be used either to refer to the cell value that the tuple indicates, or to indicate a combination of elements when used in a function.

MDX set is an ordered composition of tuples with the same dimensionality. The set is most often used for sizing axes and slices in a query, and therefore it can have only one tuple or in some occasions, it can be empty.

More info regarding these elements you can find in our article MDX members, sets and tuples.

What is axis in MDX query? 

The next logical question is what about axis? Avis is a function in MDX query which returns the set of tuples. The axes on which we have the results of the query do not necessarily have to coincide with the dimensions of the cube. We can arrange several measurements along one axis. .

What are MDX statements? 

There are four basic MDX statements:

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

What is crossjoin MDX? 

Crossjoin is MDX function which returns set’s (or several sets) cross product. The Crossjoin function returns the cross product of two or more specified sets. The tuples’ order in the result set depends on the order of the sets to be merged and the order of their members.

This document has been edited with the instant web content composer. The online instant HTML editor tools make a great resource that will help you a lot in your work. Save this link or add it to your bookmarks.