Usage-Based Optimization in Analysis Services 2005 - Page 2
January 9, 2006
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:
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:
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.