Today, Cube is announcing the public preview of our latest API endpoint: MDX. MDX (MultiDimensional eXpression) is a query language designed to retrieve data from multidimensional OLAP cube technologies. MDX was pioneered by Microsoft in the 1990s and has proven to be a remarkably durable language, seeing broad adoption by OLAP cube vendors and downstream analytics applications.
Conceptually, MDX is related to the SQL language in that they are both query languages, but they differ in their usage. Where SQL is oriented towards querying relational database management systems, MDX is oriented towards querying multidimensional cubes and retrieving aggregate measure values.
A traditional spreadsheet view is an easy to understand example of a two-dimensional cube representation. In this case a measure is aggregated by dimensions as rows and columns. As a quick example, think about a SUM of Sales measure in each cell of a spreadsheet with region on the rows, and a time dimension, perhaps each month of a transaction date, along the columns.
Multidimensional cubes are interesting in that they can represent data in more dimensions than two. Imagine our prior example, but let us add an additional dimension for a product category. This becomes difficult to visualize, of course, but it is useful in many analytical contexts.
Multidimensional analysis is frequently seen in areas such as scenario planning, supply chain optimization, logistics and various financial processes. It is such a common need in finance departments, that cubes have become nearly synonymous with financial planning and analysis (FP&A) functions within enterprises. Excel is, by a wide margin, the most commonly used front-end application for users performing this type of analysis. The common pivot table within Excel is a great example of how many users experience multidimensional analysis.
The challenge of migrating
A major problem that we have observed here at Cube is that while these OLAP cube technologies provide a powerful analysis capability, they are tied to relational database technologies such as SQL Server, Oracle, or SAP HANA. The three most common cube technologies are tied directly to these relational databases as sources. The challenge arises when enterprises undertake migrating their data warehouse to modern cloud data warehouse or lakehouse platforms. Users then lose this baseline capability they had before to perform this type of analysis on modeled, secured, and well governed data because these modern data platforms don’t natively speak MDX as a language. Without this capability, Excel cannot perform this type of analysis in a direct connection, instead forcing users to deal with exported snapshots of data
Ensuring direct connection to Excel
With the new MDX endpoint, Cube can effectively restore the direct connection to Excel capability that was lost, or impaired by customers who have migrated to more modern cloud data warehouse or lakehouse technologies. Cube will act as the OLAP engine in the place of technologies like SQL Server Analysis Services, Oracle Essbase, SAP HANA, and others that maintain multidimensional data models and aggregated measures. Users will simply point their MDX-speaking tools, such as Excel, at the new endpoint within Cube Cloud.
However, we are not simply replacing direct connectivity to Excel. We are also modernizing multidimensional analysis by improving it in several ways. With our pre-aggregation and caching capabilities in Cube Store we can meet and exceed the performance characteristics of older OLAP cube technologies. With our code based data modeling experience we can bring these multidimensional models into a modern data engineering work flow including version control and code review. With our advanced query rewrite capabilities, we enable Excel, and other applications that speak MDX, to query our long list of data sources live, if users choose to not use our pre-aggregations.
We are also now the first technology to enable querying of the same data models transparently via REST, GraphQL, SQL and now MDX API endpoints. These APIs are all secured and governed in a unified security model, share a unified caching technology, and share unified performance profiling and query history capabilities.
MDX as a supported language and API endpoint is a logical extension of Cube. In fact, we were so inspired by the concept of the multidimensional cube that it influenced the name of the open source project, and the company many years ago. We have been testing MDX heavily within Cube, and we have several customers who are using it and have provided invaluable feedback. If you would like to be a part of the public preview for this feature, please contact your Cube Cloud account team or reach out to us via our contact form.
We look forward to hearing your feedback and continuing to work with you to solve your toughest analytics challenges.