Any of us can work with some simple MDX queries and get them to do what we want, but in order to understand what we’re working with we need to understand how Analysis Services processes queries. This means we need to identify the two major parts of the SSAS engine.
- Formula Engine – The formula Engine processes MDX Queries, figures out what data is needed to cover them, and forwards this information to the storage engine for retrieval. It then performs all calculations needed for the query.
- Storage Engine - The Storage Engine handles all the reading and writing of data and retrieving and aggregating the data the formula engine requested at query run time.
When an MDX query is executed, the query is passed to the Formula Engine where it is parsed then passed to the storage engine. When the raw data comes back from the storage engine, he formula engine performs any calculations required on that data (Aggregations are done at the storage level).
At all stages of this process there are opportunities for performance tuning. These include:
- Designing for Performance
- Partitioning is important to physically and logically split data to improve performance by reducing overall IO performed by the storage engine.
- Aggregations are critical to make sure data is rolled up in the same way it will be queried. This enables the storage engine to do less work and respond to requests from the formula engine more quickly.
- Usage Based Optimization allows you to optimize based on the activity of the users. IT records samples of the queries and then runs optimizations based on the activity from those queries to ensure continued targets performance.
- MDX Calculation Performance
- Diagnosing Performance Problems is a tricky process, but some specific steps can be followed to help ensure success. Using profiler to test timing and partition hits of Query SubCube events is the best place to start.
- Calculation Performance Tuning is a specific process that can take some time, but a couple of good best practices include the use of Named sets to avoid recalculation of Set based expressions. Also, using calculated members to cache numeric values and avoid retrieving data more than once from disk.
- Controlling Cache Scope is important to ensure that your plan is not conflicting with the executions of others’ and that calculations are being re-used appropriately.
- Cache Warming can be accomplished by several techniques but is important after a restart to ensure quick query response for the first round of queries from the users.
- Managing Blog Computation can drastically improve performance. Block Computation is the implementation of MDX that allows it to calculate a cell value once and use it numerous times for the same query as opposed to having to go cell by cell. This only works in certain situations, but should be taken advantage of whenever possible. More details on this are available online and in a future blog post.
If you take these steps and troubleshoot your MDX code as it’s being designed, you’ll see performance you can be proud of. As always – Please post your questions to the BIDN.com forums.
