OBIEE & Essbase Substitution Variables

In one of my first blog post I indicated that I may deviate from my primary topic to write about other topics that may get my attention.

This topic is one that has taken my attention away from my recent series of past posts on the topic of Maps with OBIEE.

When working with OBIEE and relational databases it is common for a Connection Pool to be dedicated for repository variables.  In fact there is an option that RPD Developers will set so that the Connection Pool for used for Initialization Blocks that populate the repository variabes is different than the Connection Pool for the data.  This serves a few purposes, foremost is that the user ID for the Initialization Blocks and Repository Variables can be set so that it is purely used for this administrative data.

When working with Essbase as a data source I have found that it is more convenient to have a completely separate "database" in the physical layer to support Repository Variables and more specifically Essbase Substitution Variables.

Depending on the scope of the Essbase variable, the naming convention for the BI Server variable is as described below.
Server instance scope
<server name>:<var name>
Application scope
<server name>:<app name>:<var name>
Cube scope
<server name>:<app name>:<cube name>:<var name>

So that my RPD is not specifically tied to an environment and Essbase Server Name I will rename the "database server" and connection pool to a more business purpose name.  Also note that since this imported database will solely be used for Substitution Variables set the name so that there are no spaces in the name.

Finally the initialization block and variable information will need to be updated to match the new name of the "database".  This removes environment issues when migrating Dev > Test > Prod.
 

If there are Application or Cube level substitution variables then import that cube into this "database". 

For the cube(s) that will be used for OBI Analysis; they should be imported into a separate "database" in the Physical Layer.  There will need to be some clean up in the variable manager, delete any repository variables and initialization blocks that are created for those cubes that are used in OBI Analysis.