OracleBIBlog Search

Friday, July 17, 2009

Essbase Substitution Variables

Substitution variables act as global placeholders for information that changes regularly. This functionality is frequently used with reports where the time values change over time. You simply store the substitution variable values within Essbase Application Server (EAS) for use at any time. Set once - - retrieve many!

For instance, suppose you create a sales report for June and want to rerun the same report 30 days later when you reach July. Without a substitution variable, you must manually update the Time dimension member to "July" instead of "June". With a substitution variable, the Time dimension member automatically retrieves the value stored within EAS (ie "July").

This is particularly helpful when you have complex reports with multiple time members. No more searching for every instance of where you need to change "June" to "July". The Essbase retrieval handles it for you automatically. This really helps improve overall data integrity.

Create the variable(s) and a corresponding string value within EAS. For example, now any time you retrieve the value "MnthMinus1", you will get the value for "FY06 May". Note that when you retrieve in Excel the syntax requires the ampersand "&" character as a variable prefix (ie: to retrieve data for "MnthMinus1", place "&MnthMinus1" in the cell).

A "before and after" example of an Excel retrieval using substitution variables is shown below.

Please contact me or leave comments if you have any questions.