EPM 11.1.2 - AASOSAMPLESIZEPERCENT configuration setting for Essbase
By Ahmed Awan-Oracle on Sep 26, 2011
ASOSAMPLESIZEPERCENT is a documented Essbase configuration setting used in Essbase aggregate storage (ASO) applications.This setting is new starting in Essbase 11.1.2 and later code lines. The ASOSAMPLESIZEPERCENT configuration setting limits the sample size used to create the views from aggregations in aggregate storage (ASO) applications.
Important Note: When the database is very large, this setting can help improve aggregation performance where the input-level cells are very large.
Tested 10 billion input-level cell in an ASO application with ASOSAMPLESIZEPERCENT .5, which is interpreted as .5%. Test results of this testing are as follows:
2 hour savings in total aggregation time
25% reduction in total database size
Normally, testing between 1-3% is typical with this level of input-level cells but testing showed going to half a percent had better results. Caveat to testing too low a percentage is that Essbase will determine the setting is too low and will ignore the setting. The indicator to know the setting you are testing is too low is when you see the following after the aggregation, which is based on the same test case but using
ASOSAMPLESIZEPERCENT .25 --> interpreted as .25%. After the aggregation completed, had the following message in the output log:
Aggregate storage key sample size is set to [2.5114e+07] cells or [0.25] percent of input data. For better performance, increase the size of aggregate storage cache.
In this test case, tested ASO cache from 1 GB up to 30 GB and this did not resolve hitting the "for better performance...." message after the aggregation. The "for better performance...." message indicates the view size estimates are not very accurate and that the aggregation build is inefficient and slowness is a result.
The higher you make the sample size, the more accurate the view size estimates will be, however it makes the view selection process slower, not view build part of the aggregation process. Recommendation is to make ASOSAMPLESIZEPERCENT as low as possible without seeing the "for better performance...." message.