Summary for "Design Effective Aggregations in Analysis Services 2008" by Chris Webb

The following is a summary for the very good session held by Chris Webb for SQLBits and titled Designing Effective Aggregations in Analysis Services 2008:

• Aggregations are the single most important feature in Analysis Services regarding query performance, yet it’s not always the answer to our performance problems.
• Aggregations are only useful when the Storage Engine has to fetch data from disk.
• Aggregations will not be used if the data is in the Storage Engine cache.
• Aggregations may not be useful if the cause of query performance problems lies in the Formula Engine.
• How building aggregation speed Storage Engine response to the Formula Engine thus improves performance?  When AS has to read a lot of data from disk and has to aggregate that data up from the level which stored on disk, up to the granularity that been requested by the Formula Engine. That’s where a lot aggregation at run time takes place, so if we can get the granularity requested directly from the aggregation, instead of reading it from the kind of fact table granularity and aggregate it, then yes, that part of the process is going to be fast.
• Before building aggregations, design your cube carefully, especially attributes relationships in dimensions. Also try to build natural user hierarchy.  
• How do we know if the Storage Engine is the problem? How do we know if we are going to benefit from building aggregations? How do we know if we even are using the aggregation we’ve built?
  You can use SQL Profiler to see what is happening internally when a query is running. The following events are useful:
• Query Begin/End: the start and end of the query.
• Progress Report Begin/ End: for all reads from partitions or aggregations.
• Get Data From Aggregation: appears every time an aggregation is used, and it is the most important to look for in this exercise, because if you see this event, then you query is using aggregation. 
• Query Subcube Verbose: show details of the requests made to the Storage Engine by the Formula Engine. For example in the following screenshot of the lower area of the trace, you can see one dimension name, and the list of all the attributes in that dimension, and this describes the granularity of request. If the attribute has zero next to it, then we are not requesting data at that granularity else we are. 
  
  
  So in this example we are requesting data at the month granularity, as it has asterisk next to it. And how this is useful? Look at the granularity of the subcube request and compare them with the granularities in the aggregation tab.
• Aggregation Design Wizard may not build the aggregation that you really want, because in this tool it goes and analysis your cube structure and guess what aggregation can help, it doesn’t know what queries you are running. So after running the Aggregation Design Wizard setup Usage Base Optimization.
• The last step you can do is to build manually.