I am totally confuse, can somebody help me out with a “Dynamic Calc” member question? A few months ago I was modifying one of our calc scripts which was using a dynamic calc parent member of the account dimension. When I was validating the results, I was getting incorrect values. However when I stopped referring to the dynamic calc parent member in the calc script and instead called up and summed the individual children I got the expected results. Today, I was looking and testing another calc scipt that also uses a dynamic calc member with a member formula and I am getting the correct answers. Why the inconsistency, I thought dynamic calc only “calculates the data value upon retrieval.” But then going over the Essbase Administrator’s Guide I also see “When you reference a dynamically calculated member in a formula in the database outline or in a calculation script, Essbase interrupts the regular calculation to do the dynamic calculation. This interruption can significantly reduce calculation performance.”
So the question is: Can Dynamic Calc Members be used in a Calc Script? If yes under what conditions?
Excerpt from the Essbase Administrator’s Guide….
For a member tagged as Dynamic Calc, Essbase does not calculate its data value during a batch database calculation (for example, during a CALC ALL). Instead, Essbase calculates the data value upon retrieval (for example, when you retrieve the data into Spreadsheet Add-in or Smart View.)
Specifically, Essbase calculates a data value dynamically when you request the data value in either of two ways:
Essbase does not store the calculated value; it recalculates the value for each subsequent retrieval.
Formulas and Dynamically Calculated Members
You can include a dynamically calculated member in a formula when you apply the formula to the database outline. For example, if Qtr1 is a Dynamic Calc member, you can place the following formula on Qtr1 in the database outline:
Qtr1 = Jan + Feb;
You cannot make a dynamically calculated member the target of a formula calculation in a calculation script; Essbase does not reserve memory for a dynamically calculated value and, therefore, cannot assign a value to it. For example, if Qtr1 is a Dynamic Calc or Dynamic Calc and Store member, Essbase displays a syntax error if you include the following formula in a calculation script:
Qtr1 = Jan + Feb;
If Qtr1 is a Dynamic Calc or Dynamic Calc and Store member and Year is neither Dynamic Calc nor Dynamic Calc and Store, you can use the following formula in a calculation script:
Year = Qtr1 + Qtr2;
This formula is valid because Essbase does not assign a value to the dynamically calculated member.
When you reference a dynamically calculated member in a formula in the database outline or in a calculation script, Essbase interrupts the regular calculation to do the dynamic calculation. This interruption can significantly reduce calculation performance.
You can most definitely use dynamically calculated members in a calc script/business rule.
What you cannot do (but will not error) is write to that dynamically calculated member. What I mean by that is if MbrABC is a dynamically calculated member, you cannot send (via Smart View, data loads, or a calc script assignment) data to that member. What's weird is that Essbase will, at least from the client UI, allow you to do the send, but then not tell you that the data sent to MbrABC won't stick. <--This isn't totally true in the case of a data load as you will get a warning message that not all of the data got loaded. But EAS/MaxL will not tell you what members are dynamic and hence not a successful load.
Lastly, the comment about dynamic calcs being slower in calc scripts is that Essbase needs to evaluate the member formula every time it is referenced as an intermediate part of a formula. If it were stored then it would need to be read from within the block but not actually calculated. You get to play the ever-popular Essbase game of "When do I want to feel pain?" So long as the dynamic calc is in the block, the in-memory calculations are generally so fast that it doesn't really matter. If the dynamic calc goes across blocks then there may be some very noticeable pain.
As for your specific issue you'll have to be more explicit.
there are times where you can get wrong results if the dynamic calculation requires members that might not have been calculated yet(order of operations) like when you reference a member after your formula. Sometimes tagging the member as two pass will help other times it will not. Frankly when possible I prefer to use the stored members as you could force your calculation to be serial
Yes one must track calculation order -- this is one of those areas where ASO is actually quite a bit simpler as you can drive calculation order much more granularly.
Getting back to BSO, yes, calculation order is important, and it can lead to weird design (see the dense Scenario dimension in Sample.Basic which is set that way to force its calc order) considerations.
Regardless, it is fairly common practice to treat most if not all upper level dense calculations as well as in-block (and that is really important from a calculation performance perspective because multiple dynamic calcs of multi-block calculations can be really slow) member formulas as dynamic. I agree that there are times when the approach isn't worth the pain, but that is typically not the case.
o guess if you are going for ease of writing calculations yes use dynamic calc members and test the heck out of it. If you are looking for performance do the calculation on stored members and set the config to set grid expansion to false This will not do any dynamic calcs and keep the block small in memory
ps I'm actually sitting next to Cameron at Oracle Ace Director briefings. an we are enjoying giving each othe grief