Hello World

Objective: To understand a simple MDX query

Before we begin let me lay down the golden rule of understanding MDX. Although MDX resembles SQL, but MDX is very different from SQL. Therefore it wise to ignore all SQL concepts while writing MDX.

Lets execute our first MDX query. Connect to our Adventure works cube and execute the below query

–My first MDX Query.
//Again my first MDX query
/*
And Again
my first MDX query
*/
select
from
[Adventure Works]

Now lets explain what is happening in the above query. The first Six lines are comments, the third comment being a multi line comment.
The elements names may be enclosed in [] characters. However in case of space or special characters within an element name, [] are must. For instance if we remove [] from “Adventure Works” we get an exception.
Although the above query seems to be incomplete but it still returns result. Don’t worry we will soon understand what it has returned and why.

You have just been introduced to “Select” clause and the “From” clause.
Select clause in MDX is further supported by the “On” clause. The “On” clause specifies the axis for a dimension or Measure. We will see the “On” clause in a bit.
“From” clause in MDX is used to specify the cube or the sub-cube.
Lets try another query

select
from
[Adventure Works]
where [Date].[Calendar Year].&[2012]

We simply added a “Where” clause to the original query. The “Where” clause will filter the result set. The expression “[Date].[Calendar Year].&[2012]” is one of the methods to access a member of a dimension(we will explain how to fetch members of a dimension later). Hence the above result set is only for 2012.
Now lets display a Measure of our choice. “Internet Sales Amount”

select [Measures].[Internet Sales Amount]
on columns
from
[Adventure Works]
where [Date].[Calendar Year].&[2012]

In the above query we appended
“[Measures].[Internet Sales Amount] on columns “, as mentioned above “Select” clause is assisted by the “ON” clause. The above statement we want “Internet Sales Amount ” on the “Column” axis. MDX has multiple axis available, but we we will only be using “Columns” and “Rows”. We could also have written the above query by using
“[Measures].[Internet Sales Amount] on 0”. The axis numbering is zero based and an axis cannot be skipped.
SELECT clause specifies what you want to display. Within the SELECT clause you have the ON clause, which helps specify the axis on which to display. So in the example above we want to display “[Measures].[Internet Sales Amount]” on columns axis.
Lets try another query.

SELECT
[MEASURES].[INTERNET SALES AMOUNT]
ON 0,
[PRODUCT].[CATEGORY].[CATEGORY].MEMBERS
ON 1
FROM
[ADVENTURE WORKS]
WHERE [DATE].[CALENDAR YEAR].&[2012]

In the above query we did quite a few things, let go over each one.
Firstly the entire query was in upper case, MDX is neither case-sensitive nor line-oriented.
Next we replaced the “Columns” and “Rows” with 0 and 1. Next we returned the breakup of “Internet Sales Amount” by product categories.
We also used the “member” function. The “member” function retrieves all the members of a hierarchy or a level of a hierarchy(more on this later).

Multiple parts within element names are separated by “.” so for instance in “[Product].[Category].[Category].members ” product is the dimension and category is the attribute,then the next category is the level. Again in “[Measures].[Internet Sales Amount]” “Measures” is the dimension and “Internet Sales Amount ” is the measure/attribute (Yes Analysis Service does consider Measures as a dimension).
Lets try another query

select
[Measures].[Internet Sales Amount]
on 1,
[Product].[Category].[Category].members
on 0
from
[Adventure Works]

In the above query we swapped the display axis of “Internet Sales Amount” and “Product.Category”. Not only did we swap their positions, we also mentioned the axis 1 before axis 0. However we cannot skip an axis. Take a look at the query below.

select
[Measures].[Internet Sales Amount]
on 1
from
[Adventure Works]

This would result in the following exception “Query (2, 1) Axis numbers specified in a query must be sequentially specified, and cannot contain gaps”. Just replace “1” with 0 and every thing is good to go.

Summary

In this topic we explored the following concepts

  1. Comments in MDX are inserted by “–” , “//” or “/* */”
  2. Element names in MDX may be enclosed in “[]”. In case if they contain space or special characters then it is must to enclose then in “[]”
  3. Multiple parts within element names are separated by “.”
  4. You cannot skip an Axis in MDX
  5. Basic elements of MDX query are Select {…} on 0, {…} on 1 From Cube
  6. MDX is neither case-sensitive nor line-oriented
  7. To refer all members within a hierarchy, you can use [Dimension].[Hierarchy].[Level]

Leave a Reply

Your email address will not be published. Required fields are marked *