Multidimensional Expressions or MDX is a calculation/query language to express queries for online analytical processing - OLAP, in a database management system. MDX is an SQL extension to query data stored in a multidimensional structure. As for multidimensional databases they relate to OLAP cubes intended for reporting and analysis. The MDX queries contribute to working with dimensions, hierarchies, members etc. in the OLAP application.
It’s possible to exploit MDX to query data maintained in an SQL Server. As a result, an MDX expression returns a dataset containing cell and axis data. The main MDX query is the statement SELECT. The statement determines a result set to return, possesses specific syntax, and composes the MDX query.
Short history of MDX
The MDX language was developed in the 1990s in Panorama company which was later taken over by Microsoft. Thereby it is generally accepted that Microsoft designed the multidimensional expressions language setting it a proprietary standard for Microsoft production. MDX was rapidly applied by multiple companies developing multidimensional databases, OLAP systems as later Microsoft renounced its control of the MDX standard. Nowadays the MDX language constitutes a non-proprietary standard.
Multidimensional Expressions include several types of OLAP data to work with and return through the MDX queries:
- Scalar is either a number or a string.
- Dimension is a dimension in a cube and it’s the initial part to arrange measure and attribute. Dimensions are organized independently. They include members arranged in hierarchies whilst hierarchies contain levels.
- Hierarchy is primarily a dimension hierarchy and is organized inside dimensions and can’t be determined by unique names.
- Level belongs to a dimension hierarchy and in its turn is organized in a dimension hierarchy.
- Members belong to a dimension hierarchy and are arranged in a dimension hierarchy. They can be determined by unique names. All levels are specified depending on a hierarchy hence there can be 2 various members to coordinate in sets if one member belongs to 2 various hierarchies.
- Tuple is a collection of members from various dimensions and can be determined by several members.
- Set in its turn is a collection of tuples from identical dimensions/hierarchies.
- Other: there are also member properties as an alternative to data attributes.
In order to retrieve the requisite data one has to enter MDX queries into the system connected with a multidimensional database - OLAP. The MDX query doesn’t determine one object in the group but typically a number of objects reply the query though with various relevance degree. Thereby the results or replies of MDX are usually ranked in the way they match the information needed by a user. According to the system used the objects can be images, text docs, audio, videos etc.
Ranet OLAP MDX query builder
There are several aspects to remember when using Ranet OLAP MDX query builder and working with MDX queries in Ranet OLAP.
The tuple based on settings in the Rows and Columns areas is framed by Hierarchize function for each field. The function is used for each axis (columns and rows);
Several properties are automatically included in the MDX query:
Dimension properties: PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0, MEMBER_TYPE
Cell properties: BACK_COLOR, CELL_ORDINAL, FORE_COLOR, FONT_NAME, FONT_SIZE, FONT_FLAGS, FORMAT_STRING, VALUE, FORMATTED_VALUE, UPDATEABLE, ACTION_TYPE
All other properties have to be added manually.
If you use Non Empty for the axes in the custom query mode, the state of the corresponding buttons on the Toolbar has to be set accordingly. For example, if you use Non Empty for the columns axis in the MDX query, the Hide empty columns button has to be active.
The pivot table can be initialized using a subcube that limits the data slice used for the report. Filter settings from the Filters area form an embedded cube that is automatically added to the subcube. If there is only one element in the filter settings for the column, it additionally will be added to the Where condition in the MDX expression. Multiple filters are only added to the embedded cube.
Filters applied to cells in the visible rows and columns together with the filters in the Filters area form the embedded cube (subcube). Filters set for cells in the visible rows and columns cannot be added to the Where condition in the MDX query. Only hierarchies that are not on the visible axes of the pivot table (rows and columns axes) can be added to the Where condition.
If there is only one measure in the Data area, it is included in the Where condition in the MDX query. The measure will not appear in the names of rows and columns in the pivot table (See example from Ranet for HTML below).
It’s possible to work with multidimensional expressions in online demos of Ranet OLAP in HTML and Silverlight.