Wednesday Jun 27, 2012

HPCM 11.1.2.x - Outline Optimisation for Calculation Performance

When an HPCM application is first created, it is likely that you will want to carry out some optimisation on the HPCM application’s Essbase outline in order to improve calculation execution times. There are several things that you may wish to consider.

[Read More]

Tuesday Apr 10, 2012

Essbase 11.1.2 - AgtSvrConnections Essbase Configuration Setting

AgtSvrConnections is a documented Essbase configuration setting used in conjunction with the AgentThreads and ServerThreads settings. Basically, when a user logs into Essbase, the AgentThreads connects to the ESSBASE process then the AgtSvrConnections will connect the ESSBASE process to the ESSSVR application process which then the ServerThreads are used for end user activities. In Essbase 11.1.2, the default value of the AgtSvrConnections setting was changed to 5. In previous Essbase releases, the AgtSvrConnections setting default value is 1.

It is recommended that tuning the AgtSvrConnections settings be done incrementally by 1 or 2 maximum and based on the number of concurrent Set Active/Clear Active calls.

[Read More]

Tuesday Mar 27, 2012

Essbase 11.1.2 - JVM_OPTION settings for Essbase

When tuning the heap size for Essbase, there are two JVM_OPTIONS settings available for Essbase - one for the Essbase agent and one for the Essbase applications that are using custom-defined functions (CDFs), custom-defined macros (CDMs), data mining, triggers or external authentication.

  • ESS_JVM_OPTION setting is used for the application and mainly for CDFs, CDMs, data mining, triggers, external authentication
  • ESS_CSS_JVM_OPTION setting is used to set the heap size for the Essbase agent

Wednesday Mar 14, 2012

Essbase 11.1.2 - Optimisation of Essbase Calculation Scripts Using NOT in IF statements

In Essbase calculation scripts, when an IF statement is performed, it will perform the first element of the IF statement first, check if the combination being calculated meets that criteria and calculate it if it does or pass to the next element, i.e the next ELSEIF. This will continue through all the ELSEIF statements and then the ELSE (if present) if the combination does not meet any of the criteria of the IF or ELSEIF.

It is possible to optimise an IF statement by first analysing which condition will be met by the majority of your data and then to try and place this IF statement as near to the top of your IF statement as possible ... if possible, of course.

[Read More]

Wednesday Jan 11, 2012

Essbase 11.1.2 - Commit Block Tuning

The commit blocks setting is a tunable Block Storage (BSO) database setting. For additional information, definition and explanation what the commit blocks setting is, see the Essbase Database Administration Guide, aka Essbase DBA Guide, under the “Ensuring Data Integrity” chapter. This post is to provide additional guidance with how to tune commit blocks for a given database but does not supersede what is documented in the Essbase DBA Guide.

For some Essbase databases, the default commit block setting of 3,000 is not appropriate because the compressed block size is so small that transactions are committed too often, which produces too much overhead. If this is the case, Essbase will adjust the commit block interval automatically and the adjusted value will be recorded in the application log. This adjusted commit block interval value that is recorded in the application log provides a hint on what a better commit block threshold might be for this given database.

The commit block adjustment interval is based on a range from 3,000 to 100,000. Meaning, commit blocks will not adjust higher than 100,000. Adjusted block intervals can happen with any setting if the algorithm determines block size is small and commit blocks occurs too often. It is possible to set commit blocks to 0, as per the Essbase DBA Guide, if you set commit blocks to 0, the synchronization point occurs at the end of the transaction. For this post, we are only focusing on the adjusted commit block intervals.

If the data committed by one of the first transactions before this adjustment was made is less than 10 MB of data then Essbase will try to make it commit at least 10 MB of data.  The goal with each implicit commit is to commit at least 10 MB of data. If it is less than 10 MB of data then Essbase is saying there is no point in committing this early and will try to make the commit interval larger.

In general, if the commit block interval automatically sets larger than 20,000, then it is recommended to reconsider the block density. Poor selection of block density will make too many blocks and potentially the reason for increased fragmentation. Once the best outline sparse/dense configuration is determined, the next statistic to monitor would be the compression ratio. If the compression ratio it is too low, this typically means there is too much compression, which is a good indicator that there are too many #Missing blocks.

The factors that impact whether Essbase adjusts the commit blocks interval are as follows:

  • The number of blocks dirtied by the calculation is different at the first implicit commit.
  • If the number of non-missing cells changes in the compressed block at the first implicit commit, thus affects the size of the data buffers to be flushed out to the page file.
  • Under parallel thread calculations, if the first adjusted commit block interval happens to be too low, than commit blocks will continue to re-adjust. If the adjusted commit blocks value is appropriate, the same interval may be applied to all remaining calculation threads. Basically, if parallel threads are enabled then commit blocks may adjust differently based on which thread reaches that point first. .

