Dave Rodabaugh's Cube

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.