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. 6
Enhancing Query Performance. 8
Understanding the querying architecture. 8
Session management 9
MDX query execution. 10
Data retrieval: dimensions 12
Data retrieval: measure group data. 15
Optimizing the dimension design. 18
Identifying attribute relationships 18
Using hierarchies effectively. 22
Maximizing the value of aggregations 24
How aggregations help. 24
How the Storage Engine uses aggregations 25
Why not create every possible aggregation? 27
How to interpret aggregations 29
Which aggregations are built 30
How to impact aggregation design. 31
Suggesting aggregation candidates 32
Specifying statistics about cube data. 36
Adopting an aggregation design strategy. 39
Using partitions to enhance query performance. 40
How partitions are used in querying. 41
Designing partitions 41
Aggregation considerations for multiple partitions 43
Writing efficient MDX. 44
Specifying the calculation space. 44
Removing empty tuples 47
Summarizing data with MDX. 55
Taking advantage of the Query Execution Engine cache. 58
Applying calculation best practices 60
Tuning Processing Performance. 61
Understanding the processing architecture. 61
Processing job overview. 61
Dimension processing jobs 62
Dimension-processing commands 64
Partition-processing jobs 65
Partition-processing commands 65
Executing processing jobs 66
Refreshing dimensions efficiently. 67
Optimizing the source query. 67
Reducing attribute overhead. 68
Optimizing dimension inserts, updates, and deletes 70
Refreshing partitions efficiently. 71
Optimizing the source query. 71
Using partitions to enhance processing performance. 72
Optimizing data inserts, updates, and deletes 72
Evaluating rigid vs. flexible aggregations 73
Optimizing Special Design Scenarios. 76
Special aggregate functions 76
Optimizing distinct count 76
Optimizing semiadditive measures 78
Parent-child hierarchies 79
Complex dimension relationships 79
Many-to-many relationships 80
Reference relationships 82
Near real-time data refreshes 86
Tuning Server Resources. 91
Understanding how Analysis Services uses memory. 92
Memory management 92
Shrinkable vs. non-shrinkable memory. 94
Memory demands during querying. 95
Memory demands during processing. 96
Optimizing memory usage. 97
Increasing available memory. 97
Monitoring memory management 97
Minimizing metadata overhead. 98
Monitoring the timeout of idle sessions 99
Tuning memory for partition processing. 100
Warming the data cache. 101
Understanding how Analysis Services uses CPU resources 103
Job architecture. 103
Thread pools 103
Processor demands during querying. 104
Processor demands during processing. 104
Optimizing CPU usage. 105
Maximize parallelism during querying. 105
Maximize parallelism during processing. 107
Use sufficient memory. 109
Use a load-balancing cluster 109
Understanding how Analysis Services uses disk resources 110
Disk resource demands during processing. 110
Disk resource demands during querying. 110
Optimizing disk usage. 111
Using sufficient memory. 111
Optimizing file locations 111
Disabling unnecessary logging. 111
Conclusion. 112
Appendix A – For More Information. 113
Appendix B - Partition Storage Modes. 113
Multidimensional OLAP (MOLAP) 113
Hybrid OLAP (HOLAP) 114
Relational OLAP (ROLAP) 115
Appendix C – Aggregation Utility. 116
Benefits of the Aggregation Utility. 116
How the Aggregation Utility organizes partitions 117
How the Aggregation Utility works 118
- Len Wyatt
