Database Reference
In-Depth Information
Budget (
vaBlendedRate = .35;
)
ENFIX
occurrence #1 or Actual is now set to equal .3 and #2 or Budget to equal .35. A new
member named Benefits was added to the measures dimension of Sample.Basic. This
member will be used to store the new benefit calculation.
FIX (Actual, Budget)
Benefits (
Benefits = Payroll * vBlendedRate;
)
ENDFIX
unlike most languages, it is not necessary to explicitly specify vBlendedrate(1) when
we want to reference the Actual blended rate. Essbase will use the appropriate blended
rate value to calculate Benefits depending on the scenario being processed. In the exam-
ple above, if the current scenario is actual, Benefits = Payroll * .3. When the current
member is Budget, Benefits = Payroll * .35.
4 .7. 5
Aggregate Only What Is Needed
Database aggregations are some of the most time-consuming functions that calculation
scripts perform. When running in batch mode, it is not so bad, but having end users
wait for aggregation is undesirable. his issue prompted this topic's editor, Cameron
Lackpour (Chapter 2), to create a technique for aggregating databases that reduces
the overall time dramatically. he has presented this technique at an oDtug (oracle
Development tools user group) kaleidoscope Conference and has written about it on
his blog, but it bears repeating.
The premise behind the technique is that when changes occur in a single data block,
it is faster to update (aggregate) just the data blocks that need to be changed to derive
the correct total. much the way that the intelligent calculation feature works, the outline
structure identifies the blocks that need to be updated. It sounds so simple. I do not
understand why it took Lackpour 10 years to figure it out?
here is how the technique is used. Assume that while building a budget, the end
user selects a product and market so that sales estimates can be entered. once the data
entry is finished, the numbers must be aggregated into the totals. Aggregations could be
performed on the entire database, but it is preferable to update as few blocks as possible.
here is the test calculation script.
Set UpdateCalc off;
Set Msg Detail;
FIX (Jan:Dec, Budget,"Oregon","100-10")
Sales=50;
ENDFIX
FIX ("Jan":"Dec", "Budget")
FIX ("Oregon")
@IALLANCESTORS("100-10") ;
ENDFIX
FIX (@IALLANCESTORS("100-10"))
