Interview Questions for SQL Server Analysis Services (SSAS)

I have received lot of mails from my readers for SSAS interview questions. This gave me idea to list down all the analysis services question here on my blog so everyone can be benefited. This question set is applicable for SQL server analysis service 2005 ( SSAS 2005) and SQL server analysis services 2008 (SSAS 2008). This post divides interview questions into different categories for your convenience:

Design & Development 

Question1: What do you understand by attribute relationship? what are the main advantages in using attribute relationship? Are there any changes in implementation of attribute relationship from SSAS 2005 to SSAS 2008?

Question2: What is natural hierarchy and how will you create it?

Question3: What do you understand by rigid and flexible relationship? Which one is better from performance perspective?

Question4: In which scenario, you would like to go for materializing dimension?

Question5: In dimension usage tab, how many types of joins are possible to form relationship between measure group and dimension?

Question6: What are role playing dimensions?

Question7: Can you create server time dimension in analysis services(Server time dimension)?

Question8: How many types of dimension are possible in SSAS?

Question9: What is time intelligence? How will you implement in SSAS?

Question10: What do you understand by linked cube or linked object feature in SSAS?

Question11: How will you write back to dimension using excel or any other client tool?

Question12: What do you understand by dynamic named set (SSAS 2008)? How is i different from static named set?

Question13: In Process Update, which relationship will be better(Rigid and Flexible relationship)? 


Question14: What is the difference between "ProcessingGroup" ByAttribute and ByTable?

Question15: What do you understand by following properties of dimension attribute:

1. Default Member
2. AttributeHierarchyEnabled
3. AttributeHierarchyOptimizedState
4. DiscretizationMethod
5. OrderBy
6. OrderByAttribute

Advanced Features

Question1:  What are different storage mode option in SQL server analysis services and which scenario, they will be useful?

Question2:  How will you implement data security for given scenario in analysis service data?

"I have 4 cubes and 20 dimension. I need to give access to CEO, Operation managers and Sales managers and employee. 

1) CEO can see all the data of all 4 cubes.

2) Operation Managers can see only data related to their cube. There are four operation managers.

3) Employees can see only certain dimension and measure groups data. (200 Employees) "

Question3: What are the options to deploy SSAS cube in production?

Question4: What are the options available to incrementally load relational data into SSAS cube?

Question5: Have you implemented ROLAP storage mode in your earlier projects? What are the prerequisite for it?

Question6: Why will you use aggregation at remote server?    

Question7:  What are different ways to create aggregations in SSAS?

Question8: What do you understand by Usage based optimization?

Question9:  Can we use different aggregation scheme for different partitions?


Question10: Why will you use perspective in SSAS?

Question11: What are KPIs? How will you create KPIs in SSAS?

Question12: What are the main feature differences in SSAS 2005 and SSAS 2008 from developer point of view?

Question13:What are the aggregate functions available for measure in SSAS?

Question14: How will you keep measure in cube without showing it to user?

Question15: What are the processing modes available for measure group? What do you understand by lazy aggregation?

Performance

Question1: How can you improve dimension design in SSAS 2005?

Question2: What are the performance issues with parent child hierarchy? 

Questions3: What do you understand by formula engine and storage engine?

Question4: How can you improve overall cube performance?