SET CACHE HIGH;
SET UPDATECALC ON;
SET LOCKBLOCK HIGH;
SET CALCPARALLEL 4;
SET FRMLBOTTOMUP ON;
I have many rules loaded with the SET Commands like above, in my EPBCS environment. Do they really SET ESSBASE ON?
Let's see one by one in detail----
a. SET CACHE - First let's see what techref telling.
Essbase uses the calculator cache to create and track data blocks during calculation. Using the calculator cache significantly improves your calculation performance. The size of the performance improvement depends on the configuration of your database.
You can choose one of three levels - high | low | default. Again, this is dependent on the CALCCACHE setting in the essbase.cfg file
will it help in the performance?
Definitely, if you're required to calculate at least one sparse dimension in full. You will have the additional resources in play to speed up the process.
Do I recommend this?
Yes and No. Yes, if you're running admin/batch jobs in serial mode. And, No if it's a planning business rule triggered by multiple users at the same time. In the second case, trying to tap more resources for multi-process can result in adverse performance.
b. SET UPDATECALC - This setting helps us to on/off the intelligent calculation
ON - Essbase calculates only blocks marked as dirty (see Description). Dirty blocks include updated blocks and their dependent parents (see Notes). The default setting is ON. You can change this default using the UPDATECALC TRUE | FALSE setting in the essbase.cfg file.
OFF - Essbase calculates all data blocks, regardless of whether they have been updated.
ON will help the performance. is that right?
Yes. But it calculates only the blocks that are marked dirty. May lead to data issues.
What do I recommend?
OFF - universally cuz data accuracy takes precedence over performance.
And, if you're intelligent enough to understand when the blocks will be marked dirty/clean, you're eligible to turn the intelligent calc on in a situation, can help performance.
c. SET LOCKBLOCK - Specifies the maximum number of blocks that Essbase can get addressability to concurrently when calculating a sparse member formula.
Error: 1012704 Dynamic Calc processor cannot lock more than [200] ESM blocks during the calculation, please increase CalcLockBlock setting and then retry(a small data cache setting could also cause this problem, please check the data cache size setting).
An error like above, push us to go and change this setting to the next level, only to make things worse.
What do I recommend?
You can try up to 1000 and not more than that. This is an indication of a problem with your code and outline structure. I suggest work on that. And, I strongly suggest setting the data cache and index cache to a reasonable level to avoid the possible second part of the error - "a small data cache setting could also cause this problem, please check the data cache size setting"
d. SET CALCPARELLEL - Enables parallel calculation in place of the default serial calculation.
Essbase analyzes each pass of a calculation to determine whether the parallel calculation is possible. If it is not, Essbase uses serial calculation even if CALCPARALLEL is set.
Many think that this is a mantra to improve performance. It is if it can create a task schedule that can be executed parallel. If you can't determine "whether the parallel calculation is possible or not", this is of no use and may do bad.
In the old world, we did use this in conjunction with SET CALCTASKDIM to determine the possible parallel task schedule. Now, it's implicit to the parallel command. But, still, the key is task schedule and non-empty tasks. You should prepare a case sheet and do trial and error to come up with the optimum setting. This involves your outline order also.
What do I recommend?
Use it in admin/batch jobs after the detailed study, findings, and results. Stay away from the rules that are for concurrent business users. The second case may eat into the system resources that are otherwise required for other activities.
e. SET FRMLBOTTOMUP - Optimizes the calculation of complex formulas on sparse dimensions in large database outlines. This command tells Essbase to perform a bottom-up calculation on formulas that would otherwise require a top-down calculation.
Otherwise, means? You're instructing it to go bottom-up. If not, it's intended behavior is to go top-down.
why and when?
The formula contains complex functions (for example, range functions)
The formula's dependencies are not straightforward
what my set frmlbottomup do then?
Surely, It improves the performance at the cost of data accuracy.
How can I avoid that? Use not. Please!
Still, I wanted to use it in such a case, for which this command is introduced. suggest the way?
Shit! Create parallel environments, on/off. simulate the full production cycle. review the logs and compare the results. Yes, you win, if no harm found. otherwise, a big ouch!
***** One last Q - if no use, why our pros are using it so heavily in the rules?
I am an Indian. Let me explain this from the Indian context. We pray many Gods. Each can give immunity and strength on one life skill or force. We want all; we don't want to disappoint any. So, we are.
Same way, SET commands also helps you to create the right vehicle for your calc if used well. We want it. We use all so. is that explained? 😊
If you ask, I have seen Americans also used them alike. My answer is, given the chance they are also like us to pray many for more benefits. 😉
