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