Aggregate View Build Optimization

To improve the performance of building aggregates on an Essbase ASO cubes, you can increase the size of the aggregate storage cache, or increase the sample size.

You might encounter the following message while building aggregate views on an aggregate storage cube:

For better performance, increase the size of aggregate storage cache

This message sometimes occurs when the cube is larger than a few hundred million input cells.

To improve the performance of building aggregates, take the following steps.

Increase the size of the aggregate storage cache to at least 512 MB or 20% of the input data size, whichever is smaller. (If the cache setting is already greater than this amount, proceed to the next step.) Use the MaxL statement alter application with set cache_size grammar to increase the cache. For example:
```
alter application appname set cache_size xMB
```
This setting takes effect after you restart the application.
If you still see the message when building aggregate views after increasing the aggregate storage cache, use the ASOSAMPLESIZEPERCENT configuration setting. Syntax:
```
ASOSAMPLESIZEPERCENT [appname [dbname]] n
```
Gradually increase the n value until the message disappears and optimal aggregation performance is reached. For a cube that contains:
- 20 million input cells, start with 5%
- 100 million cells, start with 1%
- More than 1 billion cells, start with 0.1%
Clear the aggregate views; then reselect and rebuild them. If the message still appears, increase the setting and try again.

Performance of building aggregate views may not improve until the message no longer occurs. When the message no longer occurs and performance no longer improves, stop increasing the setting.

Note:

If you increase the ASOSAMPLESIZEPERCENT setting too high, performance will start to degrade again. The optimal setting for a cube larger than 1 billion cells will probably be less than 3%.