Simple MDX Queries

From SQLServerPedia

Jump to: navigation, search
See Also: Main_Page - Database Administration - Analysis Services - MDX with Analysis Services


The very basic MDX query would have the following syntax:

SELECT {set that defines column headers} ON COLUMNS,
{set that defines row headers} ON ROWS
FROM [cube name]


The SELECT statement defines results that you want to view; you can represent dimensions on viewing panes or axes, starting out with columns and rows. Note that you MUST have COLUMNS in every MDX query. The FROM clause simply references the cube from which we retrieve the data. For example, the following query returns sales of all vehicles:

SELECT {[dimProduct].[all products]} ON COLUMNS,

{[measures].[sales dollars]} ON ROWS


FROM [vehicle_sales]


Note that for the purposes of MDX queries the collection of measures behaves much like any other dimension.



Perhaps the simplest and most commonly used MDX function when defining column or row headings is .members. The members function returns all members of a given dimension, hierarchy or level. For example, we could easily change the previous query to return all measures instead of just sales dollars as follows:

SELECT {[dimProduct].[all products]} ON COLUMNS,

{[measures].members} ON ROWS


FROM [vehicle_sales]


Note that the measures "dimension" only contains one hierarchy so it's not necessary to specify the hierarchy the members of which you want to return in your results. When using the members function for any other dimensions you must specify the hierarchy members of which you wish to return. For example, we could return all measures for all product brands with the following: 

SELECT {[dimProduct].[product brand].members} ON COLUMNS,

{[measures].members} ON ROWS


FROM [vehicle_sales]


The alternative way of getting the same results would be as follows:

SELECT {[dimProduct].[product brand].[Buick],

[dimProduct].[product brand].[Ford],
[dimProduct].[product brand].[Mercedes],
[dimProduct].[product brand].[Audi],
[dimProduct].[product brand].[Toyota],
[dimProduct].[product brand].[Jeep],
[dimProduct].[product brand].[Nissan]
} ON COLUMNS,
{[measures].members} ON ROWS


FROM [vehicle_sales]


As you noticed in the previous query if you wanted to specify multiple members explicitly you would need to separate them with commas. For example, the following query returns count of orders placed by customers with bachelor's degrees or those with high school education:

SELECT {[customer].[education].[bachelors],

[customer].[education].[high school] } ON COLUMNS,
{[measures].[order count]} ON ROWS


FROM [adventure works]


As you noticed from the examples above MDX requires separating dimension names, level names and member names with dots.



You will often encounter cases when you need to represent a range of values instead of specifying each member explicitly. For example, you might wish to display sales that occurred between years 2001 and 2004. You can return ranges by separating the first and last members with a colon, as in the following query: 

SELECT {[date].[cy 2001]:[date].[cy 2004] } ON COLUMNS,

{[measures].[order count]} ON ROWS


FROM [adventure works]


So far all examples included the SELECT and FROM clauses by displaying certain data on rows and other data on columns. If your MDX query doesn't explicitly reference a dimension then MSAS "assumes" a default member for that dimension. The WHERE clause can be used to limit the result set by specifying a "slicer", or scope for your query. The WHERE clause is used to specify members of the dimensions that are not being retrieved on COLUMNS or ROWS. For example, we could change the previous query slightly to return order count during years 2001 through 2004 for those orders that have occurred in Atlanta as follows: 

SELECT {[date].[cy 2001]:[date].[cy 2004] } ON COLUMNS,

{[measures].[order count]} ON ROWS
FROM [adventure works]


WHERE ([geography].[city].[atlanta])


This query will override the default member (which happens to be "all cities") and retrieve only Atlanta sales.



If you wanted to limit the output of your query by multiple criteria you could separate slicers with commas. For example, the next query returns those orders in Atlanta that occurred between 2001 and 2004 because of product quality:

SELECT {[date].[cy 2001]:[date].[cy 2004]  } ON COLUMNS,

{[measures].[order count]} ON ROWS
FROM [adventure works]
WHERE ([geography].[city].[atlanta],


[sales reason].[sales reason].[quality])


Note however, that you cannot use the dimension you specified on columns or rows as a slicer. For example, you couldn't limit the query above by calendar year because the date dimension is already returned on columns. You are allowed to use the measures "dimension" in the WHERE clause much like any other dimension. The following is a valid query: 

SELECT {[date].[cy 2001]:[date].[cy 2004] } ON COLUMNS,

{[geography].[city].[auburn]} ON ROWS
FROM [adventure works]
WHERE ([sales reason].[sales reason].[quality],


[measures].[internet order count])


MSAS 2000 does not support sets in the WHERE clause. This limitation no longer exists with MSAS 2005. For example, the following is a valid statement with MSAS 2005:

SELECT {[date].[cy 2001]:[date].[cy 2004] } ON COLUMNS,

{[geography].[city].[auburn]} ON ROWS
FROM [adventure works]


WHERE ([product].[product categories].[category].members)


Since cubes are multidimensional structures you might wonder if you could return data on more axes than COLUMNS and ROWS. MDX does support returning data on more than two axes, so you could specify additional sets that need to be returned on PAGES, SECTIONS and CHAPTERS. In fact, MDX supports up to 128 axes, but only 5 of them have aliases. You can reference COLUMNS as AXIS (0), ROWS as AXIS (1) and so forth up to AXIS (127). However, most front end applications can only display result sets in two dimensional formats and don't provide ways to visualize more than two dimensions. This news shouldn't trouble you because most business requirements are limited to two-dimensional reports.



Analysis Services 2000 is installed with a sample MDX application that you can use to validate your queries. With MSAS 2005 you can execute your MDX queries directly in SQL Server Management Studio (SSMS). Both of these applications only support returning cube data on rows and columns.



You can comment your MDX code by using one of three choices:

  1. Double dash (--). Such comment will advise MSAS to ignore the rest of the line. For example:


    -- this line is NOT executed SELECT {[measures].members}
  2. Double slash (//). Works exactly the same way as the double dash.


    // this line is NOT executed SELECT {[measures].members}
  3. Block comment beginning with "/*" and ending with "*/", identical to Transact-SQL comments. Such comment will advise MSAS to ignore the entire block:


    /* none of this code is executed:

    SELECT CROSSJOIN({[date].[cy 2001]:[date].[cy 2002] },
CROSSJOIN({[product].[category].members},
{[customer].[gender].members}))

    

    ON ROWS,

    

    {[measures].[order count]} ON COLUMNS

    
FROM [adventure works]*/
Retrieved from "http://sqlserverpedia.com/wiki/Simple_MDX_Queries"