Recently I happened to attend a interview for a BI Position which was a based on a Consulting type of role. The questions spanned from SSIS,SQL,SSRS and SSAS. Of course if there was a an expsoure to the newer features such as PowerPivot,Powerview and use of sharepoint integrated mode that would have opened up an other set of questions. Here is a list of questions which were asked:
1. The first questions was around slowly changing dimensions, what are the the different types of SCD's. How is each type of SCD implemented from a schema standpoint. In SCD type 2 scenario how would one locate the most recent record. This was a pretty easy question to answer, of course more real project implementations help significantly.
2. What kind of methodology was used whil designing/building data marts/Data Warehouses (Inmon or Kimball), depending on the answer the next set of questions were asked.
3. What is a snowflake schema, how is it designed in SSAS, give an example, aslo differentiate between a snowflake schema and a parent child dimension. Here a understanding of snowflake schema and practical experience of implementing such dimensions in SSAS would help. Also implementing a parent child relation ship in SSAS would help as well, here with respect to parent child (it is about performing a self join on the table).
4. What is the purpose of Lookup task in SSIS, give a scenario describing where a Lookup task would work well, also provide a contrasting scenario where Lookup task would not work well.
5. While peforming fact table load thorugh a ETL process how would one perform Dimension lookups for the existing of valid keys, also in case of late arriving dimension data, how would the related fact data be handled.(Here one of the options is how to use UNKNOWN attribute which could be part of a dimensions).
6. How are Aggregations designed in SSAS, what is the default behavior, if one were to selectively build aggregations how would one do it. (here an understanding of SSAS cube architecture, how aggregations are performed is very important).
7. Compare and Contrast MOLAP vs ROLAP, what are your personal obervations regarding performance such storage in SSAS, here building cubes in these two storage modes, understanding how load,processing and retrieval of data from such cubes is important. This focuses on how cube is built kind of behind the scenes in SSAS.
8. How is security set up for a cude, in case security needs to setup for a dimension how is it performed, what is importance of having a role.
9. What different kinds of SSRS implementations are available. What different kind of reporting work were performed by SSRS (relational/SSAS cube reporting). In case of SSAS cube reporting how was the MDX created, here understanding of MDX very important.
10. Was TFS used in yourprojects, for what purpose was TFS used and was it used for SSIS,SSRS and SSAS projects.
I have provided as much info about the questions that was possible. I feel all the questions focus on understanding of how things work, why such approaches are being adopted and what are the best practices. In order to tackle these a good hand on implementations and challenges faced would really enhance the responses and provide details as needed.