Calculate a Cube with Calculation Scripts
Calculation scripts override Essbase block storage outline formulas and give you more control of the cube's calculation flow. You can control the logical calculation flow, prevent overcalculation, and clear data. Calculation scripts also enable you to to copy, export, and calculate data subsets, and to implement variables for information that changes frequently.
A calculation script contains a series of calculation commands, equations, and functions which you can use to control calculations more specifically than the flow determined by the database outline.
In a calculation script, you can perform a default calculation (CALC ALL) or a calculation of your choosing (for example, you can calculate part of a database or copy data values between members).
You must write a calculation script to perform any of the following tasks:
-
Calculate a subset of a database
-
Change the calculation order of the dense and sparse dimensions in a database
-
Perform a complex calculation in a specific order or perform a calculation that requires multiple iterations through the data (for example, some two-pass calculations require a calculation script)
-
Perform any two-pass calculation on a dimension without an accounts tag
-
Calculate member formulas that differ from formulas in the database outline (formulas in a calculation script override formulas in the database outline)
-
Use an API interface to create a custom calculation dynamically
-
Use control of flow logic in a calculation (for example, to use the IF…ELSE…ENDIF or the LOOP…ENDLOOP commands)
-
Clear or copy data from specific members.
-
Define temporary variables for use in a database calculation
-
Force a recalculation of data blocks after you have changed a formula or an accounts property on the database outline
-
Control how Essbase uses Intelligent Calculation when calculating a database
The following calculation script calculates the Actual values from the Year, Measures, Market, and Product dimensions:
FIX (Actual)
CALC DIM(Year, Measures, Market, Product);
ENDFIX
You can create calculation scripts by using the script editor. See Creating Calculation Scripts.
If you run a calculation script from Smart View, the file must have a .csc
extension. However, because a calculation script is a text file, you can use MaxL to run any text file as a calculation script.