x
Home
Search
Topics
Almanac
Science
Nature
People
History
Society
Signup
Login
HOME
TOPICS
ALMANAC
SCIENCE
NATURE
PEOPLE
HISTORY
SOCIETY
PHILOSOPHY
Multidimensional Expressions
Topic Home
Discussion
0
Encyclopedia
Multidimensional Expressions (MDX) is a query language
Query language
Query languages are computer languages used to make queries into databases and information systems.Broadly, query languages can be classified according to whether they are database query languages or information retrieval query languages...

 for OLAP
OLAP
In computing, online analytical processing, or OLAP , is an approach to swiftly answer multi-dimensional analytical queries. OLAP is part of the broader category of business intelligence, which also encompasses relational reporting and data mining...

 database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...

s, much like SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....

 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 cube
OLAP cube
An OLAP cube is a data structure that allows fast analysis of data. It can also be defined as the capability of manipulating and analyzing data from multiple perspectives...

s. 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
Comparison of OLAP Servers
The following tables compare general and technical information for a number of Online analytical processing servers. Please see the individual products articles for further information.-General information:-Data storage modes:-APIs and query languages:...

 and has become the de facto standard
De facto standard
A de facto standard is a custom, convention, product, or system that has achieved a dominant position by public acceptance or market forces...

 for OLAP systems.

History

MDX was first introduced as part of the OLE DB for OLAP specification in 1997 from Microsoft
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...

. It was invented by the group of SQL Server
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...

 engineers including Mosha Pasumansky
Mosha Pasumansky
Mosha Pasumansky is one of the inventors of the MultiDimensional eXpressions language, a query language for online analytical processing databases...

. The specification was quickly followed by commercial release of Microsoft OLAP Services 7.0 in 1998 and later by Microsoft Analysis Services
Microsoft Analysis Services
Microsoft SQL Server Analysis Services is part of Microsoft SQL Server, a database management system. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services and 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
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...

-owned specification, it was adopted by the wide range of OLAP vendors. This included both vendors on the server side such as Applix
Applix
Applix Inc. was a software company founded in 1983 based in Westborough, Massachusetts that published Applix TM1, a MOLAP database server, and related presentation tools, including Applix Web and Applix Executive Viewer. Together, Applix TM1, Applix Web and Applix Executive Viewer were the three...

, icCube, MicroStrategy
MicroStrategy
MicroStrategy, Inc. , is a business intelligence software vendor. MicroStrategy's software enables leading organizations worldwide to analyze the vast amounts of data stored across their enterprises to make more strategic business decisions...

, NCR
NCR Corporation
NCR Corporation is an American technology company specializing in kiosk products for the retail, financial, travel, healthcare, food service, entertainment, gaming and public sector industries. Its main products are self-service kiosks, point-of-sale terminals, automated teller machines, check...

, Oracle Corporation
Oracle Corporation
Oracle Corporation is an American multinational computer technology corporation that specializes in developing and marketing hardware systems and enterprise software products – particularly database management systems...

, SAS
SAS Institute
SAS Institute Inc. , headquartered in Cary, North Carolina, USA, has been a major producer of software since it was founded in 1976 by Anthony Barr, James Goodnight, John Sall and Jane Helwig...

, SAP
SAP AG
SAP AG is a German software corporation that makes enterprise software to manage business operations and customer relations. Headquartered in Walldorf, Baden-Württemberg, with regional offices around the world, SAP is the market leader in enterprise application software...

, Teradata
Teradata
Teradata Corporation is a vendor specializing in data warehousing and analytic applications. Its products are commonly used by companies to manage data warehouses for analytics and business intelligence purposes. Teradata was formerly a division of NCR Corporation, with the spinoff from NCR on...

, Whitelight, and vendors on the client side such as Panorama Software, PowerOLAP, XLCubed
XLCubed
XLCubed is a business intelligence software and consulting services company. Established in 2001, XLCubed develops business intelligence software and provides business intelligence and performance management consulting services...

