Quick start – writing your first MDX query

Exclusive offer: get 50% off this eBook here
Instant MDX Queries for SQL Server 2012 [Instant]

Instant MDX Queries for SQL Server 2012 [Instant] — Save 50%

Learn how to write MDX queries from SQL Server Analysis Services 2012 cubes quickly and easily with this book and ebook

$19.99    $10.00
by Nicholas Emond | September 2013 | Enterprise Articles Microsoft

In this article by Nicholas Emond, the author of the book Instant MDX Queries for SQL Server 2012, it is explained that a MDX query is coded and executed against a cube to have a result in a specific format return to the client application.

(For more resources related to this topic, see here.)

Step 1 – open the SQL Server Management Studio and connect to the cube

The Microsoft SQL Server Management Studio (SSMS) is a client application used by the administrators to manage instances and by developers to create object and write queries. We will use SSMS to connect on the cube and write our first MDX query. Here's a screenshot of SSMS with a connection on a SSAS server:

  1. Click on the Windows button, click on All Programs, click on Microsoft SQL Server 2012, and then click on SQL Server Management Studio.

  2. In the Connect to Server window, in the Server type box, select Analysis Services. In the Server name box, type the name of your Analysis Services server. Click on Connect.

  3. In the SQL Server Management Studio window, click on the File menu, click on New, and then click on Analysis Services MDX Query.

  4. In the Connect to Analysis Services window, in the Server name box, type the name of you Analysis Services server, and then click on Connect.

    • SELECT

    • FROM

    • WHERE

If you have already written SQL queries, you might have already made connections with the T-SQL language. Here's my tip for you: don't, you will only hurt yourself. Some words are the same, but it is better to think MDX when writing MDX rather than to think SQL when writing MDX.

Step 2 – SELECT

The SELECT clause is the main part of the MDX query. You will define what are the measure and dimension members that you want to display. You also have to define on which axis of your result set you want to display the measure and dimension members.

Axes

Axes are the columns and rows of the result set. With SQL Server Analysis Services, upto 128 axes can be specified. The axes have a number which is zero-based. The first axe is 0, the second on is 1, and so on. So, if you want to use two axes, the first one will be 0 and the second will be 1. You cannot use axe 0 and axe 2, if you don't define axe 1. For the first five axes, you can use the axis alias instead. After the axe 4, you will have to revert to the number because no other aliases are available.

Axe Number

Alias

0

Columns

1

Rows

2

Pages

3

Sections

4

Chapters

Even if SSAS supports 128 axes, if you try to use more than two axes in SSMS in your query, you will get this error when you execute your MDX query: Results cannot be displayed for cellsets with more than two axes. So, always write your MDX queries using only two axes in SSMS and separate them with a comma.

Tuples

A tuple is a specific point in the cube where dimensions meet. A tuple can contain one or more members from the cube's dimensions, but you cannot have two members from the same dimension. If you want to display only the calendar year 2008, you will have to write [Date].[CY 2008]. If you want to have more than one dimension, you have to enclose them using parenthesis () and separate them with a comma. Calendar year for United States will look like ([Date].[CY 2008], [Geography].[United States]). Even if you are writing a tuple with only a single member from a single dimension, it is good practice to enclose it in parenthesis.

Sets

If you want to display the year 2005 to 2008, you will write four single-dimension tuples which composes a set. When writing the set, you separate the tuples with commas and wrap it all with curly braces {} and separate the tuples with commas such as {[Date].[CY 2005], [Date].[CY 2006] , [Date].[CY 2007] , [Date].[CY 2008]} to have the calendar years from 2005 to 2008. Since all the tuples are from the same dimension, you can also write it using a colon (:), such as {[Date].[CY 2005]: [Date].[CY 2008]} which will give you the years 2005 to 2008. With SSAS 2012, you can write {[Date].[CY 2008]: [Date].[CY 2005]} and the result will still be from 2005 to 2008.

What about the calendar year 2008 for both Canada and the United States? You will write two tuples. A set can be composed of one or more tuples. The tuples must have the same dimensionality; otherwise, an error will occur. Meaning that the first member is from the Date dimension and the second from the Geography dimension. You cannot have the first tuple with Date-Geography and the second being Geography-Date; you will encounter an error. So the calendar year 2008 with Canada and United States will look such as {([Date].[CY 2008], [Geography].[Canada]), ([Date].[CY 2008], [Geography].[United States])}.

When writing tuples, always use the form [Dimension].[Level].[MemberName]. So, [Geography].[Canada] should be written as [Geography].[Country].[Canada]. You could also use the member key instead of the member name. In SSAS, use the ampersand (&) when using the key; [Geography].[State-Province].[Quebec] with the name becomes [Geography].[State-Province].&[QC]&[CA] using the keys.

