Querying Technique

While there are multiple programmable ways to query data from an OLAP cube, such as SQL and other programming languages, MDX has been one of the most prominent options to query cube data.

For almost two decades, MDX has been used as a primary powerful way to query and analyze multidimensional data, allowing users to gain insights from complex datasets that would be difficult to analyze using traditional relational database queries; however, with the evolution of data lake and delta lakes the preferences have changed.

MDX (Multidimensional Expressions) is a query language used to retrieve data from multidimensional databases that store data in a cube format, typically used in OLAP (online analytical processing) systems. It was invented by Microsoft and was first introduced in 1997 as part of their SQL Server Analysis Services product. Since then, it has become a widely adopted standard in the OLAP industry, and is supported by many vendors, including IBM, Oracle, and SAP.

MDX works by providing a flexible syntax for querying multidimensional data, allowing users to extract information from different dimensions and levels of a cube. MDX queries can retrieve data from a single cell or from multiple cells within a cube and can perform calculations and aggregations on the data.

Some of the key features of MDX include the following:

•     Hierarchies: MDX supports hierarchies, which allow users to navigate through data in a structured way. Hierarchies are arranged in a tree-like structure, where each level represents a different attribute of the data.

•  Measures: Measures are the numeric data stored in a cube, and MDX provides a way to perform calculations on them. Measures can be aggregated over different dimensions and levels to provide different perspectives on the data.

•     Cross-join: MDX supports cross-join operations, which allow users to combine different sets of data into a single result set. This is useful for comparing data across different dimensions.

•     Filters: MDX allows users to apply filters to their queries to restrict the data returned to specific subsets.

A basic MDX query example:

SELECT {[Measures].[Sales]} ON COLUMNS, {[Month].[Year].MEMBERS} ON ROWS FROM [SalesCube]

WHERE [Product].[Category].[Electronics]

This MDX query retrieves the total sales for each year in the [Time] dimension, for the [Product] category “Electronics,” from the [SalesCube] multidimensional database.

The query is composed of several parts:

•     SELECT: specifies which data to retrieve—in this case, the [Measures].[Sales] data on the columns axis.

•     ON COLUMNS: specifies that the [Measures].[Sales] data should be displayed on the columns axis.

•     {[Month].[Year].MEMBERS}: specifies that the [Time].[Year] hierarchy should be used on the rows axis, and that all the members of the hierarchy should be included in the query.

•     ON ROWS: specifies that the [Month].[Year] hierarchy should be displayed on the rows axis.

•     FROM [SalesCube]: specifies the name of the multidimensional database (in this case, [SalesCube]) that the data is being retrieved from.

•     WHERE [Product].[Category].[Electronics]: specifies a filter that restricts the results to the [Product].[Category].[Electronics] category.

This query will return a table with the total sales for each year in the [Time] dimension, for the [Product] category “Electronics.”