MDX Query Basics (Analysis Services 2012)

MDX tutorial, introduction to Multidimensional Expressions

The Logic/Flow of MDX (a.k.a. How to Think in MDX)

Refer Source: MDX Query Basics (Analysis Services 2012)

Adventureworks 2012 multidimensional models installed on your SSAS MD mode instance.

Source to get: Codeflex etc.

Start "SSMS:"  "Server Type:" Analysis Services

MDX:

1.

/*

This is multiline comment.

*/

-- This is single line comment

2.

SELECT ... FROM ... WHERE

Select   From  [insert_your_cube_name_here]

Select  From  [ Adventure Works]

3.

Measure Group: Internet Orders

Measures:

Dimensions:

4. -- adding Measures:

Select {measures} on COLUMNS --0 

FROM [insert_your_cube_name_here]

Select  { [Measures]. [Internet Order Count]} ON COLUMNS

FROM  [Adventure Works];

note: for multiple columns  { [Measures]. [Internet Order Count], }

or

Select  { [Measures]. [Internet Order Count]} ON 0

FROM  [Adventure Works];

5. -- adding Dimensions:

Select  { [Measures]. [Internet Order Count]} ON COLUMNS ,

{ [Date]. [Calendar Year].[CalendarYear]} ON ROWS

FROM  [Adventure Works];

note: when above rows show some null values to remove null values "Functions".

6.Functions:

Select  NONEMPTY({ [Measures]. [Internet Order Count]}) ON COLUMNS ,

NONEMPTY({ [Date]. [Calendar Year].[CalendarYear]}) ON ROWS

FROM  [Adventure Works];

7.SLICERS: -- WHERE Clause

Select  { [Measures].[Internet Order Count]} ON COLUMNS Where [Date].[Calendar Year].& [2005];

Note: Can not have the where condition one the Slicer in the selection.

Select  { [Measures].[Internet Order Count]} ON COLUMNS , {[Date].[Calendar Year].& [2005] }

Where [Date].[Calendar Year].& [2005];