I stumbled upon a post from Shiv Bharti and thought I’d expand a bit on this idea. First of all to prevent the necessity of using the “Advanced” tab; one of the “don’t do this at home, kids” parts of Answers. Secondly since just recently there was yet another question on OTN asking for advice on how to assign / force new values to a session variable during runtime.

Let’s do the example with an Essbase cube as a data source to showcase that this approach works with any data source. First, I create an initialization block which goes against an Oracle 10gR2 source to be able to write a simple dummy select statement from DUAL indicating CoFSM42 as my default Essbase server. CoFSM41 is a backup instance and hence my Essbase server which I want to switch to in order to see what the backup contains in terms of data loads (in this example, 42 will contain data until today whereas 41 will be the backup from last week, so no data this week).

select ‘CoFSM42’ from DUAL

Of course you can implement more elaborate solutions with control tables holding the servers and schemas of your different environment and instances.

In the connection pool to my Essbase source, I change the “Essbase Server” parameter to “VALUEOF(NQ_SESSION.Essbase_Server)

Starting the server, let’s to a quick check of the data through answers by using the approach Shiv proposed. First, I create the request on my standard subject area which currently points to CoFSM42 due to the session variable.

I see that the data I entered for this week is present. Using the SET VARIABLE prefix, I change the source to CoFSM41.

Ok, that’s empty. Now off to making this switch available on the dashboard through a prompt. The prompt is a fake prompt simply unioning my two server names. Here again, you may use a control table which holds your servers.

The important bit is to choose “Set Variable” as “Request Variable” and in the variable name “Essbase_Server” (my session variable used in the connection pool). Now I combine the prompt and the request in a dashboard to see the effect the “Set Variable” has on the request I’ve built. Bear in mind that there is no “is prompted” speficication or filter on that request apart from my week specification.

Here’s the results prompted with the current server:

And here the ones for the backup server:

So the prompt nicely switches between the different data sources for us without the need of Answers access or within Answers the need to play with the query prefix. Plus, it has shown the use of prompts to changes the values assigned to session variables during runtime.

Now for all those who were hoping for another OBIEE/Essbase post or are thinking about using this to switch cubes easily in their architecture:
Unfortunately this approach can NOT be used to easily switch between cubes. And who’s to blame? The substitution variables.

Essbase substitution variables, when defined on database level (“cube” in OBIEE terminology), arrive in the form of “server:application:database:varaiable”. E.g.: “CoFSM42:Sample:Basic:vCurQtr”.
The “CoFSM42” bit can’t be switched out using a variable since the variable name is always interpreted as a literal string. Taking the example from above, doing something like “VALUEOF(NQ_SESSION.Db_Server):Sample:Basic:vCurQtr” won’t work since the variable is then actually called “VALUEOF…”

Pity, since that would make your development and testing of different Essbase sources extremely flexible.

Cheers,
C.