Automatic optimization of in-memory aggregates

When you enable this feature, the system continually analyzes the workload activity and automatically optimizes the set of in-memory aggregates in response to the report queries.

Automatic optimization of in-memory aggregates has the following benefits:

It minimizes the number of manual Aggregate Advisor runs and reduces the need to generate comprehensive workload logs.
It improves report performance by adjusting the set of in-memory aggregates over time to better match query activity.
It complements the near real-time updates of dynamic cubes.
It reduces the cost of dynamic cube ownership.

When automatic optimization of in-memory aggregates is enabled, the workload is logged automatically. Aggregate Advisor runs automatically in the background, quickly analyzes the workload, recommends new and more effective in-memory aggregates, and applies them to the content store. The server then automatically, one in-memory aggregate at a time, loads or removes the in-memory aggregates from the running instance of the cube. If this feature is enabled for multiple cubes on a server, the automatic optimization steps are performed sequentially, one cube at a time. This helps to minimize the impact on the live system, which includes the query service and the database server.

Because the workload activity is logged automatically and not filtered, you do not need to manually enable workload logging and capture a comprehensive workload ahead of time. The system adjusts the set of in-memory aggregates over time, in a conservative manner. For example, the system creates additional in-memory aggregates if it estimates that there is enough memory space. If there is a possibility that the space might be exceeded, the system tries to make an intelligent compromise between the previously- and newly-recommended in-memory aggregates. The system is especially cautious when it recommends to remove aggregates. This approach results in minimal changes to the set of in-memory aggregates, and when the in-memory aggregates are loaded one at a time, minimizes the impact on the system.

User-defined in-memory aggregates, if they are in the model, are always included in the set of in-memory aggregates, regardless of the memory space estimates or matching to in-database aggregates.

Automatic optimization of in-memory aggregates works best in the following situations:

In-database aggregates are either not required in the model, or are stable and do not include slicers.
Additive measures are used in the model.
Non-additive measures cannot rollup from in-database aggregates and can result in a number of in-memory aggregates to provide a direct match for queries.

If you are using multiple dispatchers for the query service, only the server with the dynamic cube property enabled has its in-memory aggregates automatically and continually optimized. The cube on other servers synchronizes and loads in-memory aggregates when it starts.

To enable automatic optimization of in-memory aggregates, turn on the dynamic cube property Enable automatic optimization of in-memory aggregates, and set the dynamic cube property Maximum space for in-memory aggregates (MB) to a value greater than 0. For more information, see Starting and managing dynamic cubes.

When automatic optimization of in-memory aggregates is enabled, you can use the following, optional, query service Advanced settings to configure this functionality:

qsAutomaticAggregateOptimizationMatchInDatabaseAggregates: By default, Aggregate Advisor recommends in-memory aggregates that are based only on the workload, which means that the in-memory aggregates are loaded either from the in-database aggregates or from the fact table. Loading an in-memory aggregate that is based on a large fact table takes a long time.; Load performance is improved if the in-memory aggregates load from in-database aggregates that are smaller than the fact table. To ensure that your in-memory aggregates load from in-database aggregates, change this setting to True. As a result, Aggregate Advisor recommends only in-memory aggregates that match the in-database aggregates. Because the in-memory aggregates do not have slicers, Aggregate Advisor ignores any in-database aggregates with slicers when assessing whether an in-memory aggregate would match.; Value: True or False; Default: False; Tip: When you enable automatic optimization of in-memory aggregates, Aggregate Advisor does not recommend in-database aggregates. Users must create and model the in-database aggregates in the cube. The users can either create the in-database aggregates themselves, or manually run Aggregate Advisor to obtain in-database recommendations.
qsAutomaticAggregateOptimizationStartTime: By default, the system determines when to run Aggregate Advisor and load in-memory aggregates. Use this property if you prefer to start this activity at a specific time.; Value: 00:00 to 23:59. This value is based on a 24-hour clock. For example, if you specify 23:00, the automatic optimization of the dynamic cubes on the server occurs nightly, starting at 11:00 PM.; Default: Empty string
qsAutomaticAggregateOptimizationMaxConcurrentCubeTasks: By default, the system performs automatic optimization of one cube at a time. For example, if there are three cubes on a server that is enabled for automatic optimization of in-memory aggregates, the system automatically runs Aggregate Advisor and loads any recommended aggregates for the first cube. Once complete, this action is repeated for the second cube, and then for the third cube. This type of processing minimizes the load on the query service and database servers.; You can change this setting to specify the number of cubes to be optimized concurrently. This is typically done when the cubes are configured for automatic optimization at a specified time (the qsAutomaticAggregateOptimizationStartTime advanced setting is configured to a non-default value), preferably during a maintenance window when the system is lightly used. However, if optimization occurs throughout the day, which is the default behavior (the qsAutomaticAggregateOptimizationStartTimeset setting is configured to use the default value), you must be cautious about changing this setting.; Value: Positive integer starting with 1; Default: 1; Tip: This advanced setting does not require a query service restart.

For information about configuring the query service Advanced settings, see Setting query service properties for dynamic cubes.