|MDX Query Basics and Usage Example|
This page provides an overview of the MDX query, compares it to an SQL query, and describes the basic MDX query structure. It also includes a basic usage example for MDX using the Quartet ActivePivot OLAP server.
The general information in this page is taken from major developer sites on the web, with links to the original articles. This content is provided courtesy of Quartet FS, makers of the ActivePivot OLAP Server, a robust and powerful object-based tool that provides realtime analytics capabilities through its transactional engine and multi-threaded processing capabilities.
MDX Query Basics
The Multidimensional Expressions (MDX) language allows users to describe queries and manipulate multidimentional information, such as the data stored in cubes. MDX functions can define calculated members and query cube data.
The MDX and SQL languages have a similar structure, and include some of the same keywords. However, one of the significant conceptual differences between the two languages is that MDX builds a multidimensional view of the data, where SQL builds a relational view. Although it is possible to use SQL exclusively to query cubes, the MDX query was designed specifically to retrieve multidimensional data structures with almost any number of dimensions. Additionally, SQL was designed to handle only two-dimensional tabular data when processing queries, where MDX can process one, two, three, or more dimensions in queries. Each dimension in MDX is referred to as an axis, and the terms column and row are simply used as aliases for the first two axis dimensions in an MDX query (the alias itself holds no real meaning to MDX).
Additional to the conceptual differences between MDX and SQL, both languages use different terms to describe their basic concepts. For example, the cube concept explained above is actually a table in the SQL language; and the multidimentional term level is a column in SQL (referring to a string or discrete number). Additionally, a dimension in MDX refers to what would be understood in relational terms as several related columns or a dimension table; and a measure in MDX refers to a discrete, continuous or numeric column in SQL. Finally, a member in MDX refers to the specific row and column of a dimension table.
A basic MDX query uses the
An MDX query, and specifically
For the full article regarding MDX query structure, see the SAS documentation website.
ActivePivot MDX Usage Example
The following example allows you to perform an MDX query, and get the Gross. Profit measure for brand LG and the category LCD on May 2011, broken down by an Amazon Standard Identification Number (ASIN). The results are displayed in a Pivot Table using Quartet FS's ActivePivot Real time OLAP server.
The ActivePivot OLAP server can aggregate data from multiple data sources, process high volumes of data, and analyze multi-dimensional events to display business metrics and key performance indicators (KPIs) with zero latency.
The dataset being queried in this example is Quartet FS's eCommerce demo project, which contains traffic data based on Amazon.
Here you take the top 5 ASINs, with regards to the LCD category (still in the context of LG and May 2011):
Finally, you can select the ASIN with the smallest gross profit, for which the cumulative Gross.Profit is less than 200k, using
To see how ActivePivot can help you perform powerful, real-time analysis, please fill in the form below.