Questions tagged [mdx]

MDX is an OLAP query language used on Microsoft SQL Server Analysis Services (SSAS) and a number of other OLAP servers. The term is short for Multi-Dimensional eXpressions.

Although bearing a superficial resemblance to SQL, MDX works to a fundamentally different paradigm, and there are many significant anisomorphisms between the languages. For example, MDX has no explicit concept of a row within the language.

MDX is primarily concerned with calculations and operations that manipulate sets. A result set is a multi-dimensional structure, rather than rows and columns (although it is possible to emulate a tabular result). Queries select a set of dimension members to display on query axes. A special measures dimension allows measures to be selected.

The main parts of a MDX query are:

• A set of precursor definitions , with statements that define calculations, synthetic dimension members and sets. A MDX query does not necessarily have to have with statements, but they are normally used on queries of non-trivial complexity.

• a select statement selecting sets on each axes. A default measure may appear in each cell, or it may be explicitly selected as a part of the query on the axis. The set may be a set of tuples and can be hierarchical in nature.

• a slicer (or where) clause that restricts the query. It operates similarly to a definition of an axis with a single, but has slightly different semantics.

Anatomy of a MDX query:

Here is an example of a MDX query taken from a question on DBA.SE. We can see that although the syntax bears a superficial resemblance to SQL the underlying semantics are very different.

with 
member [Measures].[Delta]
    as [Measures].[Price] -
       (parallelperiod ([Date].[Calendar].[Month]
                       ,1
                       ,[Date].[Calendar].currentmember)
       ,[Measures].[Price])

This is defining a calculated member with a time-aware function (parallelperiod). A time hierarchy can be explicitly defined on a date dimension, which is then accessible to date-time aware functions such as parallelperiod, ytd and so forth. The measure is the difference between the value in a curent period and previous period.

member [Measures].[Rising]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] > 0))
      ,solve_order = 20

member [Measures].[Static]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] = 0))
      ,solve_order = 20

member [Measures].[Falling]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] < 0))
      ,solve_order = 20

These measures apply filters to members of the Reseller dimension based on the value of a computation. Note that the filter is happening at a slice (aggregate) level rather on a row by row basis.

select {[Measures].[Rising]
       ,[Measures].[Static]
       ,[Measures].[Falling]}
    on columns 

The select statement is selecting three measures on columns using the Measures pseudo dimension, producing a tabular result set. By selecting dimension members across the columns you can generate a cross-tabulation.

      ,[Product].[Product].Children
    on rows

And on the rows we are slicing by the products using the Children operator, which takes a specific level in a hierarchy and returns the children of that level.

  from [SalesTrends]

The cube we are selecting on is called SalesTrends

 where ([Date].[Calendar].[Month].[201202])

This is a slicer. Note that the expression is not an equality test. (i.e. not an evaluation of foo=bar). It is a definition of a dimension member.