Learn MDX

How to write MDX queries for SQL Server Analysis Services?

Solution

The solution presented here was created in Visual Studio, but similar steps can be performed in Report Builder. The premise is to use the drag-and-drop functionality in the Query Designer, and then switch to the text editor to see the auto-generated MDX query.

The first step is to start the Report Wizard and select your Analysis Services data source. In the Design the Query page, select "Query Builder..." as shown in the figure below.

In this example, drag the measures Internet Sales Amount and Internet Order Quantity from the Measures section onto the palette.

Secondly, choose the Gender attribute of the Customer dimension and filter where the gender equals Female. Notice how the values for Internet Sales Amount and Internet Order Quantity have been reduced.

Next, choose the Country attribute of the Geography dimension and filter where the country does not equal the United States.

Then, choose the Calendar Year attribute of the Delivery Date role-playing dimension and filter where the range is the calendar years 2005 through 2007 inclusive.

Next, drag the Education attribute of the Customer dimension onto the palette to slice the measures by the education level.

Now, drag the Home Owner attribute of the Customer dimension onto the palette to slice the measures by the education level and the home owner attributes.

Finally, click on the Design Mode icon indicated by the arrow in the image below. This will display the MDX query in a textbox for editing.

Please note that the query in the textbox is not formatted for easy viewing.

With some strategically placed CR/LF's, the query can be viewed more easily.

Removing the WHERE clause does not affect the outcome of the query.

In the following code sample, I have added line numbers at the end of each line to help explain the syntax of this query.

• Line 1 is the SELECT command.
• Lines 2 and 7 are the NON EMPTY operators which prevent the return of null values for the measures and for the dimensions respectively.
• Lines 4 and 5 are the measures (sometimes these are called facts) that we are selecting from the Analysis Services cube. Please note that the measures are separated by a comma just like columns are separated by a comma in an SQL statement.
• Line 6 is where we specify to place the measures in columns.
• Lines 9 and 10 are where we specify the dimensional attributes on which to slice the measures. Please note that the dimensional attributes are separated by an asterisk (*).
• Line 11 is where specify to place the dimensional attributes on rows.
• Lines 12 and 13 are used to specify the range of the calendar years from the Delivery Date role-playing dimension. Please note the colon (:) is used between the low and high values of the range.
• Line 14 is used to remove all values where the country is equal to the United States. Please note the minus sign (-) before the curly bracket after the word SELECT.
• Line 15 is used to select where the Gender attribute of the customer dimension equals F for Female.
• Line 16 specifies that we are selecting from the Adventure Works cube. Please note the three closing parentheses at the end of the line match the number of FROM ( SELECT statements in lines 12, 14 and 15.
• Line 17 is the WHERE clause. In this example, removing the WHERE clause does not affect the results of the query.
  
  

  SELECT                                                                                   //01
  NON EMPTY                                                                                //02
  {                                                                                        //03
    [Measures].[Internet Sales Amount],                                                    //04
    [Measures].[Internet Order Quantity]                                                   //05
  } ON COLUMNS,                                                                            //06
  NON EMPTY                                                                                //07
  { (                                                                                      //08
    [Customer].[Education].[Education].ALLMEMBERS *                                        //09
    [Customer].[Home Owner].[Home Owner].ALLMEMBERS                                        //10
  ) } ON ROWS                                                                              //11
  FROM(SELECT ([Delivery Date].[Calendar Year].&[2005]:                                    //12
              [Delivery Date].[Calendar Year].&[2007]) ON COLUMNS                          //13 
  FROM(SELECT ( -{ [Customer].[Customer Geography].[Country].&[United States]}) ON COLUMNS //14
  FROM(SELECT ( { [Customer].[Gender].&[F] } ) ON COLUMNS                                  //15
  FROM [Adventure Works])))                                                                //16
  WHERE ( [Customer].[Gender].&[F] )                                                       //17