Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 9, 2006

Usage-Based Optimization in Analysis Services 2005

By William Pearson

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.

Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM