Execute Aggregate Selection

Select views of an aggregate storage database based on various selection criteria, and return the results in the form of a table or aggregation script. Next, use the tabular information 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.

For more information about aggregate views, see the Oracle Essbase Database Administrator's Guide and the Oracle Essbase Administration Services Online Help.

Syntax

Syntax diagram for execute aggregate selection.DBS-NAMEVIEW-IDOUTLINE-IDNumbers in MaxL SyntaxSTOPPING-VALVIEW-FILE-NAME

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

KeywordDescription

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 enabled query tracking using alter database <dbs-name> enable 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.

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.Sample;

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

execute aggregate selection on database ASOsamp.Sample 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.Sample 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.Sample stopping when total_size exceeds 1.2;

Selects an aggregation of the ASOsamp Sample 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.Sample 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.Sample 
dump to view_file myView;

Selects a default aggregation of the ASOsamp Sample 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.Sample using view_file 'myView.csc';

See Also