Usage-Based Optimization in Analysis Services 2005

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.