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….
Understanding Dynamic Calc Members
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:
- By retrieving the data value into Spreadsheet Add-in or Smart View
- By running a report script that displays the data value
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.