Background
The MultiDimensional eXpressions (MDX) language provides a specialized syntax for querying and manipulating the multidimensional data stored inHistory
MDX was first introduced as part of themdXML
In 2001 the
tag.
MDX data types
There are six primary data types in MDX * Scalar. Scalar is either aAggregate
(number), UniqueName
(string), .Value
(number or string) etc.
* Dimension/Hierarchy. Dimension is a ime/code> or it can be returned by an MDX function, e.g. .Dimension
. Hierarchy is a dimension
In physics and mathematics, the dimension of a mathematical space (or object) is informally defined as the minimum number of coordinates needed to specify any point within it. Thus, a line has a dimension of one (1D) because only one coor ...
hierarchy
A hierarchy (from Greek: , from , 'president of sacred rites') is an arrangement of items (objects, names, values, categories, etc.) that are represented as being "above", "below", or "at the same level as" one another. Hierarchy is an important ...
of a cube. It can be specified by its unique name, e.g. ime iscal/code> or it can be returned by an MDX function, e.g. .Hierarchy
. Hierarchies are contained within dimensions. (''OLEDB for OLAP MDX specification does not distinguish between dimension and hierarchy data types. Some implementations, such as Microsoft Analysis Services, treat them differently.'')
* Level. Level is a level
Level or levels may refer to:
Engineering
*Level (instrument), a device used to measure true horizontal or relative heights
*Spirit level, an instrument designed to indicate whether a surface is horizontal or vertical
*Canal pound or level
*Regr ...
in a dimension hierarchy. It can be specified by its unique name, e.g. ime iscal onth/code> or it can be returned by an MDX function, e.g. .Level
.
* Member. Member is a member in a dimension hierarchy. It can be specified by its unique name, e.g. ime iscal onth ugust 2006/code>, by qualified name, e.g. ime iscal 006 3 ugust 2006/code> or returned by an MDX function, e.g. .PrevMember
, .Parent
, .FirstChild
etc. Note that all members are specific to a hierarchy. If the self-same product is a member of two different hierarchies ( roduct yManufacturer/code> and roduct yCategory/code>), there will be two different members visible that may need to be coordinated in sets and tuples (see below).
* Tuple. Tuple is an ordered collection of one or more members from different dimensions. Tuples can be specified by enumerating the members, e.g. ( ime iscal onth ugust ustomery Geography
Y, or y, is the twenty-fifth and penultimate letter of the Latin alphabet, used in the modern English alphabet, the alphabets of other western European languages and others worldwide. According to some authorities, it is the sixth (or seventh ...
ll Customers
Ll/ll is a digraph that occurs in several languages
English
In English, often represents the same sound as single : . The doubling is used to indicate that the preceding vowel is (historically) short, or that the "l" sound is to be extended ...
SA easures ales
or returned by an MDX function, e.g. .Item
.
* Set. Set is an ordered collection of tuples with the same dimensionality, or hierarchality in the case of Microsoft's implementation. It can be specified enumerating the tuples, e.g.
or returned by MDX function or operator, e.g. Crossjoin
, Filter
, Order
, Descendants
etc.
* Other data types. Member properties are equivalent to ''attributes'' in the data warehouse sense. They can be retrieved by name in a query through an axis PROPERTIES clause of a query. The scalar data value of a member property for some member can be accessed in an expression through MDX, either by naming the property (for example, roductCurrentMember.ales Price Ales may refer to:
Places
* Alès, a town and commune in southern France
* Ales, Sardinia, a small town in the province of Oristano on Sardinia in Italy
People with the surname
* Alexander Ales (1500–1565), Scottish theologian
* Mikoláš AleŠ...
/code>) or by using a special access function (for example, roductCurrentMember.Properties("Sales Price")
). In limited contexts, MDX allows other data types as well - for example Array
An array is a systematic arrangement of similar objects, usually in rows and columns.
Things called an array include:
{{TOC right
Music
* In twelve-tone and serial composition, the presentation of simultaneous twelve-tone sets such that the ...
can be used inside the SetToArray
function to specify an array that is not processed by MDX but passed to a user-defined function in an ActiveX library. Objects of other data types are represented as scalar strings indicating the object names, such as measure group name in Microsoft's MeasureGroupMeasures
function or KPI name in for example Microsoft's KPIValue
or KPIGoal
functions.
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.
SELECT
ON COLUMNS,
ON ROWS
FROM Sales
WHERE ( tore
Tore is a Scandinavian masculine name. It is derived from the Old Norse name ''Thórir'', which is composed of ''thorr'' which means thunder, and ''arr'' which means warrior. So Thunder Warrior or Thor's Warrior. The most famous person by this nam ...
SA A)
In this example, the query defines the following result set information
* The SELECT clause sets the query axes as the Store Sales member of the Measures dimension, and the 2002 and 2003 members of the Date dimension.
* The FROM clause indicates that the data source is the Sales cube.
* The WHERE clause defines the "slicer axis" as the California member of the Store dimension.
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 the reserved words and does not otherwise contain any characters other than letters, numbers or underscores.
SELECT
easurestore Sales
Tore is a Scandinavian masculine name. It is derived from the Old Norse name ''Thórir'', which is composed of ''thorr'' which means thunder, and ''arr'' which means warrior. So Thunder Warrior or Thor's Warrior. The most famous person by this nam ...
ON COLUMNS,
ate
Ate or ATE may refer to:
Organizations
* Active Training and Education Trust, a not-for-profit organization providing "Superweeks", holidays for children in the United Kingdom
* Association of Technical Employees, a trade union, now called the Nat ...
Members ON ROWS
FROM Sales
WHERE ( tore
Tore is a Scandinavian masculine name. It is derived from the Old Norse name ''Thórir'', which is composed of ''thorr'' which means thunder, and ''arr'' which means warrior. So Thunder Warrior or Thor's Warrior. The most famous person by this nam ...
SA A)
References
Further reading
* George Spofford, Sivakumar Harinath, Chris Webb, Dylan Hai Huang, Francesco Civardi: ''MDX-Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase''. Wiley, 2006,
* Mosha Pasumansky
Mosha Pasumansky is one of the inventors of the MultiDimensional eXpressions (MDX) language, a query language for online analytical processing (OLAP) databases. Pasumansky is also one of the architects of the Microsoft Analysis Services, and an OL ...
, Mark Whitehorn, Rob Zare: ''Fast Track to MDX''.
* Larry Sackett: ''MDX Reporting and Analytics with SAP NetWeaver BW''. SAP Press, 2008, 978-1-59229-249-3
External links
Multidimensional Expressions (MDX) Reference
from Microsoft
Microsoft Corporation is an American multinational technology corporation producing computer software, consumer electronics, personal computers, and related services headquartered at the Microsoft Redmond campus located in Redmond, Washing ...
Docs
{{Authority control
Online analytical processing
Query languages