Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

A Quick Way to Start Learning SQL Server MDX

MSSQLTips author Dallas Snider By:   |   Read Comments (5)   |   Related Tips: > Analysis Services Development
Problem

Speaking from personal experience, learning and comprehending how to write MDX queries for SQL Server Analysis Services was difficult after spending years writing queries in T-SQL for the relational engine. I browsed through books and websites, and I also attended a class, but nothing seemed to "make it click" with me until I stumbled upon the simple solution that is presented in this tip.

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.

Visual Studio Report Wizard Query Builder

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

In Visual Studio Query Designer, 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.

In Visual Studio Query Designer, choose the Gender attribute of the Customer dimension

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

In Visual Studio Query Designer, choose the Country attribute of the Geography dimension

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.

In Visual Studio Query Designer, choose the Calendar Year attribute of the Delivery Date role-playing dimension

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

In Visual Studio Query Designer, drag the Education attribute of the Customer dimension

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.

In Visual Studio Query Designer, click on the Design Mode icon to see the MDX code generated

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

In Visual Studio Query Designer, 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.

In Visual Studio Query Designer, 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 Visual Studio Query Designer, as a means to test, 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
    
    Next Steps
    • After creating the MDX query in Visual Studio or Report Builder Query Designer, copy and paste the MDX query into a SQL Server Management Studio query editor window. Experiment with subtle changes to see what works and what doesn't work. This should help to build your confidence and ability to write MDX queries.
    • Please refer to the following tips for further assistance with MDX:


Last Update: 1/25/2013


About the author
MSSQLTips author Dallas Snider
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Tuesday, October 22, 2013 - 5:20:37 PM - ivan Read The Tip

Hi Dallas,

Awesome tip!  Thanks.

 

Ivan


Sunday, December 15, 2013 - 10:51:50 PM - Ola Read The Tip

thanks for the tip, it was really helpful. I was working with dates and finance measures, I want to find the difference between two periods. What function can I use.

Thanks


Tuesday, February 04, 2014 - 1:39:15 PM - Shovan Read The Tip

Can we use FILTER function in MDX to filter a set based on some Dimension Attribute Member?....My MDX is not giving the same o/p as its shown in Cube browser can you help me on this.

https://skydrive.live.com/#cid=A7526CE822AF1B61&id=A7526CE822AF1B61%21107&v=3


Wednesday, February 05, 2014 - 9:40:00 AM - Shovan Mukherjee Read The Tip

You shown me a new way & infact the simpliest way to learn MDX query without consulting any e-book,that is never possible in Cube browser to see the generated MDX in backend. Great work Dallas Snider !! Thank You.


Friday, February 14, 2014 - 1:09:31 AM - Yashawant Read The Tip

Thank you , this is very helpful tip.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.