MicroStrategy Interview Questions
It seems like a tradition for MicroStrategy Blogs to post interview questions. I’ve interviewed a lot of Developers and Architects, but giving them a quick version of a Certification Exam never really seemed prudent to me. I’m less interested with their ability to recite textbook definitions and more about their thought process and competancies. Here is a list of interview questions I always ask, my thought process behind it, and the answer I’m looking for.
The questions are broken out into sections. Some I ask everyone, some I only ask where appropriate (for example, if I’m interviewing an entry level MicroStrategy Developer who doesn’t have a lot of MicroStrategy experience, but does have experience with other BI Tools).
Here they are in no particular order:
Q) How did your previous company make a profit?
This is my favorite question, and though I don’t always lead with it, I always ask it. As someone in Business Intelligence, we are directly involved with the business operations. Anyone who is involved with any measurable aspect of the project or thinking more than just the assignment in front of them should have a good enough understanding of the business model to explain it.
Example: If you came from a pharmaceutical company, the answer isn’t, “They sold medicine”, but rather something like, “The R&D group would propose ideas and they would be brought up in front of a board who would estimate their profitability by considering the estimated R&D costs, the expected demand (by the number of afflicted individuals), the patent life and expected profit over generic”.
Q) What are some characteristics of good Project Requirements?
Especially when interviewing for a large shop, Project Requirements can be critical. No matter where you’ve worked, you’ve had to build something off of requirements whether it was explained to you in the hallway or you received a 40 page document. Some can be too long, some can be too short. What are the things you look for and what would you consider to be the essentials?
Example: The project requirements should include a mock up of the target report with formatting and real sample data if possible would be really helpful. Any metric formulas should be full defined and source system and data update frequency identified.
Q) How large was your last group and what was your role?
There are two sides to this question that are interesting. If the candidate is coming from a small group where they performed multiple tasks, it can be a plus since they have exposure to many areas of the system which just increases overall knowledge of any particular area (for example, you have a better understanding of report development if you also understand schema architecture). However if they’re coming into a large group, the lack of working in a large team dynamic can be a tough transition for some. The other side is someone coming from a large group where they performed just a single aspect, like report development, but had no exposure to anything else. Coming into a small team could prove a challenge when they’re not familiar with all of the responsibilities they may need (like schema architecture or administration).
Q) What were the statistics of your last environment? Usage and platform.
It says a lot about someone’s level of involvement if they’re aware about all aspects of your product. If they just do what they’re handed, they’re probably not aware of how the business makes money or how the product even operates. Does the product serve 100 reports a day to 10 users? 10,000 reports to 8,000 users? I think everyone on the team should know those kinds of basic numbers regardless of their function. This question, along with “How did your previous company make a profit” can go a long way to tell you that the candidate is involved more so than an assignment taker. I’d also expect that they would know what Data Warehouse they were hitting, roughly how large it was and how many users they had.
Q) Why is BI and Data Warehouse Important? Why shouldn’t I just use .NET?
Whether we like it or not, we’re all salesmen for BI. From an outsider’s perspective, BI/DW is extremely expensive, so why shouldn’t we just use some “free” alternative like SQL and Microsoft Reporting Services? This is especially dangerous when the business user has some minor technical background.
Example: The Data Warehouse brings all of the company’s data into a single point with an agreed upon set of business rules. When individual groups or analysts are running their own spreadmarts, confusion and conflicting numbers are present. ETL processes provide stable and reliable means of data transport and storage into extremely large and flexible environments, possibly including “As-Is vs As-Was” analysis. MicroStrategy brings rapid development capabilities directly to the hands of a business user with easy report creation and drill down capabilities at the speed of thought. Other products may require manual IT involvement for minor changes, or be difficult to manage when the report and user counts reach thousands.
Q) What’s a Left join?
There was a joke going around a group I once worked in about programmers not knowing what a left join does, and whether or not that was important (they were developing against databases in some cases). I was part of interviewing for a DBA for our group, and thought it would be funny to ask them all what a left join was. Of the 4 candidates, only 1 knew! We were shocked (and ended up hiring the one that knew) and it was an eye opener about the need for technical questions. Though this question is hardly representative of a technical exam, I do enjoy asking it. MicroStrategy is simply a SQL Generation Engine, and if you don’t have a basic understanding of SQL, you wil will struggle. Ironically a Left join rarely comes into play with MicroStrategy, but it’s such a basic SQL question that I feel it shows that the candidate has done at least some level of hand SQL writing and didn’t learn SQL from looking at MicroStrategy reports.
Q) What’s an index and it’s importance?
This greatly depends on the database platform both your company works with and the candidate has experience with, so it could be modified slightly accordingly. The point is that sometimes we write the perfect report and it runs incredibly slow. Although a MicroStrategy developer doesn’t necessarily need to know how to improve that performance, they should be aware of the options and be able to give the DBA a little bit more information than “This query is slow”.
Example: In Microsoft SQL Server, and Index orders the table in such a way that searching for a particular row (via a join or WHERE clause) can rapidly be discovered as opposed to manually searching and comparing every row, much like an index at the back of a book allows you to jump to the page you want rather than flip through every page.
Q) What’s the coalesce function do?
I had done a lot of SQL development prior to entering the world of MicroStrategy, and I admit I had never heard of this function. This question is kind of the opposite of the “What’s a Left join?” question, because I think that you’d only really encounter it with MicroStrategy. If your reports do a lot of multipass SQL with temp tables and use MSSQL or Oracle data warehouses, you’ll see this function used in every single report.
Example: It returns the first non-null value from a list. It’s used by the MicroStrategy SQL Engine to combine multiple passes of SQL when joining, in the event one of the passes didn’t contain data that the others did.
Q) What are some characteristics of a Dimensional Model?
Depending on if you’re interviewing a developer or architect, you’re expected answer to this question is going to vary greatly. For a developer, I’m mostly just looking for some basic understanding of the concept, and for an architect, I’d probably hand them an ERD and ask them to sketch a MicroStrategy schema or vice versa.
Example: In order to easily support prompt loading and aggregate tables, proper dimensional tables are highly recommended for MicroStrategy, which is optimized for this type of schema. These tables allow for quick distinct look ups and fact tables at any level to be joined directly.
Q) What is a Ragged/Recursive/Type 2 Hierarchy?
Just like the previous question, I’d hope that a developer would be aware of these kinds of schemas, but if they’ve never worked with them, I wouldn’t hold it against them. If my project relies heavily on one of them (especially Type 2), then it would be considered quite a plus, but not a deal breaker. For an architect, I’d definitely expect them to be aware of these schemas even if they hadn’t worked with them in the past. I’d also expect an architect to have some idea of how to implement these models in MicroStrategy.
Example: Ragged Hierarchies occur when levels can be skipped. The most common case is an organization chart where an Executive Assistant may report directly to a Chief Executive. A Recursive Hierarchy is when there is an indeterminate number of parent/child relationships embedded in a single table. A Type 2 Hierarchy (or Slowly Changing Dimension) is when the data warehouse stores by As-Is and As-Was to track changes to dimensions over time.
Note that although these are in no particular order, I’ve left the MicroStrategy questions for the end. This is mostly because I think MicroStrategy is a very easy to use tool. If someone had good overall knowledge and charisma, as well as good answers to the Business question section, I’d pretty much forgive anything they didn’t know here. You can always send someone to training to learn a tool, but you can’t train someone to be innovative.
The MicroStrategy questions that I do ask are more conceptual than a definition recital.
Q) What’s the difference between view filter and report filter?
I like this question because it not only tests that they’ve had to consider different methods of filtering in the past, but that they have an awareness of how the SQL Engine and Analytical Engine interact with each other.
Example: A Report Filter is applied directly to the SQL and sent to the database as a WHERE clause. A View Filter is applied in memory to a result set that has already returned and does not require the report to re-execute.
Q) What’s the difference between a custom group and consolidation?
I’m always surprised at how few people really do know the difference. It’s important for performance and just like a carpenter, knowing the best tool for the job. A Custom Group can do everything a Consolidation can, but a Consolidation is much faster than a Custom Group.
Example: A Consolidation is an in memory grouping similar to a case statement. It can only act on a single attribute and all element groupings must be explicitly defined. A Custom Group is a basically a set of filters. It can be used across multiple attributes and metrics, as well as include actual filters and conditions. While slower than a Consolidation, it is more flexible. A new element being added to the database would require the Consolidation to be modified, but not a Custom Group.
Q) What are some things the Analytical Engine does?
My theme in the last few questions is to see if the candidate knows the intricacies MicroStrategy’s tiered architecture. I expect everyone to know this answer, because if you aren’t aware of where the result is coming from, you can’t possibly debug your report when you get a wrong answer.
Example: The Analytical Engine performs additional calculations and manipulations in memory on the data set that was returned by the database. This can include any functions not supported by the database (such as Rank), and most commonly subtotals and pivoting.
Q) What are some weaknesses of MicroStrategy that you think they could improve upon?
No matter how much you love MicroStrategy, anything can be improved upon. Anyone who has used MicroStrategy a lot will no doubt have compiled their own personal list of gripes. The way the candidate answers this question will be a direct indication of the breadth of their exposure to MicroStrategy as well as how much they’ve pushed it to the edges. I’ll leave the interpretation of this question to you.
As I mentioned above, I think MicroStrategy is a pretty easy tool to use. If someone was coming from a Cognos or Business Objects background, I wouldn’t necessarily discount them for a MicroStrategy Developer position. Most of the concepts are very similar, and if they show strong responses to the other questions, I’d be willing to send them to training and expect them to be full contributors in just a few short weeks.
Q) What are the strengths of your tool?
I do expect someone coming from a different background to have at least looked up what MicroStrategy does. They should have at least some simple example of what the tool they are coming from did well, and bonus points for how it stacked up against MicroStrategy.
Q) Cubes vs SQL? Which do you prefer and why? When would you use the other?
Some of the other BI tools on the market seem to align strongly on either MOLAP or ROLAP. I’d expect someone coming from a Cube background to have an opinion of a traditional SQL Engine, though I wouldn’t necessarily expect a SQL person to have a Cube background. Mostly this question is just designed to get them talking.
I always skim the resume and pick out a few claims and ask them to explain. For example, if there’s a line that says, “Optimized a report that was taking 10mins and now it takes 1min”, I’d ask them to explain that in detail. What was the problem? What was the cause? How did you find it? Sometimes candidates embellish accomplishments, are purposefully vague or really do have pretty interesting stories to tell. I like to give them an opportunity to articulate what they have to offer.
Conducting the Interview
One thing I like to do is start the interview off talking myself. I like to introduce myself and my company, the position we have available, and then role I expect the candidate to fill. I also confirm this is the understanding they have and that they are interested. That seems silly, but it accomplishes a few things. First, it breaks the ice since I’ve done the talking and hopefully calmed some nerves. Second, I set the expectations which sometimes aren’t perfectly conveyed by an HR posting or a recruiter’s pitch. And finally, I feel like I’m trying to convince them to join our company as much as they’re trying to convince me to let them. If the candidate knocks my socks off, I can assume they’re knocking other socks off as well. In the event that the candidate gets multiple offers, I’d like to think I made a good first impression too.
If you notice, I’ve tried to design all of my questions around gauging the competency of a candidate, not that they’ve memorized the product manual. I want to make sure that someone coming in has the ability to pick up our business processes quickly, provide valuable feedback and opinions during meetings and bring new ideas to the table. I’m not infallible, and I want to work with others who will challenge me on my own preconceptions of the best way to do something. Someone who doesn’t show a history of innovation or contributing more than their elbow grease isn’t the person I’d want on my team, regardless of their text book knowledge.
My primary goal is to get them talking. Business Intelligence is the most sociable technical position in IT that often involves directly interfacing with high ranking members of the business. I want to be sure that they can speak clearly and effectively, have a strong presence, and the ability to pick up and generate new ideas.