, Proclarity, AppSource, Jaspersoft, Cognos
Cognos
Cognos was an Ottawa, Ontario-based company making business intelligence and performance management software. Founded in 1969, at its peak Cognos employed almost 3,500 people and served more than 23,000 customers in over 135 countries.Originally Quasar Systems Limited, it adopted the Cognos...

, Business Objects, Brio Technology
Brio Technology
Brio Technology was a San Francisco Bay area software company cofounded in 1984 by Yorgen Edholm and Katherine Glassey. The company is best known for their business intelligence software systems, starting with DataPivot on the Apple Macintosh. Brio Software was acquired by Hyperion in 2003...

, Crystal Reports
Crystal Reports
-External links:*****Reviews:****...

, Microsoft Excel
Microsoft Excel
Microsoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...

, and Microsoft Reporting Services.

With the invention of XML for Analysis
XML for Analysis
XML for Analysis is an industry standard for data access in analytical systems, such as OLAP and data mining. XMLA is based on other industry standards such as XML, SOAP and HTTP...

, which standardized MDX as a query language, even more companies - such as Hyperion Solutions
Hyperion Solutions
Hyperion Solutions Corporation was a business performance management software company, located in Santa Clara, California, USA, which was acquired by Oracle Corporation in 2007...

 - 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
XML for Analysis
XML for Analysis is an industry standard for data access in analytical systems, such as OLAP and data mining. XMLA is based on other industry standards such as XML, SOAP and HTTP...

 standard, which included mdXML as a query language. In the current XMLA 1.1 specification, mdXML is essentially MDX wrapped in the XML tag.

MDX data types

There are six primary data type
Data type
In computer programming, a data type is a classification identifying one of various types of data, such as floating-point, integer, or Boolean, that determines the possible values for that type; the operations that can be done on values of that type; the meaning of the data; and the way values of...

s in MDX
  • Scalar. Scalar is either a number
    Number
    A number is a mathematical object used to count and measure. In mathematics, the definition of number has been extended over the years to include such numbers as zero, negative numbers, rational numbers, irrational numbers, and complex numbers....

     or a string
    String (computer science)
    In formal languages, which are used in mathematical logic and theoretical computer science, a string is a finite sequence of symbols that are chosen from a set or alphabet....

    . 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
    Dimension (data warehouse)
    In a data warehouse, a dimension is a data element that categorizes each item in a data set into non-overlapping regions. A data warehouse dimension provides the means to "slice and dice" data in a data warehouse. Dimensions provide structured labeling information to otherwise unordered numeric...

     of a cube
    OLAP cube
    An OLAP cube is a data structure that allows fast analysis of data. It can also be defined as the capability of manipulating and analyzing data from multiple perspectives...

    . 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
    Dimension (data warehouse)
    In a data warehouse, a dimension is a data element that categorizes each item in a data set into non-overlapping regions. A data warehouse dimension provides the means to "slice and dice" data in a data warehouse. Dimensions provide structured labeling information to otherwise unordered numeric...

     hierarchy of a cube
    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. The cube can also be called a regular hexahedron and is one of the five Platonic solids. It is a special kind of square prism, of rectangular parallelepiped and...

    . 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
    Level
    Level or levels may refer to:-Places:*Levél, Győr-Moson-Sopron, Hungary*Level, Ohio, United States*Level Valley*Levels, West Virginia-Engineering-related:*Floor, or storey, of a building or a mine...

     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].[Q2].[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 hierarchy ([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 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
    Array data type
    In computer science, an array type is a data type that is meant to describe a collection of elements , each selected by one or more indices that can be computed at run time by the program. Such a collection is usually called an array variable, array value, or simply 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.

External references

The source of this article is wikipedia, the free encyclopedia.  The text of this article is licensed under the GFDL.
Silverdale Interactive © 2024. All Rights Reserved.
 
x
OK