I always believed that it’s not that you can’t learn but that the teacher can’t teach.  Bad teachers galore and it kills generations of intelligent minds and learning drives.  Looking back on my informative years, there were only two teachers I can really call teachers. 

As regards to MDX, I have to say, it’s a hard language.  It’s hard because it has to deal with more than columns and rows and there are multi dimensions,  hierarchy and levels.   That’s why so few people really know MDX versus SQL.  People have to rely, sometimes, exclusively on UI tools for querying MD database.

Following is an article that will evolve as I am playing with MDX and Adventure Works.  All comments are welcome. 

I was extremely frustrated when I first looked at MDX.  So I decided to start a crusade to remove some of the ambiguities around Hierarchy, level and members.  It’s very important to make sure you understand them crystally clear.  Any ambiguities about those concepts will hinder your progress in mastering MDX.

We are going to explore dimension "Product" in "Adventure Works DW".  Let’s look at the design first.

Fig 1 A user defined hierarchy and levels

Fig 2  Level 0 is the ubiquitous (All) that is present in all hierarchies (user defined and attribute hierarchy)

To identify a specific member you should follow this formula:
[Dimension].[Hierarchy].[Level].[Member].
Don’t try to short-cut things!  All the troubles arise from laziness.  The MDX designer has given a blank check to laziness.  That’s insane.  Please make it mandatory to write in the correct format for MDX.  No excuses for laziness.  I give my student F if they don’t follow this rule!   Now we are onto the saga to see how disgusting parts have been omitted from current UI tools.  It’s a crime!

But complaint is complaint.  We still have to be able to understand other people’s MDX.

Here is the general rule.  If it is not explicitly written out then it’s default to
 [Dimension].[Hierarchy].Levels(0).Members(0). 
Levels(0) is [(All)].  Please note, it’s [(All)] not [All].

Let’s ask what the level [(All)] is.  I like to ask all the way so my teacher can’t answer.  It’s an important way of learning.  Don’t assume you know it if you have doubts.  Unfortunately, in today’s teaching world, professors are too busy to answer questions.  And they think you are stupid if you ask the obvious.  Sometimes, they themselves don’t really know the answer. 

[(All)] is a level where you have your undivided total.  For example, if you sell 3 hats in WA, 2 hats in TX, 7 hats in GA.  At [(All)] level you sold 3 + 2 + 7 hats.  Then your boss is interested in how many you sold in TX and you start to divide the number at [(All)] level to a level below it–State. Perhaps your girl friend is interested in how many your sold in WA.

[(All)]  —12
State–WA 3, TX 2, GA 7

Now you have two levels, [(All)] and [State], which also can be written as Levels(0) and Levels(1).

At Level 1 you have 3 members right now,  they are WA, TX and GA, which can be written as Members(0), Members(1) and Members(2) depending how you sort them.

You can use the following query to
return the name of the level of a member or just the name of the a level: 

WITH
MEMBER Measures.x AS

[product].[Product
Categories].Level(0).Level.Name

SELECT
Measures.x ON 0

FROM
[Adventure Works] 

The
result is: 
(All) 

What exactly is a member?  There is no precise definition for member.  At least I can’t find one googling around.  But conceptually, a member is a dot you can put on the dimension axes.  Anything you can put on the dimension axes is a member.  You can’t put hierarchy nor level on the dimension axes. Members going from the top level 0 to the lowest level (we call leaf level) give you finer and finer granularity, increasingly more detailed division of the total  than the member a level above.  I think I need a Figure here to illustrate this.

We can say members of a hierarchy,
members of a level.  

Members of a hierarchy is a
collection of members from level 0, level 1, level n … 

[Product].[Product
Categories].levels(0).MEMBERS 

 + [Product].[Product
Categories].levels(1).MEMBERS

 + [Product].[Product
Categories].levels(2).MEMBERS

 + [Product].[Product
Categories].levels(3).MEMBERS      ON
ROWS

FROM
[adventure works] 

is equivalent to 

[Product].[Product
Categories].MEMBERS ON ROWS

FROM
[adventure works] 

When you come down to a member, you
will encounter following concepts: 

Children of a member

Parent of a member (Ancestor? and Parent? Don’t get confused.  Your parent is your Ancestor as well as your parent’s parent.) 

Ancestor of a member (there are many ancestors for a member; MDX has a way to specify which ancestor you are talking about; later)

Descendants of a member (there can
be many descendants of a member, MDX has a way to specify which descendants you are talking about; later) 

We look at Fig 3 to understand
Children and Parent 

 Fig 3 Children and Parent

And Descendants?  Let’s look at Fig 4

Fig 4 Descendants of member "Accessories"

What are exactly Children of a
member? How does a member get children? 

Look at Fig 5 and you will
understand:

Fig 5 Children of a member are
members of the level below 

We continue our saga of debunking the myth of MDX hierarchy, level and members in Part Two.