Dave Rodabaugh's Cube

Part IV of My Analysis Services Interview Questions: Technical Features

In parts two and three of this multi-part series on Analysis Services I discussed interview questions about cool business problems and architectural/design philosophies.  The third level of questioning I use deals with product features.  You’ll always get these questions if you fail one or both of the first two because even if you aren’t an architect, you could be very productive while working under an architect.  Plus, if you’re an ambitious learner, you’ll become an architect by learning from an architect.

Sometimes I even ask the technical questions if you pass the first two questions.  Remember, I’m not just looking for answers.  I’m also watching your thought process.  I want to see passion, and I’ve found the best people can hardly wait to tell you about the great things they’ve done, and when you ask them a question, the answer practically leaps out of them.  So if you answer the first two correctly, but I’m not convinced, you may still get the technical questions.

(Interesting story:  one fabulous lady I worked with was annoyed when we hired a guy who clearly knew what he was doing, but of whom I asked no technical questions.  She said she was bothered by this, and asked my why I’d done this.  I said, “Because he clearly knew what he was doing and I’m satisfied that he knew the technical details as well.” He was passionate and articulate.  But I told her that we’d ask technical questions in the next interviews if it made her comfortable, and we did that for two more interviews. But she changed her mind when she figured out that people who really know how to use Analysis Services also know its features.  She once noted that I didn’t hammer her in her phone interview.  I replied that I could tell she was thoughtful, passionate, and a great learner.  I wasn’t wrong.  To this day that lady is one of the very finest technologists I have ever met, and I would work with her anywhere, anytime.  Though I have left the client for whom she works, we still talk regularly.)

Even my technical questions are stratified.  My favorite technical question is “How do you optimize a dimension?”  (Remember, this question’s context was Analysis Services 2000.)

The correct answer has four parts:

(1)     If possible, ensure that member keys are unique across a given level, and

(2)     If possible, ensure that member keys are unique across the entire dimension.

(3)     If possible, ensure that member names are unique across a given level, and

(4)     If possible, ensure that member names are unique across a given dimension.

When this is achieved, you have very compact dimensions.  When answering queries, Analysis Services can avoid assigning context to members by using ancestral chains, and things get very quick and compact.  In my mind, this is the first technique for excellence in Analysis Services, and only two or three people have ever answered it correctly.  Usually the respondent, eager to get up after being knocked down by the two prior questions, blurts out, “I go to Tools and select Optimize Schema.”  They’re usually shocked when I inform them that this isn’t the correct answer because Optimize Schema optimizes a cube, and I asked about optimizing a dimension.  Honestly, I’ve never had a candidate answer the question correctly if this is their first answer.

My second favorite question is about optimizing the cube schema.  Nearly all candidates know what this feature is and have used it.  I avoid closed-ended questions like “Have you ever optimized a cube schema?” because the answer is a simple, “Yes.”  This doesn’t tell me what you know, so I always ask what optimizing a cube schema does.  Ironically, the same people who use schema optimization to answer the dimension optimization question don’t know what schema optimization actually does, or why it’s used.  The answer is that it reduces or eliminates joins in the cube processing SQL statement.  It may be the most powerful technique to improve cube processing performance (assuming ample hardware and parallel partition processing that doesn’t swamp the server, and moderate aggregations).

One candidate, who took a beating on the two high level questions, also took a beating on these two technical questions.  He was obviously unaccustomed to this because after a half hour he said, “Well, maybe you just haven’t actually done this.”  This new tack caught me a little off guard since I had explained to him he couldn’t articulate any cool business problems, he didn’t have any strong architectural philosophies with which I agreed (and I told him why), and he didn’t know how to optimize a dimension.  I even told him *why* these things were important and what the right answers were.  In addition to getting the answers wrong, he demonstrated that he wasn’t teachable, a bad trait for any technologist since we must learn continuously.  He was notable not only because of his belligerence, but also because he only had one name on his resume.  He’d concatenated his first and last name into a single word, which had eight syllables and was in a foreign tongue.  He left quite an impression, and six months later when his resume landed on our desks from a different staffing company, we immediately put it into the circular file.  Ignorance, belligerence, and a goofy name are a bad combination.