Usage-Based Optimization Wizard - Set Aggregation Options

In this step of the wizard, you set options for storage and performance to design the aggregations for your cube or partition.

In designing your cube, you will have to balance the storage needs of your aggregation tables against the speed and performance of your queries. There are three approaches to achieving this balance:

• Set the storage size and let Microsoft® SQL Server™ OLAP Services determine which aggregations to store. This approach works well when you have limited storage space.
• Set the percentage of performance gain you want 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.

Aggregation Options

Estimated storage reaches

Enter the amount of hard disk storage you want 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 formula:

PercentGain = 100 * (QTimeMAX - QTimeTARGET) / (QTimeMAX - QTimeMIN)

For example, if an unoptimized query 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 this option 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 Start to begin designing aggregations based on the options you have selected.

Stop

Click Stop to manually halt the aggregation design process.

Reset

Click Reset to delete any aggregations you have just added and restart aggregation design.