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 6

By William Pearson


The Usage-Based Optimization Wizard in Action

We can call the Usage-Based Optimization Wizard for a specific partition within a cube, within the context of the Measure Group for which the partition has been created, as we shall see. We can work with the Usage-Based Optimization Wizard from within the SQL Server Management Studio, or from the Business Intelligence Development Studio. Because we are already in the Management Studio, and have worked here with both the Analysis Server (and cube) and the Database Engine (for the underlying relational data source – which also now contains the Query Log, as well), we will call the Usage-Based Optimization Wizard from within the SQL Server Management Studio. The Management Studio is best used as an administrative environment (and I would consider usage-based optimization as an administrative function, in most cases), whereas the BI Development Studio might be best leveraged as a development environment, but the lines often blur, and, because we can access the Usage-Based Optimization Wizard (and many other capabilities) from both environments, local policies and business needs might dictate that one or the other is more appropriate.

Let's practice performing usage-based optimization on a sample Measure Group within the Adventure Works cube, within our clone Analysis Services database, ANSYS043 Adventure Works DW, by taking the following steps.

1.  From the Object Explorer in SQL Server Management Studio, expand the Analysis Services database ANSYS043 Adventure Works DW, by clicking the "+" sign to its immediate left, as necessary.

2.  Expand the Cubes folder that appears within the ANSYS043 Adventure Works DW tree.

3.  Expand the Adventure Works cube that appears within the Cubes folder.

4.  Expand the Measure Groups folder that appears within the Adventure Works cube.

The various Measure Groups for the Adventure Works cube appear.

5.  Expand the Internet Sales Measure Group that heads up the Measure Groups list for the Adventure Works cube.

6.  Expand the Partitions folder that appears underneath Internet Sales.

The partitions that make up the Internet Sales Measure Group (one for each year of Internet Sales data contained in the cube) appear, as shown in Illustration 30.

Illustration 30: The Partitions for the Internet Sales Measure Group ...

NOTE: For more detailed information regarding the nature and use of partitions, and related topics, see other articles in my Introduction to MSSQL Server Analysis Services series.

7.  Right-click the Partitions folder.

8.  Select Usage Based Optimization ... from the context menu that appears, as depicted in Illustration 31.

Illustration 31: Calling the Usage-Based Optimization Wizard ...

The Usage-Based Optimization Wizard appears, beginning with the Welcome page, as shown in Illustration 32.

Illustration 32: Usage-Based Optimization Wizard – Welcome Page

9.  Click Next.

The Select Partitions to Modify page appears. It is here that we can direct which partitions we wish to modify. (The fact that we can handle multiple partitions in one dialog here might come as a relief to those who, finding that the Usage-Based Optimization Wizard could be called by right-clicking individual partitions, have concluded that we are required to handle the partitions one-by-one. The relative obscurity of the documentation on the Usage-Based Optimization Wizard has led to some confusion here, if forums, blogs, and my own e-mail receipts on the topic are any indication). We are reminded that we must choose at least one partition within this page.

10.  Select the All checkbox (on the column header, to the left of the Partition Name label), as depicted in Illustration 33.

Illustration 33: Usage-Based Optimization Wizard – Select Partitions to Modify ...

11.  Click Next.

The Specify Query Criteria page appears. This dialog affords us the opportunity to filter among the queries taken into consideration by the Wizard in proposing usage-based aggregation designs. We have, within this dialog, three general options (four actual checkbox choices) for query selection criteria; we can use one or more of these to narrow the selection of logged queries upon which we wish to base our design for optimization. The three general criteria, together with descriptions, are summarized in Table 1.

Query Criteria Option

Optimization Selection is Based Upon:

Queries within date ranges

Date range for queries. Date range type can be selected from:

Between – All queries between a desired Beginning date and Ending date, inclusive

Beginning Date – All queries on or after a selected Beginning date

Ending Date - All queries executed before a selected Ending date

Queries by specific users

Users and Groups of Users, as defined by User Roles

Query frequency

The most commonly executed queries based upon percentage represented of all queries.

Table 1: The Three General Query Selection Criteria – Usage-Based Optimization Wizard

12.  Leave the number selections blank, putting no filters in place, as shown in Illustration 34.

Illustration 34: Specify Criteria Page – No Filters Specified ...

13.  Click Next.

The Review the Queries that will be Optimized page appears next, as partially depicted in Illustration 35.

Illustration 35: Review the Queries that will be Optimized Page (Partial View)

Here we see every query in the log (since we have specified no filters), grouped by number of occurrences, for which average durations are displayed. We have the opportunity, at this point, to eliminate individual queries from consideration, for added flexibility. (We will leave them all selected for purposes of our practice session).

14.  Leaving all selections in place, click Next.

The Specify Storage and Caching Options page of the Wizard appears next. Here we can adjust the existing settings for storage mode and caching options. We will leave the settings at default (solely MOLAP), as shown in Illustration 36.

Illustration 36: Specify Storage and Caching Options Page – Default Settings

15.  Click Next.

The Specify Object Count page appears next.

16.  Click the Count button, to direct the Wizard to calculate object counts.

Counting commences, and we quickly see the values appear, as partially depicted in Illustration 37.

Illustration 37: The Specify Object Count Page (Partial View)

17.  Click Next.

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