Essbase substitution variables are placeholders that hold a value and can be passed in a calc script, load rule, reports etc. Substitution variables can be created at the Essbase server level, the application or at the database level. Forward slash / is the escape character that can be used if single quote is in the substitution variable.
Automating the updating of a substitution variable can be done in:
- MAXL by using the following command
- alter database appname.databasename set variable ‘CurMonth’ ‘Aug’
- ESSCMD by using following command
- updatevariable CurMonth local appname databasename “Aug”
Some of issues and particular scenarios that I have dealt with in regards to a substitution variable are discussed below.
- Resolving an issue related to a member name in Essbase – We had a member named $-REV in the Essbase Outline which was basically dollars related to revenue. Using it in FR for creating a report was causing a problem because “$” is a reserved keyword and symbolizes a substitution variable. The report was not even saving, and causing a validation issue. We were able to work around it by actually creating a substitution variable and assigning it the value “$-REV”. And using that substitution variable in the report. This basically proves that substitution variable is resolved at run time. The other idea would have been to add a member with a dynamic member formula referring to the $-REV member. And using this new member in the reports. Strange enough there is no restriction per Oracle documentation on using a $ symbol before a member name. http://docs.oracle.com/cd/E57185_01/EALAG/esb_restricted_names.html
- Using a substitution variable in a SQL Essbase load rule. Substitution variables can be used in a SQL query in a load rule by using single quote around it. Syntax would be: ‘&Variablename’ and can be in used the where clause or any other place in the SQL query.
- Using server name as the substitution variable – There was a particular scenario where we had to export data to a particular folder on the server. So we were passing the server name along with the file path in the data export command. Every time the script was migrated from Dev to QA to PROD environment, we would forget changing the server name in the script. So we added it as a substitution variable and set the server names in the substitution variable. So only when substitution variables were migrated (which was rarely done) we would have to reset the values.
- Another tip – if there are multiple applications using the same substitution variable then its better to create a variable at the Essbase server level. Essbase uses Database level variable first if available, else looks for application level and then goes to Server level variable if it’s not available at the application/database level.
Substitution variables in Essbase are a powerful tool for an Essbase admin as well as an Essbase developer. They allow for flexibility in terms of managing and maintaining calculations, loads and reports (In Smart View or Financial Reports). Latest feature of Essbase Runtime substitution variables basically mimics the run time prompts that are available in Calc manager. As I explore more about Essbase runtime substitution variables, I will keep you all posted as well.