Using commit blocks interval to determine the data cache setting:

See the Essbase DBA Guide for additional options for sizing the data cache. However, a reasonable estimate for tuning data cache based on the commit blocks interval would be:

Data Cache = uncompressed block size * commit blocks * number of parallel calculation threads

We need to use the uncompressed block size because data cache holds only uncompressed blocks.

Determining the optimal size of commit blocks interval:

This is hard to generalize and has to be tuned for the particular application and data model. Therefore, it can only be arrived at by meticulous testing.

One suggestion would be to experiment with values from 3,000 – 30,000 (in general) and see what gives better performances when the same batch calculation(s) are ran again and again. Generally a higher value will make the initial calculation run faster and subsequent calculations run slower. Also, the commit block setting may need additional tuning during performance testing with high end user concurrency. For instance, Planning applications with multiple end users running business rules concurrently would require experimenting with different settings to avoid possible block contention.

Friday Dec 16, 2011

HPCM 11.1.2 - Optimising Calculations in HPCM for Multiple POVs

Calculation scripts in Hyperion Profit and Cost Management (HPCM) are generated and executed by user Point of View (POV) e.g. by Period, , Year, Scenario and Version combination. Therefore, if your model is, for example, a forecast year, you will need to generate and execute 12 calculation scripts (i.e. one for each period). In v11.1.1.x code lines, generation of calculation scripts is done from the user interface. In 11.1.2.x code lines, it is also possible to use web services to automate the run of these calculation scripts.

The previous paragraph is the supported method for generating and executing calculations. However, it is also possible to run user defined scripts on the model. In the same way, it is possible to edit the standard scripts i.e. to create custom user defined scripts. By doing this, it is possible to run 12 periods in a single calculation script execution rather than 12 separate calculation script executions. It is possible to do this if all 12 periods use the same assignments and driver selections. If this is the case, you only need to generate one calculation script, edit it and then execute this script, or scripts, if HPCM generates multiple scripts per stage, for all 12 periods. This requires only some minor customisation of the script.

[Read More]

Tuesday Dec 13, 2011


Specifies whether optimized grid processing, which cuts the input grid into symmetric grids to create fewer symmetric queries, is enabled for spreadsheet operations. The default value is FALSE.

[Read More]

Thursday Dec 08, 2011

EPM 11.1.2 - Understanding the Dynamic Calculations Log Messages for Essbase ASO

Essbase application log has some informative messages related to dynamic calculations that can be helpful in tuning:

  • The following message indicates an error in a formula. Fix the error message or drop these members from outline if they are not required.
    • Error encountered when loading member [<MemberName>]'s calc string [<FormulaExpression>], ignored 
  • The following message indicates invalid solve order assignment. Assign correct solve orders for members indicated in the message.
    • Invalid formula for member [<A>] (reason: depends on a member [<B>] with higher solveorder ) will be ignored during execution
  • The following message indicates that a formula is too complex for Essbase to optimize. Rewrite if possible.
    • Formula for member [% of Total] will be executed in [CELL] mode
  • The following message indicates that a formula is complex and Essbase couldn’t detect a non empty behavior for it.

Wednesday Dec 07, 2011

EPM 11.1.2 - Increase Retrieval Buffer Sizes for Essbase (ASO/BSO)

  • Set Retrieval Buffer Size to 50 KB for 32 bit platform and 100 KB for 64 bit platform.
  • Set Retrieval Sort Buffer Size to 50 KB for 32 bit platform and 100 KB on 64 bit platform: Essbase requires a larger retrieval sort buffer size on 64-bit platforms than on 32-bit platforms.

Important Note: If you have a high-memory-use installation (Essbase 64bit), you can configure higher retrieval buffer values (i.e. 300KB). Caution: It is recommended to start with default values and a test incremental setting on largest report tuning is based on. As this setting is per user report request so it may lead to wastage of memory and only want to set as much as needed. Also note the retrieval buffer is allocated per thread, so the amount of memory needed is the number of server threads multiplied by the retrieval buffer size.

Tuesday Oct 25, 2011

Essbase Kernel Is Multi-Threaded

Although it is documented in the Essbase Database Administration Guide (DBA Guide) that the Essbase kernel is multi-threaded and provides the foundation for a variety of Essbase functions, which includes data loading, calculations, spreadsheet lock and send, partitioning, and restructuring. The Essbase kernel also reads, caches, and writes data, manages transactions, enforces transaction semantics to ensure data consistency and data integrity.  There still continues to be some confusion around the multi-thread capabilities of the Essbase kernel. 

For the documented information on the Essbase Kernel, see the Essbase DBA Guide:

