Tuesday Dec 11, 2012

EPM Architecture: Financial Performance Management Applications

This is the third post on Oracle EPM System Architecture. It will cover all Financial Performance Management Applications, namely Financial Management, Essbase Analytics Link, Planning, Profitability and Cost Management, Strategic Finance and Disclosure Management. Please be aware of the fact that I also updated the previous posts on Foundation and Reporting and Analysis based on the feedback I got so far.

[Read More]

HPCM - HPCM Standard Costing Generating >99 Calc Scipts

HPCM Standard Profitability calculation scripts are named based on a documented naming convention. From, the script name = a script suffix (1 letter) + POV identifier (3 digits) + Stage Order Number (1 digit) + “_” + index (2 digits) (please see documentation for more information (http://docs.oracle.com/cd/E17236_01/epm.1112/hpm_admin/apes01.html).

This naming convention results in the name being 8 characters in length i.e. the maximum number of characters permitted calculation script names in non-unicode Essbase BSO databases. The index in the name will indicate the number of scripts per stage. In the vast majority of cases, the number of scripts generated per stage will be significantly less than 100 and therefore, there will be no issue. However, in some cases, the number of scripts generated can exceed 99.

[Read More]

Friday Nov 30, 2012

Essbase Analytics Link (EAL) - Performance of some operation of EAL could be improved by tuning of EAL Data Synchronization Server (DSS) parameters

Generally, performance of some operation of EAL (Essbase Analytics Link) could be improved by tuning of EAL Data Synchronization Server (DSS) parameters.

a. Expected that DSS machine will be 64-bit machine with 4-8 cores and 5-8 GB of RAM dedicated to DSS.

b. To change DSS configuration - open EAL Configuration Tool on DSS machine.


[Read More]

Thursday Nov 29, 2012

Using Transaction Logging to Recover Post-Archived Essbase data

Data recovery is typically performed by restoring data from an archive.  Data added or removed since the last archive took place can also be recovered by enabling transaction logging in Essbase.  Transaction logging works by writing transactions to a log store.

[Read More]

Thursday Oct 11, 2012

Essbase BSO Data Fragmentation

Essbase BSO Data Fragmentation

Data fragmentation naturally occurs in Essbase Block Storage (BSO) databases where there are a lot of end user data updates, incremental data loads, many lock and send, and/or many calculations executed.  If an Essbase database starts to experience performance slow-downs, this is an indication that there may be too much fragmentation.  See Chapter 54 Improving Essbase Performance in the Essbase DBA Guide for more details on measuring and eliminating fragmentation: http://docs.oracle.com/cd/E17236_01/epm.1112/esb_dbag/daprcset.html

Fragmentation is likely to occur in the following situations:

  • Read/write databases that users are constantly updating data
  • Databases that execute calculations around the clock
  • Databases that frequently update and recalculate dense members
  • Data loads that are poorly designed
  • Databases that contain a significant number of Dynamic Calc and Store members
  • Databases that use an isolation level of uncommitted access with commit block set to zero.
[Read More]

Wednesday Jul 18, 2012

Essbase “Virtual Cube” BSO Tuning

Essbase “Virtual Cube” BSO Tuning

An Essbase “virtual cube” is a specific design setup where transparent partitioning is used and consists of multiple source databases that are partitioned to a single target Block Storage (BSO) database.  The single target BSO database is empty meaning contains no data.  The purpose of this design setup is so that end users have one consolidated BSO cube for reporting.  In some cases, the target BSO database may have different dynamic calc member formulas then the source databases.  Also, the target BSO database may have more dimensions to accommodate the source databases and may have different sparse/dense configuration since source databases are tuned with data accordingly.

The Essbase Database Administration Guide mentions how to tune Essbase BSO databases that contains data; however, there is no documentation on how to tune a BSO database that contains no data like in a “virtual cube” setup. 

[Read More]

Tuesday Jun 26, 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

Tuesday Mar 13, 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]

Wednesday Dec 07, 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.

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.