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
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
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