·         Chapter 46. “Running Essbase Servers, Applications, and Databases”

·         Chapter 49, “Managing Database Settings”

·         Chapter 50, “Allocating Storage and Compressing Data”

·         Chapter 51, “Ensuring Data Integrity”

·         Chapter 55, “Optimizing Essbase Caches”

·         Appendix D, “Estimating Disk and Memory Requirements”


It has been continually questioned if the Essbase kernel is in fact multi-threaded.  This post is to help clarify this point and will hopefully clear up this misconception with the Essbase kernel and its multi-threaded capabilities.

The Essbase kernel has not been single threaded since the release of Essbase v6.5.  Both the Block Storage (BSO) and Aggregate Storage (ASO) kernels are multi-thread capable. This is evident with the concurrent activities run by end users i.e. queries, HBR type calculations, batch type calculations, and data updates that are typical in a Planning (BSO) type applications or heavy queries demanded in Reporting (ASO) type applications.

The Essbase kernel does not do anything by itself to create multiple threads.  AgentThreads and ServerThreads are set in the Essbase.cfg file based on design, process and end user volume.  Normally, the ServerThread default setting of 20 threads is sufficient per application.  The calculation command “SET CALCPARALLEL”, which is set in each calc script, inherently creates multi-threading inside the Essbase kernel. The Essbase kernel will service these requests concurrently and this is not a serialization point.

A caveat that can be misread that the Essbase kernel is serial is if the customer uses a slow I/O subsystem, especially an I/O subsystem that does not support parallel I/O capabilities.  A slow IO subsystem that does not support parallel IO capabilities can be a bottleneck that will eventually serialize the concurrent operations, meaning the Essbase kernel is not the serialization point.

Another caveat that can be misread is if the volume of concurrent requests on the Essbase server exceeds the server resource capacity.  It is important to do appropriate performance testing to be sure the expected design, process and end user volume does not exceed server capacity.  If the volume of concurrent activities bombards the Essbase server, this can cause CPUs to contend and thrash negatively affecting performance.

Monday Sep 26, 2011

EPM 11.1.2 - AASOSAMPLESIZEPERCENT configuration setting for Essbase

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.

Thursday Aug 25, 2011

EPM 11.1.2 - Add NO_HOSTNAME_LISTCONNECT Configuration Setting for Essbase

When using the display session in MaxL statement or EAS sessions console to view active login sessions, specifies whether to convert the IP address of the client computers that are logged into Essbase to the computer’s hostname. When set to TRUE, IP addresses are not converted to hostnames, which improves the performance of the display session MaxL statement.


Tip: The setting should be NO_HOSTNAME_LISTCONNECT TRUE in the essbase.cfg file.

EPM 11.1.2 - How-to increase Essbase CSS Java heap size

Increase the Java heap size only when during peak periods with a large number of concurrent logins, Essbase agent run out of memory / terminate abnormally / hangs.


Tip: How-to increase Java heap size:

o Stop Essbase Server.

Since OPMN is used to start Essbase, you need to go to MIDDLEWARE_HOME\user_projects/epmsystem1\user_projects\epmsystem1\config\OPMN\opmn, and set or update below two minimum and maximum heaps parameters in opmn.xml file under tag <process-type id="EssbaseAgent" module-id="ESS">  <environment>

   <variable id="ESS_CSS_JVM_OPTION4" value="-Xms256M"/>

   <variable id="ESS_CSS_JVM_OPTION5" value="-Xmx1024M"/>


If ESS_CSS_JVM_OPTION4 and ESS_CSS_JVM_OPTION5 is already in use, set this value for the next available ESS_CSS_JVM_OPTION. For example: ESS_CSS_JVM_OPTION9.


Tip: If you are not using OPMN to start Essbase and use startup script i.e. startEssbase.bat | .sh then you need to set above parameters (ESS_CSS_JVM_OPTION) in setEssbaseEnv.bat | .sh file.


o Start Essbase Server.

Sunday Aug 14, 2011

EPM 11.1.2 - Ulimit limits settings when running Essbase (64bit) Server on a 64-bit AIX platform

User Limit Parameter

It is highly recommended to apply the following values to set for user limits when running Essbase (64bit) Server on a 64-bit AIX platform. Tip: ulimit -a command displays all the ulimit limits.

time (seconds)



file (blocks)



data (kbytes)



stack (kbytes)



memory (kbytes)



coredump (blocks)



nofiles (descriptors)

Important Note: In majority of deployments Essbase does not need too many files to be open. It is pure waste of resources.




A blog focused on Tips & Tricks about Oracle Business Intelligence (OBI), Oracle Exalytics and Oracle Enterprise Performance Management (EPM) products.
[Blog Admin: ahmed awan]


« April 2014