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 - Page 2

By William Pearson

The Usage-Based Optimization Wizard

Overview and Discussion

The Usage-Based Optimization Wizard provides us a quick means of creating aggregations to improve cube processing performance. While the Storage Design Wizard serves us well when we initially design storage for our cubes, and allows us to specify parameters to achieve a tradeoff between storage requirements and query response time that is appropriate to our business environments, the Storage Design Wizard is designed to assume that "all queries are equal," with regard to the resource requirements they place upon the system, and with regard to the likelihood of their selection by information consumers. Once the cube is designed, deployed and processed, and once it becomes a data source for a potentially diverse range of consumers, it often transpires that some queries are executed more than others, and that various parts of the cube structure are utilized more heavily than others.

The Usage-Based Optimization Wizard enables us to fine-tune the aggregations within our cubes so that recurring queries obtain quicker response times than infrequent or "one-off" queries. The Wizard allows us to do this through its analysis of queries that have been submitted by various applications to Analysis Services. The Usage-Based Optimization Wizard allows us to go as far as to even select the specific queries for which we wish to optimize, if that is desirable. Just as we find to be the case with the Storage Design Wizard, the data aggregations designed by the Usage-Based Optimization Wizard are created when the respective partition, its measure group, or its cube is processed.

We can instruct the Wizard, through a series of dialogs, to create aggregations based upon a flexible combination of several cube usage characteristics, including:

  • a date range of cube use;
  • the users querying the cube;
  • the frequency with which a query was executed;
  • response time for the query;
  • storage mode of the data involved.

We will examine each of these parameters, as we work through a practice session with the Usage-Based Optimization Wizard in this article. We will examine the operation of the Usage-Based Optimization Wizard within a context of aggregation design, as we accomplish the following:

  • Create a copy of a sample Analysis Services database for use in our practice exercise;

  • Enable the Analysis Server Query Log to capture query statistics;

  • Prepare the cube further by processing and manipulating data / creating Query Log entries;

  • Perform a practice exercise, using the Usage-Based Optimization Wizard, to set aggregations for our practice cube;

  • Examine each of the possible settings that are available to us, as we proceed through the guided steps of the Wizard;

  • Comment upon general optimization concepts as we proceed through our practice example.

Considerations and Comments

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 Analysis Services. These samples include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed. The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources, as well), provide guidance on samples installation.

Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in the references I have noted.

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