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
You can select views in the following ways using execute aggregate selection.
| Keyword | Description |
|---|---|
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 views up to a maximum number of views. |
stopping when total_size exceeds | 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 selecting 9 views;
Selects a maximum of nine views of ASOSamp Sample.
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';