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 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.
-
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.
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:
- My Analysis Services Interview Questions
- Part II of My Analysis Services Interview Questions: Cool Business Problems
- Part III of My Analysis Services Interview Questions: Architectural Philosophy
- Part IV of My Analysis Services Interview Questions: Technical Features
- Part V of My Analysis Services Interview Questions: The Most Common MDX Functions