1. Database Administrator's Guide for Oracle Essbase
  2. Develop Calculation Scripts for Block Storage Cubes
  3. Runtime Substitution Variables in Calculation Scripts Run in Essbase

Runtime Substitution Variables in Calculation Scripts Run in Essbase

Runtime substitution variables (RTSVs) enable you to dynamically reference member names in Essbase calculation scripts instead of hard coding them. A runtime substitution variable can be included in a calculation script wherever substitution variables are allowed.

In a calculation script, an ampersand (&) must precede the name of the runtime substitution variable.

Every runtime substitution variable used in a calculation script must be declared in the SET RUNTIMESUBVARS calculation command, with a name and default value.

Note:

If a default value is not included in the runtime substitution variable declaration in SET RUNTIMESUBVARS, an error occurs when the calculation script is validated. Oracle recommends that you provide a default value to avoid the validation error and, when running the calculation script, provide the expected value. However, if you do not provide a default value, you can still provide a value at runtime using the execute calculation MaxL statement with the with runtimesubvars grammar.

A description of the runtime substitution variable's data type and data input limit is a string in the <RTSV_HINT>rtsv_description</RTSV_HINT> tag. This tag is optional when the calculation script with runtime substitution variables is run in Essbase; see Hints for Runtime Substitution Variables in Calculation Scripts Run in Essbase. The <RTSV_HINT> tag, with additional metadata, is required when the calculation script with runtime substitution variables is run in Smart View. See Runtime Substitution Variables in Calculation Scripts Run in Smart View.

In this example of SET RUNTIMESUBVARS, three runtime substitution variables are declared: myMarket, salesNum, and pointD. Default values are specified for each runtime substitution variable (for example, the value of myMarket is "New York"). This example applies to a calculation script that is run in Essbase: 

SET RUNTIMESUBVARS
{
   myMarket = "New York";
   salesNum = 10;
   pointD = "Actual"->"Final";
};

At runtime, the default values that are specified in the SET RUNTIMESUBVARS command can be overwritten, using one of these methods:

  • execute calculation MaxL statement with the with runtimesubvars grammar, in which runtime substitution variables are specified as a string of key/value pairs.

    Using the SET RUNTIMESUBVARS example above, at runtime you can overwrite the salesNum default value of 10 with 500 by using the following MaxL statement: 

    execute calculation appname.dbname.calcScriptName with runtimesubvars 'salesNum=500';

    Using this MaxL statement also allows you to provide values for runtime substitution variables that do not have a default value in the SET RUNTIMESUBVARS declaration.

  • An API call in which runtime substitution variables are specified as a string of key/value pairs: IEssCube.calcFileWithRunTimeSubVars (Java API) or EssCalcWithRuntimeSubVars (C API)

  • An API call in which runtime substitution variables can be specified in a text file on the client computer or as a string of key/value pairs: IEssCube.calcFileWithRunTimeSubVarFile (Java API) or EssCalcFileWithRuntimeSubVars (C API)

When specifying runtime substitution variables as a string of key/value pairs, the string must be enclosed with single quotation marks, and key/value pairs must be separated by a semicolon, including a semicolon after the last runtime substitution variable in the string and before the terminal single quotation mark. In this example of a runtime substitution variable string, the name and value of four runtime substitution variables are specified (for example, the value of the runtime substitution variable named "a" is 100): 

'a=100;b=@CHILDREN("100");c="Actual"->"Final";d="New York";'

When specifying runtime substitution variables in a text file, create the text file with an .rsv extension on the client computer. (Essbase does not support runtime substitution variable files located on the Essbase Server computer.) Each line in the file specifies one runtime substitution variable as a key/value pair and must end with a semicolon. In this example of an .rsv file, the name and value of four runtime substitution variables are specified: 

a=100;
b=200;
c=@CHILDREN("100");
d=@TODATE("DD/MM/YY","10/11/12");

When a calculation is executed, runtime substitution variable values are determined in the following order:

  1. Values specified through the execute calculation MaxL statement with the with runtimesubvars grammar, or the APIs (IEssCube.calcFileWithRunTimeSubVars or IEssCube.calcFileWithRunTimeSubVarFile Java APIs; EssCalcWithRuntimeSubVars or EssCalcFileWithRuntimeSubVars C APIs).

  2. Default values specified in the SET RUNTIMESUBVARS calculation command.

Consider these guidelines when using runtime substitution variables:

  • If you declare a runtime substitution variable in SET RUNTIMESUBVARS but do not use the runtime substitution variable in the calculation script, Essbase ignores the unused runtime substitution variable declaration (no warning or exception is generated).

  • Runtime substitution variables have a higher precedence than substitution variables. Therefore, if a substitution variable and a runtime substitution variable have the same name (for example, myProduct), the value of the runtime substitution variable overwrites the value of the substitution variable.

  • If multiple runtime substitution variables have the same name but have different values, only the value of the first instance of the runtime substitution variable is used; all other subsequent values are ignored.

The rules for setting names and values for runtime substitution variables are the same as for substitution variables. See Rules for Setting Substitution Variable Names and Values.

Hints for Runtime Substitution Variables in Calculation Scripts Run in Essbase

When you design Essbase calculation scripts that use runtime substitution variables, you can use the RTSV_HINT to contain text to prompt users to input values at runtime.

The information in this topic applies to running a calculation script with runtime substitution variables in Essbase jobs or API, rather than in Smart View.

In the SET RUNTIMESUBVARS calculation command, the runtime substitution variable declaration can include the <RTSV_HINT>rtsv_description</RTSV_HINT> tag, in which rtsv_description is a string that describes the data type and data input limit (for example, an integer not greater than 100) for the runtime substitution variable. The rtsv_description string is not used in the calculation, but can be helpful as a user prompt.

API calls can retrieve all of the information (name, default value, and description) that is specified in the runtime substitution variable declaration in SET RUNTIMESUBVARS. For example, you can return this information using the REST API Get Essbase Script RTSVs endpoint, the IEssIterator.getCalcFileRunTimeSubVars or IEssIterator.getCalcRunTimeSubVars Java API methods, or the EssGetRuntimeSubVars C API. The rtsv_description string can then be used to prompt a user to input a value at runtime or to validate input data before passing the value to the calculation script.

In this example of SET RUNTIMESUBVARS, each declaration specifies the name, default value, and description of the runtime substitution variable:

SET RUNTIMESUBVARS
{
   myMarket = "New York" <RTSV_HINT>myMarket: Input the value as a member name, such as "New York"</RTSV_HINT>;
   salesNum = 10 <RTSV_HINT>salesNum: Input the value as an integer, such as 100</RTSV_HINT>;
   pointD = "Actual"->"Final" <RTSV_HINT>pointD: Input the value as a member combination, such as "Actual"->"Final"</RTSVVAR_HINT>;
};

Logging Runtime Substitution Variables

To log runtime substitution variables that are used in a calculation script, set the ENABLERTSVLOGGING configuration setting to TRUE. Logging can be implemented at the Essbase Server, application, or database level.

Runtime substitution variable log entries are written to the application log file. Essbase writes one entry to the application log for each string of key/value pairs (or a list of key/value pairs specified in an .rsv file when using the IEssCube.calcFileWithRunTimeSubVarFile Java API or EssCalcFileWithRuntimeSubVars C API).

In the following example, two runtime substitution variables (Entity and Currency) and their values are logged in one entry:

Executing calc script 'calcprofit.csc' with runtime substitution variables {Entity = “MyCompany”; Currency = “USD”;}