MDX- A Query Language that Makes OLAP Databases Easy to Understand

MDX, also known as Multidimensional Expressions, is a query language used in OLAP databases. This language is much similar to SQL query language that is used in relational databases. It is also referred to as a calculation language that also includes the usage of syntax. The MultiDimensional eXpressions is a great source of providing the end users with a specialized syntax that allows the users to query and manipulate the multidimensional data that is stored in OLAP cubes

Although the translation of some of these into traditional SQL is possible, however the synthesis of clumsy SQL expressions are the most required elements even for simple MDX expressions. There are n numbers of OLAP vendors which are preferably using this language. The reason is that this query language plays a very pivotal role in helping out the OLAP systems and hence, it is seen as the real standard for them.

The group of SQL Server engineers that include Mosha Pasumansky invented the MDX, which first came into existence in 1997 when it is treated as part of the OLE DB for OLAP specification. The Microsoft OLAP Services 7.0 was the first to release the specification commercially in 1998, while Microsoft Analysis Services repeated the task after sometimes. In 1999, Microsoft took an initiative to issue the latest version of the OLE DB for OLAP specification.

Some Query Language extensions such as subselects have been added by Microsoft in Analysis Services 2005. The usage of these new MDX Query Language extensions can also be seen in the products like Microsoft Excel 2007.

The Multidimensional Expressions is categorized into six major data types.

Scalar: A scalar may be either a string or a number. The specification of the scalar can be given as a literal. For example string "OLAP" or number 5 or MDX function can return it, e.g. UniqueName (string) or Aggregate (number), Value (string or number) etc.

Dimension/Hierarchy: A measure and attribute information is organized by a dimension in a cube.

Level: Level is a kind of level in a dimension hierarchy. Some unique name, e.g. [Fiscal], [Month], [Time] etc. are used in specifying it.

Other primary data types of MDX are member, tuple and set.