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 :
![MDX.bmp]()