Custom Calculations on Essbase ASO Cubes
Custom calculations extend the analytical capabilities of Oracle Essbase by enabling the execution of calculations on aggregate storage databases and the ability to store the results (rather than calculate dynamically – which may not always be preferred – specifically with large datasets, where top retrieval performance is paramount).
Custom calculation scripts are expressed in MDX and update target level 0 cells. You can perform custom calculations on an aggregate storage database using the MaxL statement execute calculation (aggregate storage version). Using the Essbase API, use EssPerformCustomCalcASO to perform or verify a custom calculation.
This post covers the execution of a Custom Calc via use of MaxL: The MaxL script defines the POV and the Source Region (more on that below). A text file contains the formulas that need to be executed.
This is relatively new functionality, therefore please note it does have some limitations: there is no editor to validate syntax (some trial and error involved), and at this point it does not support MDX member functions (like Descendants, children, etc -> only constant tuples are supported). With that said, just being able to calculate these formulas and store the values was extremely helpful with overall cube and retrieval performance.
Execute Calculation (Aggregate Storage)
Execute a custom calculation script expressed in MDX, specifying the script file, source region, and point of view (POV). Optionally specify the target, offset, and debit or credit
Minimum permission required: execute.
You can execute custom calculations with the following options:
- The clauses following the with keyword can be entered in any order, each separated by white space.
- Each clause can only be entered once.
- The script_file, pov, and sourceregion clauses are mandatory; the others are optional.
- You can specify only stored, level-0 members on the left side of the assignment
statement in the custom calculation script; do not use upper-level members,
attribute members, or dynamic calc members.
- You can specify only stored, level-0 members in the following clauses: DebitMember, CreditMember, Target, and Offset.
The following statement executes script.txt referenced above. For a sample use case, see “Performing Custom Calculations and Allocations on Aggregate Storage Databases” in the Oracle Essbase Database Administrator’s Guide.
Posted on Wed, November 28, 2012
by Ed Apuy