Set Aggregation Options (Usage-Based Optimization Wizard)

Analysis Services

Analysis Services

Set Aggregation Options (Usage-Based Optimization Wizard)

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™ 2000 Analysis 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.

    Note  If you selected the ROLAP data storage option in the previous step of this 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.

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 following 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 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 Continue 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 halt 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.