What happens when you want to write bigger sets such as for the bikes and components product category in Canada and the United States from 2005 to 2008? Enter the Crossjoin function. Crossjoin takes two or more sets for arguments and returns you a set with the cross products or the specified sets.

Crossjoin ({[Product].[Category].[Bikes], [Product].[Category].
[Components]}, {[Geography].[Country].[Canada], [Geography].[Country].
[United States]}, {[Date].[CY 2005] : [Date].[CY 2008]})

The MDX queries can be written using line-break to add visibility to the code. So each time we write a new set and even tuples, we write it on a new line and add some indentation:

Crossjoin (
{
[Product].[Category].[Bikes]
, [Product].[Category].[Components]
}
,
{
[Geography].[Country].[Canada]
, [Geography].[Country].[United States]
}
, {[Date].[CY 2005] : [Date].[CY 2008]}
)

Step 3 – FROM

The FROM clause defines where the query will get the data. It can be one of the following four things:

  1. A cube.

  2. A perspective (a subset of dimensions and measures).

  3. A subcube (a MDX query inside a MDX query).

  4. A dimension (a dimension inside your SSAS database, you must use the dollar sign ($) before the name of the dimension).

Step 4 – WHERE

The WHERE clause is used to filter the dimensions and members out of the MDX query. The set used in the WHERE clause won't be displayed in your result set.

Step 5 – comments

Comment your code. You never know when somebody else will take a look on your queries and trying to understand what has been written could be harsh. There are three ways to use delimit comments inside the query:

  1. /* and */

  2. //

  3. -- (pair of dashes)

The /* and */ symbols can be used to comment multiple lines of text in your query. Everything between the /* and the */ symbols will be ignored when the MDX query is parsed. Use // or -- to begin a comment on a single line.

Step 6 – your first MDX query

So if you want to display the Resellers Sales Amount and Reseller Order Quantity measures on the columns, the years from 2006 to 2008 with the bikes and components product categories for Canada. First, identify what will go where. Start with the two axes, continue with the FROM clause, and finish with the WHERE clause.

SELECT
{
[Measures].[Reseller Sales Amount]
, [Measures].[Reseller Order Quantity]
} on columns,
Crossjoin(
{[Date].[CY 2006] : [Date].[CY 2008]}
, {
[Product].[Category].[Bikes]
, [Product].[Category].[Components]
}
) on rows
FROM [Adventure Works]
WHERE {[Geography].[Country].[Canada]}

This query will return the following result set:

 

 

Reseller Sales Amount

Reseller Order Quantity

CY 2006

Bikes

$3,938,283.99

4,563

CY 2006

Components

$746,576.15

2,954

CY 2007

Bikes

$4,417,665.71

5,395

CY 2007

Components

$997,617.89

4,412

CY 2008

Bikes

$1,909,709.62

2,209

CY 2008

Components

$370,698.68

1,672

Summary

In this article, we saw how to write the MDX queries in various steps. We used the FROM, WHERE, and SELECT clauses in writing the queries. This article was a quick start guide for starting to query and it will help you write more complex queries. Happy querying!

Resources for Article :


Further resources on this subject:


Instant MDX Queries for SQL Server 2012 [Instant] Learn how to write MDX queries from SQL Server Analysis Services 2012 cubes quickly and easily with this book and ebook
Published: August 2013
eBook Price: $19.99
See more
Select your format and quantity:

About the Author :


Nicholas Emond

Nicholas Emond has been working in Information Technologies for the last eleven years. His past jobs were as a programmer with VB.NET, administrator of a corporate SharePoint intranet and business intelligence developer when he started to use the Microsoft BI stack since SQL Server 2005. Currently, he is a business intelligence consultant working in Montreal, Canada. Nicholas has certifications in Microsoft SQL Server and Microsoft SharePoint and he is a Competent Communicator from Toastmasters International. He likes to read books on computing and personal growth. MDX Queries for SQL Server 2012 Starter is the first book written by Nicholas Emond, and it won't be his last.

Books From Packt


 Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

PostgreSQL Server Programming
PostgreSQL Server Programming

Learning SQL Server Reporting Services 2012
Learning SQL Server Reporting Services 2012

Microsoft SQL Server 2012 with Hadoop
Microsoft SQL Server 2012 with Hadoop

Oracle SOA Suite 11g Performance Tuning Cookbook
Oracle SOA Suite 11g Performance Tuning Cookbook

Mastering SQL Queries for SAP Business One
Mastering SQL Queries for SAP Business One

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

 SQL Server 2012 with PowerShell V3 Cookbook
SQL Server 2012 with PowerShell V3 Cookbook


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
q
b
f
6
A
r
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software