Dave Rodabaugh's Analysis Services interview questions

My Analysis Services Interview Questions

My good friend Jon Baker suggested that I start a blog on this site, and as I tend to follow the advice of those smarter than I, here it is.  As noted by the title of my blog, I’m a DW/BI guy with extensive experience using Microsoft SQL Server, Analysis Services, Reporting Services, and soon Integration Services.  I’m in Columbus, Ohio (GO BUCKS!) and like all multidimensional guys, I’m a little crazy.  Not overtly so, of course.  Just off-kilter a little.

One day Jon and I were swapping interview stories.  (He's posted about this very topic at (broken link!) Is this a conspiracy?  Oh yes.) He fired his very best interview question at me and I flunked it in spectacular fashion.  That got me to thinking about how I interview DW/BI candidates, particularly those who claim to know Analysis Services.  We decided that some of these stories were pretty good, so I'll be following this post with my favorite interview questions, and some of the responses I've received.

My last big client was The Home Depot.  For 18 months I traveled between Columbus and Atlanta to help pioneer an Analysis Services implementation in their Enterprise Data Warehouse (“EDW”) group.  In that time we always seemed to be short on talent, so I was asked to interview candidates.  I probably interviewed a dozen candidates in about nine months, most of whom had Analysis Services on their resumes, and most of who really knew little about it or how to use it.

I admit to being a real hammer when conducting an interview.  I take an adaptive approach, by starting with the high-level questions about architecture, business problems, and design philosophies.  If the candidate fails those questions (and most do) then I begin to drill down into their technical knowledge.  Just because a candidate can’t fill the role of architect doesn’t mean they aren’t productive when working under an architect.  I’m sad to report that most people who fail the high-level questions also seem to know very little about Analysis Services’ product features.  What’s shocking is that some of these people are actually giving clients architectural advice!  Why can’t people admit “I don’t know the answer to that question” when peppered in an interview?  Why try to bluster and bluff your way through it?  Do you think I won’t know what you’re doing?!  (Apparently, the pretender detector for most interviewers isn’t very sensitive as many of these people continue to land Analysis Services gigs.) 

This transcends race, gender, nationality, and educational pedigree.  I’ve interviewed Americans, Russians, a Serb, an Iranian, and quite some number of Indians.  I will admit that I’ve had the most trouble with the country presently serving in a large outsourcing role.  I’m pretty sure there are talking points and that candidates are being coached because I see unswerving devotion to their use.  Supposedly, the interviewer will lap up the answer and be convinced of expertise!  As we’ll see, these talking points are vaguely reminiscent of the Underpants Gnomes in a particular South Park episode.

So this is the first in a multi-part series about interviewing Analysis Services candidates.  I offer these stories because they’re humorous and many of you will nod in acknowledgement.  Some of you who read these may think I'm a real blowhard.  Please be assured that I intend no offense to anyone! I've mentored men and women of varying nationalities, and they have taught me a thing or two as well.  I also write these stories because if you’re considering a career in DW/BI using Analysis Services, these can help you.  All of us who pitch our tent in the Microsoft camp do well when Microsoft does well, and we help them by attaining product expertise and plying our craft in the field.  I’ve worked hard to attain expertise (and hopefully I have). If I can do it, so can you.

The next installment of My Analysis Services Interview Questions:  Cool Business Problems.




Part II of My Analysis Services Interview Questions: Cool Business Problems

I’ve seen a variety of techniques used by staffing companies to combat the beating I’ve given their previous candidates.  Though I’ve never been hit by the bait-and-switch, candidates have tried to look up answers during the interview; they’ve had somebody in the room with them; and the last technique seemed to be the group interview.  In addition to the candidate, the phone interview would include the sales rep for the staffing company, and often another technologist.  Why?  To write down the questions, of course!   I can only guess that the last candidate I rejected complained about how unfair the interview was, and the recruiter wants to be sure they get every advantage possible and they think they’ll get a leg up by writing what I ask.  Fair enough, but it won't help you because I’ve learned some great interviewing techniques, like asking follow-on questions based on your first answer.  I understand that the goal for a staffing company is to put cheeks into paying seats, but that's not my goal when I'm interviewing, or when I'm a candidate.

I use an axiom in the classroom and with client personnel who are learning business intelligence:  if you can’t articulate the answer, you don’t know it.  Some will say, “I know the answer.  It’s in my head but I can’t tell you what it is.”  I repeat:  if you can’t articulate something, you don’t know it.  The only evidence of knowledge is its articulation, so let’s lay out a couple of rules.

  • Rule 1:  If you can’t articulate something, you don’t know it.
  • Rule 2:  If you think you know it but just can’t communicate it, refer to Rule 1.

I always start with high-level business questions.  I believe that technical knowledge is the easiest of all skills to acquire.  Writing, speaking, architecting, and mentoring are far more difficult.  Complex business problems require passionate people with creative solutions.  The high-level questions explore all of these at one time.  I want to know if you’re a thinker and a learner, squeezing as much as possible from your experiences.

I usually lead with the following:

“Tell me about some great business problems you’ve solved with Analysis Services.”

I expect to hear an answer like this:  “I used Analysis Services to provide certification supplier metrics for a retail supply chain.  The database and cube(s) that provided these answers had 45 dimensions, about 10 measures, and 25 calculated members.  My favorite metric was a ratio of dollars ordered to dollars received because the business could ensure they were getting what they paid for. Additionally, they were able to reduce back office labor for receiving because they could avoid detail receiving for suppliers they knew were doing well.”

Some of the answers I’ve actually received are downright comical.

“I used surrogate keys.” That’s very nice, but it’s hardly a business problem.

“I built a star schema.” Stating an obvious architecture is not evidence that you really know what you’re doing.

“I used MDX.” That’s good!  Perhaps you used dimensions and cubes as well, but quoting product features provides no evidence of problem solutions.

But my favorite is a maddening three-part answer vaguely reminiscent of the Underpants Gnomes in a certain South Park episode.  The Underpants Gnomes were “experts” in big business because “underpants are big business!” The Underpants Gnomes had three steps:

  • Step 1:  Collect underpants.
  • Step 2:  ?
  • Step 3:  Profits!

Here’s what I get as the most common answer to the question about business solutions.  “First, I talk to the business and gather requirements.  Then I make a design.  Then I build software.”  See?  It IS the Underpants Gnomes, who would say it like this:

  • Step 1:  Collect business requirements.
  • Step 2:  Make a design.
  • Step 3:  Build software.

Of course, this isn’t a business solution.  It’s an approach to solving problems and it’s so obvious that nobody should have to say it.  It would be like going to a staff meeting and hearing a low-level manager lecture you for 15 minutes on the importance of profit.  (Duh.  And yes, I’ve experienced this with a prior employer.  Note I said "prior.")

It may be obvious, but I hear it repeated so many times. Over and over and OVER again.

In an earlier installment of this blog I noted the existence of talking points.  I invariably get these from candidates stemming from one Asian country.  It’s like there’s a four hour seminar that instructs candidates, “Americans LOVE process.  If you tell them this, they’ll see that you’re truly an expert and will have no choice but to take you!”  I’ve had candidates stick to this party line even when I tell them, “That isn’t a business problem.  What interesting business problems have you solved?”  I’ve actually told more than one candidate to stop repeating the line because I don’t want to hear it again.  (I must be the Dr. Laura of BI interviews.)  I usually provide examples in hopes of spurring the candidate to action because sometimes there are linguistic difficulties.  Some candidates respond well after that; some don’t.  In a couple of cases the interview has ended because the candidate can’t deviate from the party line.

Why can’t they answer the question?  There are many reasons.  After teaching SQL Server to adults for more than five years, I’ve taken a very dim view of the average adult’s learning ambitions and thought intensity.  Most people just don’t pay attention and learn lessons.  Others are terrified of talking, but it’s an interview and you must talk.  I think the majority of them simply don’t know the answer to the question but they believe such an admission is failure.  Well, you surely won’t be recommended for an architect position if you can’t tell me what cool business problems you’ve solved, but you may have a personal epiphany that leads to personal growth.

Mostly, candidates responding in this fashion haven’t really used Analysis Services to any great degree even though their resume says they have.  They’ve had success bluffing their way with interviewers who can’t tell the difference.  Personally, I only put on my resume those things in which I am competent or expert but I seem to be a rarity.  In fact, here's some advice:  I don't like 10 page resumes.  I don't mind four or five pages because it's my duty to read your resume and see what you've written.  If you've worked for 10 or 15 years, I expect more than two pages.  But if you get much past five pages, you're either playing cut-as-paste patsy, or you're telling me stuff I don't care about, like what you wrote in FORTRAN or how many NIC's you replaced.  BTW, if you feel the need to tell me that, this doesn't help you and may hurt you. If this is a BI interview then I want to know what you've done that is pertinent to BI. (And don’t get me started on recruiters who don’t actually READ your resume.  I get called frequently for Oracle/Informatica work even though those words don’t appear on my resume.  Yes, many of the same letters in those two words appear on my resume, but not the actual words themselves.  Would you believe that today I got a hit for a UNIX/Sun/Solaris admin?  Huh?  You can't find any of those words on my resume with just a keyword search!  But I digress...)

The lesson here is to pay attention to your experiences.  Wring every last drop of wisdom from your work.  Be prepared to talk about those experiences and what you’ve learned.  You never know when an interviewer will ask you about them!

NEXT.  Part III of My Analysis Services Interview Questions:  Architectural Philosophy



Part III of My Analysis Services Interview Questions: Architectural Philosophy

In the previous installment of Dave’s Cube, I laid out my first interview question for Analysis Services candidates.  Since I use an adaptive technique, I start with the hard questions and move to technical questions later (if necessary).  My first interview question is always about cool business problems that you’ve solved with Analysis Services.  As noted, that’s rough for most people, but it doesn’t get easier with the second question.  

My second question is always about architectural/design philosophies.  I don’t ask too many closed-ended questions because I want you to talk, so I know what you know.  I usually ask the following question:

“Do you have any particular design philosophies or architectural principles that you will use to guide this client toward best practices?”

Believe it or not, you can say something different than I would without consequence, as long as you can provide your reasoning. (You must also be correct, of course.  Telling me it’s red when I know it’s blue won’t work, even if you have reasons.)  I’m a stickler for best practices because they are the authority upon which you stand when telling a client how best to implement a product they’ve probably never used.   Here are some of the answers I expect to hear:

“I recommend that a client always carry as much detail as possible into the data warehouse and into a cube.  Details may use extra space and CPU cycles, but they ensure the future value of the application because they enable the most calculations to be performed upon that data without rework back into the data warehouse and/or cube.  Details often carry negligible incremental cost on first development.”

“I like to use as many dimensions/attributes as suggested by the data – even if those attributes are not in the business requirements.  The incremental time required to design and populate those extra attributes is usually negligible, but the business will often request those dimensions as filters and slicers for calculations they modify or identify late in the process, or even after a project is closed and customer acceptance is complete.”

“I design first for query performance, and then second for processing performance.  The purpose of an Analysis Services implementation is to serve quantitative answers to end users; query performance is paramount.  Processing performance issues can usually be solved by tuning in the relational layer and by eliminating joins in the cube processing statement.”

These answers tell me two things.  First, you know best practices and you’ve actually used them.  The reasoning behind a philosophy usually only comes from experience.  Second, I know immediately when you are a learner and a thinker because such people answer this question *immediately* and with passion.

I have received the same spate of humorous answers I’ve received for my first interview question:

“I use surrogate keys.” Personally, I prefer skeleton keys since they’re very versatile.

“I love star schemas.” Me too.  And Buckeye football.  And Chevrolet Corvettes.  And my kids.  But I digress… None of these are architectural philosophies.

Of course, the Underpants Gnomes hold sway in the mind of many people answering this question.  Recently I figured out why.  Underpants Gnomes wear cones on their heads.  Dogs hate cats.  Buckeyes and Wolverines are at perpetual enmity. Everybody knows that cones hate cubes, but they don’t have an annual contest to settle the score one November Saturday at noon (unless ABC says to play at 3:30 PM).  The Underpants Gnomes consider infiltration into the minds of aspiring Analysis Services candidates a dutiful service to Gnomekind!  Kill the cubes!

Has a candidate ever answered quickly and passionately with a wrong answer?  Only once.  We were interviewing a candidate from NYC who said he was an Analysis Services architect.  He was articulate, smooth, and eager.  He laid out some very good business problems he’d solved with Analysis Services, and we were feeling good since it’s hard to find experienced, thoughtful Analysis Services resources.  His answer to this question flabbergasted us.  He said, “Use as few dimensions as possible.”  Upon inquiry, he divulged his reasoning.  “Every time I add more dimensions to the cube, cube processing slows down to the point where I can’t process the cubes.”  This man was freely sacrificing the power of multidimensional databasing (dimensions) on the altar of performance because he couldn’t figure out how to tune the technology!

I told him that not only did the three people on our end disagree with him, but we had empirical evidence that he was wrong.  We had cubes with 50 or more dimensions in them, yet we could process them just as quickly as if they had only five dimensions.  Since he failed a high-level question, I immediately drilled into technical knowledge.  I asked him if he knew how to optimize a cube schema.  Oddly, he answered this question by discussing dimension optimization, a question that flunks all but the most knowledgeable of Analysis Services people.  He said that he could make dimension keys and names unique, which is great but doesn’t help you optimize a cube schema.

Incredibly, this promising guy had used Analysis Services for years but had never once bothered to learn about optimizing a cube schema.  I explained to him that when he adds dimensions to a cube, Analysis Services adds a join to the SQL statement it issues against the data provider.  (He said he’d never looked at the SQL statement, a sensible answer since he didn’t know he could do anything to change it.)  By optimizing the cube schema, you reduce or completely eliminate the joins in the cube processing statement.  Naturally, queries against a single table in the data source can have nearly instantaneous response times and marvelously high row payout rates, often choking either the RDBMS or Analysis Server.  You can theoretically add an infinite number of dimensions and not appreciably slow cube processing.  (Remember: I was interviewing for Analysis Services 2000 positions; the answer will vary somewhat for SSAS.)

He knew he was cooked.  You could hear it in his voice.  I think the full weight of his error hit him quickly.  He hadn’t just missed a prominent performance tuning feature in Analysis Services.  No, he’d been giving clients architectural advice based on this glaring error.  I told him that after he hangs up the phone, spending two hours with Books Online to read up on this feature would make a much better Analysis Services expert than he was before the phone call.

It was obvious that he was experienced, smart, and teachable.  What he didn’t know what that we asked the boss if we could bring him on anyway since we were pretty sure we could fill in his holes with little effort.  Sadly, his hourly rate was so high that the boss rejected him.  If you’re going to ask for a mountain of money you need to at least know as much (and hopefully more) than everybody else.  Dave Ramsey says, “In our economy, you get paid for what you know.”  I modify that to the following: “In our economy, you get paid more than the next guy depending on how much more you know than the next guy.”  We’re both right.

Study hard when mastering Analysis Services!  It pays!

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.


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!


Original broken links for reference:
Comments (24)
24 Thursday, 05 August 2010 16:21
Nathan Proper
It's funny how people who don't know how to answer the questions assume the questioner is an arrogant racist. Did you not notice that he repeatedly said that candidates who couldn't answer the first couple questions still had potential as engineers, just not architects. There is a difference. Just because you can drive a car and even change the oil doesn't mean you know how to build one. So if your resume claims you're an architect, there's nothing wrong with making you prove it by answering difficult questions. Again - as he pointed out time and again - it's less about the "right" answer and more about the thought process behind it. If you can't handle thinking during an interview, McDonald's is probably hiring...
23 Friday, 30 July 2010 05:55
First time I have come across an article written by a computer.

Computer can be fooled, he is a fool, racist.
22 Tuesday, 20 July 2010 07:12
Thank you, I have recently joined a new group in a government body and they want to do "BI". I have been working with the SQL Server stack for over 10 years and still learning. He is bringing me candidates with the skill set "Sql expericence = medium" so I sent him your top level question to use to screen them in.
21 Thursday, 08 July 2010 17:49
45 dimensions per cube? obviously this guy needs to refine his design philosophy first before interviewing any more potential candidates. I know this kind of "know-all" kind of guys who sits on the interview board and scare already stressed out candidates in the interview environments.

Good luck to those who happens to have a "massive" bad luck of working with him.
20 Saturday, 22 May 2010 05:24
The author seems to be know the subject which is fair enough BUT its his attitude that would bother anybody wanting to hire this guy or his consultanny he works for. I think he seems to have not resolved some personal issues which he seems to take out on his potential employees. I have quite a few years on SSAS but would never hire someone with his attitude or work for someone like him. I see a potential disaster on the client side with this guy unless he changes his attitude.
19 Friday, 21 May 2010 18:05
Hmm! Ok, some of your interview questions are alright. But, from your particular manner of presenting your philosophy of interviewing I really doubt if you'd pass muster for one of my interviews. In fact, since you seem to be a consultant I have put you on my 'skip list' should my company be looking.
18 Thursday, 20 May 2010 21:32
Yes it seems like this guy will take someone who is maybe 95% there and lump them in the "doesn't know anything" category. Here's my prediction -- he's interviewed 100's of people but has never once found the right person. They may have had the right answer, but because they could not explain it exactly the way he wanted, then "they didn't know anything."

Lastly the Microsoft BI stack consists of more than SSAS. So unless you are on a big team where you are looking for someone who will do nothing other than SSAS developmet (e.g. they are not going to do the SQL work or SSIS work...) then this will potentially remove too many candidates from the selection.

Quite frankly, he could easily be fooled. It looks like you just memorize the answers to questions on the cert test and you are fine with this interview. Of course this does not necessarily net a person that can actually think and produce a business solution.