Set Aggregation Options (Storage Design Wizard)
In this step of the wizard, you set options for storage and performance to design the aggregations for the cube or partition.
In designing your cube, you will have to balance the storage needs of the aggregation tables against the speed and performance of the queries. The three approaches to achieving this balance are as follows:
- Set the storage size and let Microsoft® SQL Server™ 2000 Analysis Services determine which aggregations to store. This approach works well when you have limited storage space.
- Set the percentage of performance gain and let the necessary aggregation tables take as much storage space as they need.
- Manually determine the best balance by watching the progress of the Performance vs. Size graph.
Note If you selected the ROLAP data storage option in the previous step of the Storage Design Wizard, you can use ROLAP data storage without creating aggregations in the relational data store. To do this, select Until I click Stop, and then click Next to advance to the Finish step of the wizard.
Options
Select the option you want for aggregation design.
Estimated storage reaches
Enter the amount of hard disk storage to allocate for storing the aggregation tables. You can enter a maximum storage size in either megabytes (MB) or gigabytes (GB).
Performance gain reaches
Specify the percentage amount of performance gain for your queries. This amount represents the percentage improvement between the maximum and minimum query times, as represented by the following formula:
PercentGain = 100 * (QTimeMAX - QTimeTARGET) / (QTimeMAX - QTimeMIN)
For example, if a query that is not optimized takes twenty-two seconds (QTimeMAX) to execute, and the best possible query performance with maximum aggregations is two seconds (QTimeMIN), specify a 75% desired performance gain to achieve a query time of seven seconds (QTimeTARGET).
Until I click Stop
Select to manually control the balance. Watch the Performance vs. Size graph to determine when the increase in performance levels off, even though storage continues to build.
Start
Click to begin designing aggregations based on the options you have selected.
Continue
Click to resume designing aggregations based on the options you have selected. The Continue button replaces the Start button after you click Stop or the line in the Performance vs. Size graph reaches the specified storage or performance gain.
Stop
Click to manually end the aggregation design process.
Reset
Click to delete any aggregations you have just added and restart aggregation design.
Performance vs. Size
View the progress of the design process, including the estimated performance gain and the estimated storage space requirements.