(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.

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:

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:

• Connecting to Microsoft SQL Server Compact 3.5 with Visual Studio [Article]
• MySQL Linked Server on SQL Server 2008 [Article]
• Microsoft SQL Azure Tools [Article]