Dave Rodabaugh's Cube

Part V of My Analysis Services Interview Questions: The Most Common MDX Functions

I have a litmus test for true multidimensional expertise:  MDX.  I’ve found MDX to be so potent that I freely admit my own inadequacies.  I study it, I use it, and I consult with others who do the same, but I know there’s more.   If somebody says they know Analysis Services, I want to see their MDX.  Show me somebody who doesn’t know MDX, and I’ll show you a pretender.

Remember, I don’t ask closed-ended questions.  I’ve heard interviewers say, “Do you know MDX?”  Of course, the answer is always “Yes” but this answer tells you nothing of what the candidate actually knows.  I have two favorite MDX questions.

Question 1:  What MDX functions do you most commonly use?

This is a great question because you only know this answer by experience.  If you ask me this question, the answer practically rushes out of me.  “CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate.  My personal favorite is CrossJoin because it allows me identify non-contiguous slices of the cube and aggregate even though those cube cells don’t roll up to a natural ancestor.”  Indeed, CrossJoin has easily been my bread and butter.

If you stammer and stutter, you’re in trouble.  If the first word out of your mouth is “sum” without any explanation of how you use it, you’re in trouble. 

Question 2:  Where do you put calculated members?

The reflexive answer is “in the Measures dimension” but this is the obvious answer.  So I always follow up with another question.  “If you want to create a calculated member that intersects all measures, where do you put it?”  A high percentage of candidates can’t answer this question, and the answer is “In a dimension other than Measures.”  If they can answer it, I immediately ask them why.  The answer is “Because a member in a dimension cannot intersect its own relatives in that dimension.”

Then I ask a real toughy.  “Where would you put a calculated member if you wanted it to intersect all other dimensions?”   You can hear the wheels turning with this question.  You can’t put it in the Measures dimension because it wouldn’t intersect the other measures.  You can’t put it in, say, the Time dimension because it wouldn’t intersect the other Time members.

The answer is simple but not obvious unless you’re very creative or somebody told you the answer. (Somebody told me the answer when I first started learning Analysis Services.)  You create a dummy dimension solely for the purpose of holding those calculated members!  My friends and I refer to this dimension as a “hanger dimension” because the only function it serves is to “hang” calculated members.  Any member placed therein will intersect all other dimensions.

In fact, you can use multiple hangers.  This is particularly handy if you have calculated members that need to intersect all other user-defined dimensions, Measures, *and* another hanger dimension.

In an earlier post I mentioned a fabulous lady who has taught me many things.  She wasn’t able to answer this particular question.  When I told her the answer, she immediately understood it.  It didn’t affect my recommendation to the boss because she was so good.  You’ll never believe what she told me when she arrived for her first day of work.  She said, “Do you remember the question you asked me about the hanger dimension?  After I hung up the phone, I pulled up Analysis Manager and tried it.  It worked really well.”  Wow!  No wonder we like her so much!  She even learns during interviews!