The query list is presented in a three-column, tabular format. The columns of the table and their significance are described in Table 2.
|
Column |
Description |
| Request Data Set Description | Details of the data requested by the respective query |
| Times Executed | Number of times the respective query was executed |
| Average Duration | Average duration of the respective query’s execution |
Table 2: The Three Columns Presented in the Review the Results Dialog
We might want to pay close attention to detail at this point in a production system, and ensure that the queries selected truly represent the population upon which we wish to design prospective aggregations.
10. Click Next.
The Aggregations already exist dialog appears, assuming we had aggregations in the Warehouse cube / its first partition, which we cloned to our example cube, SSPerf_Warehouse. The purpose of the dialog is to display the previously designed, and current, settings for data storage and aggregation, including:
-
Data Storage Type
-
Aggregation Storage Space
-
Number of Aggregations
At the bottom of the dialog are two options for the action we wish for the Usage-Based Optimization Wizard to take with regard to its findings for optimal aggregations. We can have the wizard completely replace the current aggregations with the new ones it proposes, based upon the queries it has examined. Alternatively, we can have the Wizard append new aggregations, allowing us to use the resulting combination of aggregations with the partition that is currently under the scalpel.
We will replace the existing aggregations, placing full confidence in the Wizard within the realm of a small example cube. The appropriateness of this action may, of course, differ with a cube in a production environment, based upon several factors, some of which we will discuss in subsequent optimization articles.
11. Ensure that the radio button to the left of the Replace the existing partitions option is selected (the default).
The Aggregations already exist dialog appears, with our selection circled, as shown in Figure 19.
Figure 19: The Aggregations Already Exist Dialog, Default Selection Circled
NOTE: Specific values in the dialog display may differ, depending on the “history” of the Warehouse cube, in whose image the clone SSPerf_Warehouse cube was created. In addition, if no aggregations existed in the Warehouse cube, this dialog will have been skipped for its successor.
12. Click Next.
The Select the type of data storage dialog appears.
13. Leave the setting at the default of MOLAP, as shown in Figure 20.
Figure 20: The Select the Type of Data Storage Dialog, Default Selection Circled
14. Click Next.
The Set aggregation options dialog appears.
15. Select the radio button to the left of the Performance gain reaches option.
16. Set the desired performance gain for the option to 25%.
The Set aggregation options dialog appears, with our setting circled once again, as depicted in Figure 21.
Figure 21: The Set Aggregation Options Dialog, with Our Selection Circled
17. Click Start.
The Set aggregation options dialog proposes that our performance increase can be attained with two aggregations, as shown in Figure 22.
Figure 21: The Set Aggregation Options Dialog, with Our Selection Circled
NOTE: The values depicted in Figure 22 may obviously differ among our various cubes, depending upon various factors we have already touched upon.
18. Click Next.
The Finish the Usage-Based Optimization Wizard dialog appears. We will elect to perform processing now.
19. Ensure the radio button to the left of the Process Now option in the What do you want to do? section is selected, as depicted in Figure 23.
Figure 23: The Finish the Usage-Based Optimization Wizard Dialog, Default of Process Now Selection Circled
20. Click Finish.
Processing begins, and runs, as evidenced by the Process viewer. The Processing cycle ends, and the success of the evolution is indicated by the appearance of the Processing completed successfully message (in green letters) at the bottom of the viewer, as we have noted before. We can see the partition action in the event details of the viewer.
21. Click Close.
We are returned to the Analysis Manager.
22. Discard the SSPerf_Warehouse cube, as desired. (Right-click and Delete.)
23. Select File —> Exit, when ready to close Analysis Manager.
We have thus practiced the basic steps of exploiting another useful performance tuning tool, the Usage-Based Optimization Wizard. The Wizard allows us to optimize the aggregations for our MSAS cubes partitions based upon the queries that have been 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; making the correct selections here can certainly inject the element of artistry into what might otherwise appear to be an objective science.
Summary
In this lesson we introduced another tool in our MSAS cube optimization toolset, the Usage-Based Optimization Wizard, within whose domain lays the important arena of aggregation design. We performed a practice exercise, using the Usage-Based Optimization Wizard to set aggregations for a copy of the FoodMart 2000 Warehouse sample cube. We examined each of the possible settings that are available to us as we stepped through the various dialogs of the Wizard, including those that it shares with the Storage Design Wizard. We commented, throughout the various steps, upon general optimization concepts as appropriate.
]]>