The Set Aggregation Options page appears next. This page is familiar to those of us who have used the Aggregation Design Wizard – and varies only little from the equivalent page of the previous version of Analysis Services.
18. Under Design aggregation until, click the checkbox labeled Performance gain reaches, to select this aggregation option.
19. Input 75 % into the selector box to the immediate right of the Performance gain reaches label.
The Set Aggregation Options page appears as shown in Illustration 38.
Aggregation design begins, and continues until the desired optimization objective is reached. In the present case, we receive a message in the status bar at the bottom of the Set Aggregation Options page, indicating a count of the aggregations designed, along with the optimization level percentage reached and the estimated storage size of the cube, similar to that depicted in Illustration 39.
Keep in mind that aggregations are only designed by the Wizard; the affected partitions must be processed to physically create the aggregations that the Wizard has designed.
The Completing the Wizard page appears, providing an opportunity to review the partitions that are about to be optimized, once processing begins. We can elect to begin processing as the final step of the Usage-Based Optimization Wizard, or to defer processing until a later time. We will elect the former option at this point.
22. Click the checkbox labeled Process partitions immediately, in the bottom left corner of the Completing the Wizard page.
The Completing the Wizard page appears, as shown in Illustration 40.
Illustration 40: The Completing the Wizard Page
The Process Objects dialog opens. Here we can perform various batch settings and review, for a final time (and even remove some, or all of) the objects scheduled to be processed. The Process Objects dialog appears as depicted in Illustration 41.
Illustration 41: The Process Objects Dialog
24. Click OK to begin processing the partitions.
The Process Progress viewer appears, logging the events of the processing of the partitions as they occur. Once all scheduled partitions are processed, we receive a Process Succeeded message in the Status bar at the bottom of the viewer, as shown in Illustration 42.
Illustration 42: Successful Processing Completion is Indicated ...
25. Click the Close button to dismiss the Process Progress viewer.
We are returned to the SQL Server Management Studio. Usage-based optimization is completed.
26. Discard the clone Analysis Services database, ANSYS043 Adventure Works DW, as desired. (Right-click and Delete.)
27. Reset the Log \ QueryLog \ CreateQueryLogTable entry, within the Analysis Server Properties dialog for your local server, to "false," if desired, to disable query logging.
28. Select File ---> Exit, when ready to close SQL Server Management Studio.
We have completed our practice steps with an Analysis Services 2005 performance tuning tool, the Usage-Based Optimization Wizard. The Wizard is similar to the tool of this name within Analysis Services 2000, with performance and utility enhancements in evidence. The Usage-Based Optimization Wizard allows us to optimize the aggregations for our MSAS cubes partitions based upon queries that have previously been executed against the cube. Our ability to discriminate between common, frequently run queries and more ad hoc, less representative queries, when deciding the population to use as a basis for tuning the cube's performance with future queries, allows us to customize the action of the tool a bit beyond what we might expect of more typical wizardry. One of numerous characteristics of the process that remains unchanged since Analysis Services 2000 is the role of the administrator within this flexible, albeit guided tuning event: making the correct selections here can certainly inject the element of artistry into what might otherwise appear to be an objective evolution.
Conclusion
In this lesson, we revisited the Usage-Based Optimization Wizard, which remains a formidable tool in our Analysis Services 2005 administration toolset. We noted that the Usage-Based Optimization Wizard embellishes the effectiveness of the Usage Analysis (going significantly farther than the generation of simple reports) and Storage Design (allowing for up-to-date, usage-based optimization) Wizards. The Usage-Based Optimization Wizard offers us the capability to base aggregation design upon a given cube's usage statistics, in combination with other factors, and to make subsequent adjustments to our existing aggregation design and storage mode as time passes, and as information is collected, from which meaningful statistics can be derived.
In this article, we examined the operation of the Usage-Based Optimization Wizard within a context of aggregation design. Our practice exercise included preparation steps, within which we created a copy of a sample Analysis Services database for use in our practice exercise, enabled the Analysis Server Query Log to capture query statistics, processed the clone database, and then manipulated data within a cube therein to create Query log entries. We then performed a procedure whereby we set aggregations for our designated practice cube with the Usage-Based Optimization Wizard. Throughout the guided steps of the Wizard, we examined each of the possible settings that it makes available to us, and commented upon general optimization concepts as we proceeded through the practice example.
» See All Articles by Columnist William E. Pearson, III
IE 7
Firefox 2.0