Analysis Services 2008 R2 Performance Guide

Writers: Thomas Kejser and Denny Lee

 Editor: Beth Inghram
 

Contributors and Technical Reviewers:

Richard Tkachuk

T.K. Anand

Marius Dumitru

Greg Galloway

Siva Harinath

Edward Melomed

Akshai Mirchandani

Carl Rabeler

Elizabeth Vitt

Sedat Yogurtcuoglu

Anne Zorner

Sanjay Nayyar (IM-Group)

Greg Galloway (Artis Consulting)

Tomislav Piasevoli

Christopher Webb (Crossjoin Consulting)

Marco Russo (SQLBI)

Published: October 2011
Applies to: SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2

Summary: This white paper describes how business intelligence developers can apply query and
processing performance-tuning techniques to their Microsoft SQL Server 2008 R2 Analysis Services OLAP
solutions. 

Introduction

This guide contains information about building and tuning Analysis Services in SQL Server 2005, SQL
Server 2008, and SQL Server 2008 R2 cubes for the best possible performance. It is primarily aimed at
business intelligence (BI) developers who are building a new cube from scratch or optimizing an existing
cube for better performance.

 The goal of this guide is to provide you with the necessary background to understand design tradeoffs
and with techniques and design patterns that will help you achieve the best possible performance of
even large cubes.

Cube performance can be divided into two types of workload: query performance and processing
performance. Because these workloads are very different, this paper is organized into four main
sections.

Design Patterns for Scalable Cubes – No amount of query tuning and optimization can beat the benefits
of a well-designed data model. This section contains guidance to help you get the design right the first
time. In general, good cube design follows Kimball modeling techniques, and if you avoid some typical
design mistakes, you are in very good shape.

Tuning Query Performance - Query performance directly impacts the quality of the end-user
experience. As such, it is the primary benchmark used to evaluate the success of an online analytical
processing (OLAP) implementation. Analysis Services provides a variety of mechanisms to accelerate
query performance, including aggregations, caching, and indexed data retrieval. This section also
provides guidance on writing efficient Multidimensional Expressions (MDX) calculation scripts.

Tuning Processing Performance - Processing is the operation that refreshes data in an Analysis Services
database. The faster the processing performance, the sooner users can access refreshed data. Analysis
Services provides a variety of mechanisms that you can use to influence processing performance,
including parallelized processing designs, relational tuning, and an economical processing strategy (for
example, incremental versus full refresh versus proactive caching).

Special Considerations – Some features of Analysis Services such as distinct count measures and many-
to-many dimensions require more careful attention to the cube design than others. At the end of the
paper you will find a section that describes the special techniques you should apply when using these
features. 

To continue reading, please download the Analysis Services 2008 R2 Performance Guide: Word | PDF