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 the OLE DB for OLAP specification in 1997 frommdXML
In 2001 the XMLA Council released the XML for Analysis (XMLA) standard, which included mdXML as a query language. In the XMLA 1.1 specification, mdXML is essentially MDX wrapped in the XML
tag.
MDX data types
There are six primaryAggregate
(number), UniqueName
(string), .Value
(number or string) etc.
* Dimension/Hierarchy. Dimension is a ime
Ime is a village in Lindesnes municipality in Agder county, Norway. The village is located on the east side of the river Mandalselva, along the European route E39 highway. Ime is an eastern suburb of the town of Mandal. Ime might be considered to ...
/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 coord ...
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
In geometry, a cube is a three-dimensional solid object bounded by six square faces, facets or sides, with three meeting at each vertex. Viewed from a corner it is a hexagon and its net is usually depicted as a cross.
The cube is the only ...
. It can be specified by its unique name, e.g. ime
Ime is a village in Lindesnes municipality in Agder county, Norway. The village is located on the east side of the river Mandalselva, along the European route E39 highway. Ime is an eastern suburb of the town of Mandal. Ime might be considered to ...
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
*Reg ...
in a dimension hierarchy. It can be specified by its unique name, e.g. ime
Ime is a village in Lindesnes municipality in Agder county, Norway. The village is located on the east side of the river Mandalselva, along the European route E39 highway. Ime is an eastern suburb of the town of Mandal. Ime might be considered to ...
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
Ime is a village in Lindesnes municipality in Agder county, Norway. The village is located on the east side of the river Mandalselva, along the European route E39 highway. Ime is an eastern suburb of the town of Mandal. Ime might be considered to ...
iscal onth ugust 2006/code>, by qualified name, e.g. ime
Ime is a village in Lindesnes municipality in Agder county, Norway. The village is located on the east side of the river Mandalselva, along the European route E39 highway. Ime is an eastern suburb of the town of Mandal. Ime might be considered to ...
iscal006
Alec Trevelyan (006) is a fictional character and the main antagonist in the 1995 James Bond film '' GoldenEye'', the first film to feature actor Pierce Brosnan as Bond. Trevelyan is portrayed by actor Sean Bean. The likeness of Bean as Ale ...
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
Ime is a village in Lindesnes municipality in Agder county, Norway. The village is located on the east side of the river Mandalselva, along the European route E39 highway. Ime is an eastern suburb of the town of Mandal. Ime might be considered to ...
iscal onth ugust ustomer y Geography ll Customers SA easuresales 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 ...
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/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
easures tore SalesON COLUMNS,
ateMembers 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, 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, Washi ...
Docs
{{Authority control
Online analytical processing
Query languages