Oracle Cloud EPM Planning has been hybrid for a while now. Generally speaking, it has been a big improvement in performance. But until recently, I was not aware of a simple change to scripts that could further improve performance.
In BSO databases, per Oracle documentation, “the SET HYBRIDBSOINCALCSCRIPT calculation command controls whether the subsequent blocks in the current Essbase calculation script execute in hybrid mode”. I would have thought this to be a default setting, but the default is to run in block storage mode.
The parameters are either NONE or FULL. NONE runs the script in block storage mode. To run the script in hybrid mode, set as FULL, and this will execute the script with efficiency similar to that of ASO databases.
What kind of performance improvement can you expect? That will depend on what the script is calculating, but in my experience with it so far, I’ve seen noticeable improvements.
For example, I have a script to calculate interest expenses based on multiple accounts and rates for an 18-month rolling forecast.
Using SET HYBRIDBSOINCALCSCRIPT NONE (or not including the command in the script at all) results in total runtime of …I don’t know, I killed the script after 60 minutes.
Changing to SET HYBRIDBSOINCALCSCRIPT FULL results in total runtime of 2 seconds!
That’s a significant improvement in this one script. So much that without the command the script is useless.
That may seem like an extreme example, so I tested again with another script. The script is calculating bonus accruals for an 18-month rolling forecast.
Using SET HYBRIDBSOINCALCSCRIPT NONE (or not including the command in the script at all) results in total runtime of …I don’t know, I killed the script after 30 minutes.
Changing to SET HYBRIDBSOINCALCSCRIPT FULL results in total runtime of 10 seconds.
I have added this SET command to many of my scripts and improved performance in those scripts. But be aware that adding it to a script may not have any affect, or possibly have a negative impact. I tried adding to a calc for salary and benefits and the result was a decrease in performance.
Using SET HYBRIDBSOINCALCSCRIPT NONE (or not including the command in the script at all) results in total runtime of 28 seconds.
Changing to SET HYBRIDBSOINCALCSCRIPT FULL results in total runtime of 5 minutes.
While this is a very useful command to improve performance in many scripts, it’s not necessarily the best thing to add in all scripts. Always test these types of changes to be sure what you are doing makes sense.
As always, happy EPM’ng!
Random EPM Notes 