Analysis Services 2005 Performance Guide
Do you want to know how to get the best performance out of Analysis Services? Whether you have an immediate performance issue or just want to know more about what’s going on in there, this guide will be a valuable resource. Many people used the AS 2000 Performance Guide and have been anxiously awaiting the AS 2005 Performance Guide. At last, it’s ready!
This paper (see Table of Contents below) covers a wide variety of topics that are important for AS performance: Writing efficient queries, optimizing the cube design, maximizing processing performance partitioning, aggregation design, and tuning the system for efficient use of resources. It was developed in collaboration with the developers and program managers on the AS team, so it’s the authoritative guide. Take a look! It will be worth your time.
Introduction
Enhancing Query Performance
Understanding the querying architecture
Session management
MDX query execution
Data retrieval: dimensions
Data retrieval: measure group data
Optimizing the dimension design
Identifying attribute relationships
Using hierarchies effectively
Maximizing the value of aggregations
How aggregations help
How the Storage Engine uses aggregations
Why not create every possible aggregation?
How to interpret aggregations
Which aggregations are built
How to impact aggregation design
Suggesting aggregation candidates
Specifying statistics about cube data
Adopting an aggregation design strategy
Using partitions to enhance query performance
How partitions are used in querying
Designing partitions
Aggregation considerations for multiple partitions
Writing efficient MDX
Specifying the calculation space
Removing empty tuples
Summarizing data with MDX
Taking advantage of the Query Execution Engine cache
Applying calculation best practices
Tuning Processing Performance
Understanding the processing architecture
Processing job overview
Dimension processing jobs
Dimension-processing commands
Partition-processing jobs
Partition-processing commands
Executing processing jobs
Refreshing dimensions efficiently
Optimizing the source query
Reducing attribute overhead
Optimizing dimension inserts, updates, and deletes
Refreshing partitions efficiently
Optimizing the source query
Using partitions to enhance processing performance
Optimizing data inserts, updates, and deletes
Evaluating rigid vs flexible aggregations
Optimizing Special Design Scenarios
Special aggregate functions
Optimizing distinct count
Optimizing semiadditive measures
Parent-child hierarchies
Complex dimension relationships
Many-to-many relationships
Reference relationships
Near real-time data refreshes
Tuning Server Resources
Understanding how Analysis Services uses memory
Memory management
Shrinkable vs non-shrinkable memory
Memory demands during querying
Memory demands during processing
Optimizing memory usage
Increasing available memory
Monitoring memory management
Minimizing metadata overhead
Monitoring the timeout of idle sessions
Tuning memory for partition processing
Warming the data cache
Understanding how Analysis Services uses CPU resources
Job architecture
Thread pools
Processor demands during querying
Processor demands during processing
Optimizing CPU usage
Maximize parallelism during querying
Maximize parallelism during processing
Use sufficient memory
Use a load-balancing cluster
Understanding how Analysis Services uses disk resources
Disk resource demands during processing
Disk resource demands during querying
Optimizing disk usage
Using sufficient memory
Optimizing file locations
Disabling unnecessary logging
Conclusion
Appendix A – For More Information
Appendix B - Partition Storage Modes
Multidimensional OLAP (MOLAP)
Hybrid OLAP (HOLAP)
Relational OLAP (ROLAP)
Appendix C – Aggregation Utility
Benefits of the Aggregation Utility
How the Aggregation Utility organizes partitions
How the Aggregation Utility works
- Len Wyatt