MultiDimensional eXpressions

Short description: OLAP query language

Multidimensional Expressions (MDX) is a query language for online analytical processing (OLAP) using a database management system. Much like SQL, it is a query language for OLAP cubes.[1] It is also a calculation language, with syntax similar to spreadsheet formulae.

## Contents

* 1 Background
* 2 History
* 2.1 mdXML
* 3 MDX data types
* 4 Example query
* 5 References
* 6 Further reading
* 7 External links

## 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 a wide range of OLAP vendors.

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 added some MDX Query Language extensions like subselects. Products like Microsoft Excel 2007 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 (XMLA) standard, which included mdXML as a query language. In the 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

* Scalar. Scalar is either a number or a string. It can be specified as a literal, e.g. number 5 or string "OLAP" or it can be returned by an MDX function, e.g. `Aggregate` (number), `UniqueName` (string), `.Value` (number or string) etc.
* Dimension/Hierarchy. Dimension is a dimension of a cube. A dimension is a primary organizer of measure and attribute information in a cube. MDX does not know of, nor does it assume any, dependencies between dimensions - they are assumed to be mutually independent. A dimension will contain some members (see below) organized in some hierarchy or hierarchies containing levels. It can be specified by its unique name, e.g. `[Time]` or it can be returned by an MDX function, e.g. `.Dimension`. Hierarchy is a dimension hierarchy of a cube. It can be specified by its unique name, e.g. `[Time].[Fiscal]` 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 in a dimension hierarchy. It can be specified by its unique name, e.g. `[Time].[Fiscal].[Month]` 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. `[Time].[Fiscal].[Month].[August 2006]`, by qualified name, e.g. `[Time].[Fiscal].[2006].[Q3].[August 2006]` 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 (`[Product].[ByManufacturer]` and `[Product].[ByCategory]`), 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. `([Time].[Fiscal].[Month].[August], [Customer].[By Geography].[All Customers].[USA], [Measures].[Sales])` 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. `{([Measures].[Sales], [Time].[Fiscal].[2006]), ([Measures].[Sales], [Time].[Fiscal].[2007])}` 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, `[Product].CurrentMember.[Sales Price]`) or by using a special access function (for example, `[Product].CurrentMember.Properties("Sales Price")`). In limited contexts, MDX allows other data types as well - for example Array 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
{ [Measures].[Store Sales] } ON COLUMNS,
{ [Date].[2002], [Date].[2003] } ON ROWS
FROM Sales
WHERE ( [Store].[USA].[CA] )

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
[Measures].[Store Sales] ON COLUMNS,
[Date].Members ON ROWS
FROM Sales
WHERE ( [Store].[USA].[CA] )

## References

1. ↑ 1.0 1.1 Carl Nolan. "Manipulate and Query OLAP Data Using ADOMD and Multidimensional Expressions". Microsoft. http://www.microsoft.com/msj/0899/mdx/mdx.aspx.

## 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, ISBN 0-471-74808-0
* Mosha Pasumansky, Mark Whitehorn, Rob Zare: Fast Track to MDX. ISBN 1-84628-174-1
* 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 Docs

* v
* t
* e

Data warehouses

Creating a data warehouse

| Concepts|

* Database
* Dimension
* Dimensional modeling
* Fact
* OLAP
* Star schema
* Snowflake schema
* Reverse star schema
* Aggregate

|

Variants|

* Anchor modeling
* Column-oriented DBMS
* Data vault modeling
* HOLAP
* MOLAP
* ROLAP
* Operational data store

Elements|

* Data dictionary/Metadata
* Data mart
* Sixth normal form
* Surrogate key

Fact|

* Fact table
* Early-arriving fact
* Measure

Dimension|

* Dimension table
* Degenerate
* Slowly changing

Filling|

* Extract-Transform-Load (ETL)
* Extract
* Transform
* Load

Using a data warehouse

| Concepts|

* Business intelligence
* Dashboard
* Data mining
* Decision support system (DSS)
* OLAP cube
* Data warehouse automation

|

Languages|

* Data Mining Extensions (DMX)
* MultiDimensional eXpressions (MDX)
* XML for Analysis (XMLA)

Tools|

* Business intelligence software
* Reporting software
* Spreadsheet

Related

| People|

* Bill Inmon
* Ralph Kimball

|

Products|

* Comparison of OLAP servers
* Data warehousing products and their producers

0.00

(0 votes)

Original source: https://en.wikipedia.org/wiki/MultiDimensional eXpressions. Read more

|

Retrieved from "https://handwiki.org/wiki/index.php?title=MultiDimensional_eXpressions&oldid=4227501"
*[v]: View this template
*[t]: Discuss this template
*[e]: Edit this template