Before we move on the the wonders of subselects I would like to give you a few pointers on tuning MDX queries and cubes in general. During my time as a performance tuner on Analysis Services – these are the most important factors you should consider in a 2005 environment:
- Use NON_EMPTY_BEHAVIOR whenver possible in your calculated members. This has a huge impact on your overall calculation performance. You should be aware of the semantic implications that NON_EMPTY_BEHAVIOR has on you calculation – so think carefully about applying this correctly. I should probably make a post on this later
- Aggregation design. In most scenarioes in Analysis Services 2000 – this it not hard: Get you dimension counts right, avoid changing and parent/child dimensions, do as many aggregations as you feel comfortable storing and processing and extend as needed with usage based aggregation. In Analysis Services 2005 there are many more settings to consider and tune. I cannot stress enough how important it is to get yourattribute relationsships right. Use rigid relationships whenever possible and make sure you hierarchies are supported. Probably enough matter here for a seperate post
- Watch our for CPU-hogs in your calculations. The MDX SUM function is the classical example – if you sum over too large a set you are essentially creating a "mega loop" in your calculations. I often hear people complain that Analysis Services is very bad at calculations. I think there are improvements to be made on the server side – even in 2005. But, most of the time, programmers just need to think harder about how they express their calculations before they complain about performance.
- Minimize subcube operations. This is closely related to my previous post about multi-selects. I have some experiences from a big installation to share with you