Execute Aggregate Selection (Aggregate Storage)

After you use this statement to select views, you use the resultant table or aggregation script to build an aggregation (materialize a view) using execute aggregate build.

Note:

View selection and aggregation can be performed by Essbase in a single step by using execute aggregate process. However, the use of the two separate statements execute aggregate selection and execute aggregate build enables you more control of the selection criteria.

You can also configure Essbase to generate aggregate views automatically. For more information about aggregate views, see Aggregating an Aggregate Storage Database.

Syntax

Description of the illustration execaggsel.gif

You can select views in the following ways using execute aggregate selection.

Keywords

using views...with outline_ID

Selects views based on pre-selected view IDs. The view IDs are obtained from previous executions of the statement.

using views...with outline_ID...force display

Selects views based on pre-selected view IDs, including the pre-selected views IDs themselves.

using views...with outline_ID...suppress display

Selects views based on pre-selected view IDs, skipping the pre-selected views IDs themselves. This is the default behavior even if the suppress keyword is omitted.

selecting <INTEGER> views

Selects the number of views based on whether the number of views specified in <INTEGER> is greater than or equal to, or less than, the recommended number of default views that are returned by the execute aggregate selection statement. By default, Essbase determines the recommended number of default views.

Assume that <RECNUM> represents the recommended number of default views:

If the value of <INTEGER> is greater than or equal to the value of <RECNUM>, the selected number of views equals <RECNUM>.

For example, if <INTEGER> equals 20 and <RECNUM> equals 15, the number of selected number of views equals 15.
If the value of <INTEGER> is less than the value of <RECNUM>, the number of views that are selected equals <INTEGER>.

If you want the number of views that are selected to equal the value of <INTEGER>, use the stopping when total_size exceeds <STOPPING-VAL> grammar to change the number of recommended default views that are returned by the execute aggregate selection statement. Define the <STOPPING-VAL> factor large enough so that the number of default views that are returned by execute aggregate selection is greater than the value of <INTEGER>.

For example, if <INTEGER> equals 20 and <RECNUM> equals 50, the number of selected number of views equals 20.

Note:

This parameter does not create views.

stopping when total_size exceeds <STOPPING-VAL>

Selects views, specifying a storage stopping value in terms of a factor times the size of the unaggregated input (level 0) values. For example, a stopping value of 1.5 means that the view selection should permit the database to grow by no more than 50% as a result of the aggregation.

based on query_data

Selects views based on previously collected query-tracking data. You must have already enabled query tracking. After enabling query tracking, allow sufficient time to collect user data-retrieval patterns before performing an aggregate selection based on query data.

Query tracking records information about every query executed on the database, so that it can be used as a basis for view selection. Query-based view selection helps to improve query performance when the distribution of user queries is skewed.

For every level combination, the cost of retrieving cells is recorded. The recording continues until the application is shut down or until the recording is explicitly turned off using alter database <dbs-name> disable query_tracking. In both cases, all the query cost data is discarded, and the recording stops (and will not continue when the application starts again).

All query cost data becomes invalid when additional views are built.

To create views based on tracked query patterns,

If needed, enable query tracking using alter database <dbs-name> enable query_tracking. Query tracking is on by default.
Run all production queries once, and then select the first set of views based on the query cost data. To select the views, run this MaxL statement (execute aggregate selection…based on query_data…).
Build the selected aggregate view using execute aggregate build.
Repeat the previous two steps at least twice. Selecting and building multiple views iteratively helps ensure there are enough usage-tracking data to form a pattern. Each new view you build decreases the rate at which query costs grow.

dump to view_file

Saves the view selection to an aggregation script. If the specified script name already exists, an error is returned. To overwrite an existing script, use the force_dump keyword.

The aggregation script contains information derived during the aggregate view selection. You can materialize the aggregation at a different time by running the aggregation script. For example:execute aggregate build on database <dbs-name> using view_file <view-file-name>

force_dump to view_file

Saves the view selection to an aggregation script. If the specified script name already exists, the force_dump keyword causes it to be overwritten.

enable|disable alternate_rollups

If enabled, secondary hierarchies (with default level usage) are considered for view selection. Default: disabled (no secondary hierarchies are considered).

Example

execute aggregate selection on database ASOsamp.Basic;

Performs the default view selection for ASOsamp Basic. This statement selects the same views as execute aggregate process on database ASOsamp.Basic would build.

execute aggregate selection on database ASOsamp.Basic using views 711, 8941 with outline_ID 4142187876;

Selects views based on the pre-selected view IDs. The view IDs are obtained from previous executions of the statement.

execute aggregate selection on database ASOsamp.Basic using views 711, 8941 with outline_ID 4142187876 force display;

Selects views based on the pre-selected view IDs. force display is used to include the pre-selected views (711 and 8941) in the new selection.

execute aggregate selection on database ASOsamp.Basic stopping when total_size exceeds 1.2;

Selects an aggregation of the ASOsamp Basic database that, when built, would permit the database to grow by no more than 20% as a result of the aggregation.

execute aggregate selection on database ASOsamp.Basic based on query_data;

Selects views based on previously collected query-tracking data. You must have enabled query tracking using alter database <dbs-name> enable query_tracking.

execute aggregate selection on database ASOsamp.Basic 
dump to view_file myView;

Selects a default aggregation of the ASOsamp Basic database, saving the selection to APP\DB\myView.csc. You can materialize the view later by running the aggregation script myView.csc. For example:

execute aggregate build on database ASOsamp.Basic using view_file 'myView.csc';