Usage-Based Optimization in Analysis Services 2005 - Page 6

Procedure

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.

The various Measure Groups for the Adventure Works cube appear.

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.

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

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.

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.

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

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).

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.

The Specify Object Count page appears next.

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