MultiDimensional eXpressions

Multidimensional Expressions (MDX) is a query language for OLAP databases, much like SQL is a query language for relational databases. It is also a calculation language, with syntax similar to spreadsheet formulas.

Background

The MultiDimensional eXpressions (MDX) language provides a specialized syntax for querying and manipulating the multidimensional data stored in OLAP cubes.[1] While it is possible to translate some of these into traditional SQL, it would frequently require the synthesis of clumsy SQL expressions even for very simple MDX expressions. MDX has been embraced by a wide majority of OLAP vendors and has become the standard for OLAP systems.

History

MDX was first introduced as part of the OLE DB for OLAP specification in 1997 from Microsoft. It was invented by the group of SQL Server engineers including Mosha Pasumansky. The specification was quickly followed by commercial release of Microsoft OLAP Services 7.0 in 1998 and later by Microsoft Analysis Services. The latest version of the OLE DB for OLAP specification was issued by Microsoft in 1999.

While it was not an open standard, but rather a Microsoft-owned specification, it was adopted by the wide range of OLAP vendors. This included both vendors on the server side such as Applix, icCube, MicroStrategy, NCR, Pentaho, Oracle Corporation, SAS, SAP, Teradata, Symphony Teleca, ActivePivot and vendors on the client side such as Panorama Software, Pentaho, PowerOLAP, XLCubed, Proclarity, AppSource, Jaspersoft, Cognos, Business Objects, Brio Technology, Crystal Reports, Microsoft Excel, Tagetik, and Microsoft Reporting Services.

With the invention of XML for Analysis, which standardized MDX as a query language, even more companies - such as Hyperion Solutions - began supporting MDX.

The XML for Analysis specification referred back to the OLE DB for OLAP specification for details on the MDX Query Language. In Analysis Services 2005, Microsoft has added some MDX Query Language extensions like subselects. Products like Microsoft Excel 2007 have started to use these new MDX Query Language extensions. Some refer to this newer variant of MDX as MDX 2005.

mdXML

In 2001 the XMLA Council released the XML for Analysis standard, which included mdXML as a query language. In the current XMLA 1.1 specification, mdXML is essentially MDX wrapped in the XML <Statement> tag.

MDX data types

There are six primary data types in MDX

Example query

The following example, adapted from the SQL Server 2000 Books Online, shows a basic MDX query that uses the SELECT statement. This query returns a result set that contains the 2002 and 2003 store sales amounts for stores in the state of California.

  1. SELECT

  2.    { [Measures].[Store Sales] } ON COLUMNS,

  3.    { [Date].[2002], [Date].[2003] } ON ROWS

  4. FROM Sales

  5. WHERE ( [Store].[USA].[CA] )

In this example, the query defines the following result set information

Note: You can specify up to 128 query axes in an MDX query.

If you create two axes, one must be the column axis and one must be the row axis, although it doesn't matter in which order they appear within the query. if you create a query that has only one axis, it must be the column axis. The square brackets around the particular object identifier are optional as long as the object identifier: is not one of reserved words, does not otherwise contain any characters other than letters, numbers or underscores.

  1. SELECT

  2.     [Measures].[Store Sales] ON COLUMNS,

  3.     [Date].Members ON ROWS

  4. FROM Sales

  5. WHERE ( [Store].[USA].[CA] )

The Members() function returns the set of members in a dimension, level or hierarchy.[2]

References

  1. ↑ Carl Nolan. "Manipulate and Query OLAP Data Using ADOMD and Multidimensional Expressions". Microsoft. Retrieved 2008-03-05.
  2. ↑ learn-with-video-tutorials. "MDX Introduction - video tutorials". www.learn-with-video-tutorials.com. Retrieved 2013-10-14.

External reference