Usage-Based Optimization in Analysis Services 2005 - Page 6
January 9, 2006
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.
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.
The Usage-Based Optimization Wizard appears, beginning with the Welcome page, as shown in Illustration 32.
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.
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.
12. Leave the number selections blank, putting no filters in place, as shown in Illustration 34.
13. Click Next.
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).
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.
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.
17. Click Next.