Usage-Based Optimization in Analysis Services 2005

About the Series …

This
article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of
the fundamentals of MS SQL Server Analysis Services, with each installment
progressively presenting features and techniques designed to meet specific real
– world needs. For more information on the series, please see my initial
article, Creating Our First
Cube.

Note: To follow along with the steps we undertake, the following components,
samples and tools are recommended, and should be installed according to the
respective documentation that accompanies MSSQL Server 2005:

• Microsoft SQL
  Server 2005 Database Engine

• Microsoft SQL
  Server 2005 Analysis Services

• Business
  Intelligence Development Studio

• Microsoft SQL
  Server 2005 sample databases

• The Analysis Services
  Tutorial sample project and other samples that are available with the
  installation of the above.

To
successfully replicate the steps of the article, you also need to have:

• Membership
  within one of the following:

  • the Administrators
    local group on the Analysis Services computer

  • the Server
    role in the instance of Analysis Services.

• Read permissions within any SQL
  Server 2005 sample databases we access within our practice session, if
  appropriate.

Introduction

In this lesson, we revisit usage-based optimization, a
subject that we undertook in my article MSAS
Administration and Optimization: Simple Cube Usage Analysis, in September of 2003, and MSAS
Administration and Optimization: Toward More Sophisticated Analysis in October of 2003. In the
earlier articles, we discovered that, among several tools that Microsoft SQL Server 2000 Analysis
Services offered us to assist in the maintenance and optimization of our
cubes, two of these tools, the Usage Analysis Wizard and the Usage-Based
Optimization Wizard, leveraged the usage-based optimization features
of Analysis Services. They made it easy to perform basic performance
enhancement of our cubes based upon their usage by information consumers. As I
mentioned then, my experience is that, regardless of the design effort invested
in any given business intelligence application, particularly within the context
of anticipating the patterns of use of that application by the intended
consumers, nothing can quite equal history as a guide to future human
activity.

In Analysis
Services 2000, the Usage Analysis Wizard allowed us to rapidly
produce simple, on-screen reports that provided information surrounding a cube’s
query patterns – information that could be useful in helping us to decide
whether to consider making structural changes to optimize cube design. The Usage-Based
Optimization Wizard, the descendant of which is the subject of this
article, embellished the effectiveness of the Storage Design Wizard, and
went significantly farther than the generation of simple reports. It offered us
the capability to base aggregation design upon a given cube’s usage
statistics, in combination with other factors, and to make subsequent
adjustments to our existing aggregation design and storage mode
as time passed, and information collected from which meaningful statistics
could be derived.

In this
lesson, we will consider the Analysis Services 2005 Usage-Based Optimization Wizard, which combines
some of the features we have seen in the related Analysis Services 2000 wizards
we have previously considered. We will discuss preparation for its use, as
well as the steps involved in making the Usage-Based Optimization Wizard an
effective tool in our Analysis Services administration toolset.