Multidimensional eXpression(MDX)

Before we understand MDX, we must first reassemble our memories with respect to OLTP and OLAP. We know what these are, both are explained as separate topics in detail.

SQL (Structured Query Language) is for RELATIONAL DATABASES which is primarily used by OLTP systems, but it also used by almost all the datastores i.e., Data Warehouse, Data Lake, Data Vault, Business Vault etc.

MDX (Multidimensional eXpression) was invented by SQL Server engineers in 1997. MDX is the query language for OLAP DATABASES to extract multi-dimensional data from OLAP Cubes. Its syntax is just like XLS formula.

MDX is not a new language, it’s been there for decades but due to the ease of use of SQL language, it’s hardly used now a day. But experts are predicting the revival of MDX soon.

MDX Data Types

• Scalar can be number or string.
• Hierarchy is like dimension e.g., in time dimension, the hierarchy is year, month, week, day etc.
• Level is the level of Hierarchy of dimension like is year, month, or week etc.
• Member is the member of hierarchy like exact date or week or month etc.
• Tuples contain one or more members of a hierarchy.
• Set contains one more tuple.
• Other Data Types are the name of the fields/ columns in a multidimensional cube. It’s just like fields/ columns in a Data Warehouse or Data Mart.