ABC of MDX expression

If you are working with BI (Business Intelligence) and you have cube then as a developer you have tasks to produce data set for the business from the cube or cubes. Then your preferable way of populating data set will be using MDX query.

MDX is stands for Multidimensional Expressions. MDX is not specific to a database rather it's standard expressions to make query from multi-dimensional databases and solutions e.g.  MDX uses by MS SQL, Oracle, SAP, Teradata and many more vendors.

When we make SQL query it's for two dimension data e.g. rows and columns and that's how SQL is designed. In contrast, MDX can process one, two, three or more dimensions in the query.

If you have experience working with SQL, you will try to compare MDX expression with SQL syntax. While I was reading through different blogs, I found that some suggested 'Never compare with SQL Syntax that will only hurt you'. On the other hand, some wrote blog explaining how SQL syntax can be converted into MDX expression. So, right now I can't decide what to follow??!!!

So without comparing two things, I will only explain about basic of MDX expressions.

MDX expression has three different parts:

SELECT

FROM

WHERE

Part 1: SELECT

You will put both measures (fact) and dimension table's column name at the SELECT part. However, when you put columns from fact table then you need to add 'ON COLUMN' at the end of the column selection e.g. 

SELECT                                                                                  

{                                                                                                                                         

  [Measures].[Internet Order Quantity],  

  [Measures].[Internet Sales Amount]                                                 

} ON COLUMN

And when you SELECT from dimension then you need to add 'ON ROWS'

                                                                                     

            [Date].[Calendar Year].MEMBERS ON ROWS
  

                                 

Part 2:  FROM

As you know in SQL you generally use table or view to select FROM, however, in this case it's a cube name e.g.  FROM [Adventure Works] and a cube can hold numbers of fact and dimension tables.

So, complete syntax will look like below where SELECT and FROM part is included:

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS, 

[Date].[Calendar Year].MEMBERS ON ROWS 

FROM [Adventure Works] 

Part 3: WHERE (Optional)

The ‘WHERE’ part is not mandatory in MDX expression. This part is called slicer axis, it's similar as 'Where' clause in SQL which filters the data. And the syntax looks like: 

WHERE ([Customer].[Customer Geography].[Country].&[Denmark])  

You can filter by using completely different dimension than the one used in select statement. As like above we are filtering the data based on Customer dimension and only interested about Denmark customer to produce the result set.  Let’s make full MDX expression:

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS, 

[Date].[Calendar Year].MEMBERS ON ROWS 

FROM [Adventure Works] 

WHERE ([Customer].[Customer Geography].[Country].&[Denmark])