Multidimensional Expressions (MDX)

Multidimensional Expressions (MDX)

Multidimensional Expressions (MDX) is the native query language for SQL Server Analysis Services (SSAS). Just as SQL is a structured query language for returning tabular results from relational databases, MDX is a query language for returning multidimensional data sets from multidimensional data sources. To retrieve data, both SQL and MDX use SELECT / FROM / WHERE clauses in their statements. What makes MDX different from SQL is how data is referred to. A SQL SELECT statement refers to the column names in a table, but an MDX query refers to levels in a hierarchy. Unlike SQL, MDX understands dimensional data, from hierarchy root to leaf level.

Example :

SELECT {[Measures].[Unit Price],

[Measures].[List Amount],

[Measures].[Sales Amount],

[Measures].[COGS Amount]} ON COLUMNS ,

{[FactTest].[Region].&[1],

[FactTest].[Region].&[2]} ON ROWS

FROM [SampleCube]

Here we've selected measure members for the columns—this creates the column heading and values. Then we selected dimension members for the rows. Finally, we indicated which cube to run the query on.

Output :