Optimizing Performance Based on Usage

To quickly and easily improve a cube’s or partition’s performance based on patterns of past query usage, use the Usage-Based Optimization wizard. The wizard takes you through steps to specify the following definitions for optimization:

• Partition

  If the cube contains multiple partitions, select the partition to optimize. (You can optimize only one partition at a time.) Partitions are the storage containers for data and aggregations of a cube. Multiple partitions are transparent to the user who sees only the total cube. For more information about partitions, see Partitions.

  ---

  Important If in the future you might merge partitions, do not use the Usage-Based Optimization wizard to optimize them or their parent cubes. The wizard changes a partition’s aggregations. Merged partitions must have identical aggregations.

  ---

• Queries

  Select the queries upon which to base optimization.

  ---

  Note The Usage-Based Optimization wizard is affected in part by options that control query logging. The wizard relies on records you select from the query log, which by default records one in every ten queries. You can change an option to record at any interval up to 1 in 10,000. Other options are turning off query logging and clearing the log, which also affect the wizard. For information about these options, see Properties Dialog Box - Query Log Tab.

  ---

• Storage option

  If no aggregations exist, or if you choose to replace existing aggregations, select a storage option:

  • MOLAP (multidimensional OLAP): stores aggregations and a copy of the partition’s source data in a multidimensional structure.
  • ROLAP (relational OLAP): stores aggregations in a relational structure and leaves the partition’s source data in its existing relational structure.
  • HOLAP (hybrid OLAP): stores aggregations in a multidimensional structure and leaves the partition’s source data in its existing relational structure.

  Each storage option has advantages and disadvantages. For more information, see Storage Modes (MOLAP, ROLAP, HOLAP).

• Aggregations.

  Select a method of controlling the number of aggregations the wizard will create. Then let the wizard create the aggregations.

  Aggregations are precalculated summaries of cube data that help enable Microsoft® SQL Server™ OLAP Services to provide rapid query responses.

  The goal is to create the optimal number of aggregations. This number should not only provide satisfactory response time but also prevent excessive partition size. A greater number of aggregations produces faster response time but also requires more storage space. Moreover, as the wizard creates more and more aggregations, earlier aggregations produce considerably larger performance gains than later aggregations. Therefore, control the number of aggregations the wizard creates by one of the following methods available in the wizard:

  • Specify a storage space limit for the aggregations.
  • Specify a performance gain limit.
  • Stop the wizard manually when the displayed Performance vs. Size curve starts to level off at an acceptable performance gain.

  For more information about aggregations, see Aggregations.

To start the Usage-Based Optimization wizard

1. In the OLAP Manager tree view, under a database, expand the Cubes folder.
2. Right-click a cube, and then click Usage-Based Optimization.

After you add to or replace the partition’s aggregations using the wizard, you must process the partition. The final step of the wizard allows you to process or defer processing. Depending on the size of the partition, processing may take considerable time.