Everything Oracle

Home

Everything Oracle

 

 

 

 

 

 

Introduction

 

When it comes to manipulating session variables from within Answers and Dashboards three possibilities come to mind:

 

  Request Variables

  URL Parameters

  NQSSetSessionValue

 

Request Variables

 

For a start, using a request variable or a request variable assignment within a request prefix field (“set <variable>=<value>;”) doesn’t update a session variable; it simply appears to do so.  A temporary copy of the session variable is created and the request variable value is assigned to this copy while the request is being constructed and executed.  However, the Session Manager reveals that the underlying session variable has not been updated either temporarily or permanently (for a detailed explanation of the processing of request variables see the article entitled “Exactly what is a Request Variable?”).

 

URL Parameters

 

It is possible to update the value of a session variable by passing the name and value in a URL, but this approach is very inflexible: it requires that the name of the session variable is added to an “Auth” section within configuration file “instanceconfig.xml”.  And, needless to say, an OBIEE administrator will not look kindly on requests to alter configuration files every time a developer opines that yet another session variable is required!

 

NQSSetSessionValue

 

There is only one practical method for updating session variables and that is to use the ODBC function that has been specifically created for this purpose, “NQSSetSessionValue”.

 

Function “NQSSetSessionValue” has the following usage:

 

  call NQSSetSessionValue( '<data type> <variable name>=<value>;' )

 

Valid data types are “Date”, “DateTime”, “Float”, “Integer”, “String”, and “Time”.  Their formats and the corresponding data type used for storage in the repository are as follows:

 

Data Type	Format	Stored As
Date	YYYY-MM-DD	DATE
DateTime	YYYY-MM-DD HH:MM:SS	DATETIME
Float	99...99.99	DOUBLE
Integer	99...99	INT
String	XX...XX	LONGVARCHAR
Time	HH:MM:SS	TIME

 

For example, to set session variable “SN_TEST” to a value of “Value” the following ODBC call can be used:

 

  call NQSSetSessionValue( 'String SN_TEST=Value;' )

 

 

Third-Party Clients

 

Let’s open up the repository online using the Administration Tool and create a session variable “SN_TEST” with a value of “Initial”.  If we open the Session Manager, we see a single active session – that of the Administration Tool itself:

 

and under the “Variables” tab we can see that session variable “SN_TEST” has a value of “Initial”:

 

As “NQSSetSessionValue” is an ODBC function, it can be used by any client application of the BI Server, and not just the Presentation Services.  Included with the OBIEE installation is a simple ODBC client application, available from the “Oracle Business Intelligence” menu, called “ODBC Client”.  If we open up the “ODBC Client” application and select “Open DataBase” from the “File” menu:

 

we get the “ODBC Logon” window.  To connect to the BI Server we select “AnalyticsWeb” as the “DSN” from the drop-down list and enter a repository user name and password:

 

If we look in the Session Manager window we can see that an ODBC session has been added to the list of sessions:

 

and that the corresponding value for variable “SN_TEST” within this newly created session is also equal to “Initial”:

 

Returning to the “ODBC Client” window, if we select “Enter SQL Query” from the “Utility” menu:

 

then the “SQL Statement Editor” window appears.  So let’s enter an ODBC function call to set the value of “SN_TEST” to “New Value” into the Editor:

 

When we press the “Execute” button and return to the Session Manager window we find that the value of “SN_TEST” has indeed been correctly updated:

 

 

nQCmd.exe

 

Now, having been able to change the value of a session variable from a third-party client let’s turn our attention to doing the same from within Answers and Dashboards.

 

Let’s begin by eliminating what might seem at first to be a viable option.  OBIEE provides an executable, “nQCmd.exe”, that reads a file containing ODBC function calls and executes these calls in turn.  Executable “nQCmd.exe” can be run from within Answers by making a call to database functionality that, in turn, runs a batch job that contains a call to “nQCmd.exe”.

 

Let’s create a text file, “SetSessionValue.txt”, that contains an ODBC function call to set the value of session variable “SN_TEST” to “nQCmd Value”:

 

We’ve also added a long list of ODBC calls to purge the cache to ensure that “nQCmd.exe” runs for long enough for us to catch it in action.  To run these commands let’s create a batch file containing the following command:

 

Now our Session Manager window currently shows only the Administration Tool session:

 

If we look in the Session Manager window while the batch file is running we can see a new ODBC session:

 

and if we look in the “Variables” tab we can see that the value of session variable “SN_TEST” has been updated:

 

However, as soon as all the ODBC function calls in the file have been executed the Session Manager window returns to:

 

While “nQCmd.exe” can be used when the impact of an ODBC function call is system wide, such as when purging the cache, it can’t be used to implement intra-session functionality, such as changing the value of a session variable in the context of a particular Presentation Services session.  Executable “nQCmd.exe” creates its own BI Server session, so any session variable changes it makes will be restricted to that session, and even those changes will be lost when the command completes and the session is closed.

 

So, when it comes to making session variable changes from within Answers and Dashboards we can’t use “nQCmd.exe”.

 

 

Raw SQL

 

So does Answers have the equivalent of the “SQL Statement Editor” window that we have seen within the “ODBC Client”?  It does.  If we navigate to “Settings => Administration => Issue SQL”, then the Raw SQL window opens up:

 

We can use this window in much the same way as the “ODBC Client”.  The problem with using the Raw SQL window is that in practice we require the window and its functionality to be manipulated programmatically, at, for example, the press of a button or automatically on entry to a dashboard.  A method of achieving this objective is described in the article entitled “Intra-Session ODBC Function Calls”.  In this article we show how to construct a Javascript function, “jf_make_obdc_call”:

 

to run any ODBC function call programmatically.  Let’s use this function to create another Javascript function, one designed to set a specified session variable to a specified value:

 

The parameter “p_debug” has values of “true” and “false”.  If set to “true” then a debug window is displayed showing the ODBC function call and its result code for the purposes of debugging.

 

So, to set the value of session variable “SN_TEST” to “New Value” all we have to do is to call the following Javascript:

 

from any dashboard object that accepts HTML as input.

 

 

Updating Static Session Variables

 

Let’s create two session variables in the repository: “SC_TEST” with column-wise initialization to a value of “Column-Wise – Initial”:

 

and “SR_TEST” with row-wise initialization to a value of “Row-Wise – Initial”:

 

Note that in order to use ODBC function call “NQSSetSessionValue” with a standard, column-wise initialized session variable the “Enable any user to set this value” checkbox must be ticked if the user does not have administrative privileges:

 

If we create a request to retrieve the values of these variables, then we get:

 

as expected.

 

Let’s make two calls to function “jf_set_session_variable” to change the word “Initial” to “Updated” and add the calls to a text object on a dashboard:

 

Now when we enter the dashboard, and then rerun our request we get:

 

If we examine the Session Manager “Variables” tab for the Presentation Services ODBC session we get:

 

So our Javascript function has updated both column- and row-wise initialized session variables correctly.

 

 

Creating and Updating Dynamic Session Variables

 

Now, all the information we read about session variables suggests that they are static by nature: true, we may be able to update the values of these variables dynamically, but we are led to believe that we cannot create the variables themselves dynamically.  Instead, it is implied that the variables must be predefined in the repository and then initialized using initialization blocks when a BI Server session commences.

 

Note that at present there is no session variable “SD_TEST” defined in the repository.  If we modify our Answers request to select this variable, then when we try to save the column formula we get an error confirming that the variable does not exist in the repository:

 

But let’s ignore the error and save the request.

 

Now let’s replace our dashboard Javascript calls with a call to set the non-existent session variable “SD_TEST” to a value of “Dynamic”:

 

If we enter the dashboard page to run the script, and then run the request we find that the session variable has been created and has been initialized correctly to the specified value:

 

And, by way of confirmation, when we examine the Session Manager window in the repository we can see that session variable “SD_TEST” are been added to the list of its predecessors:

 

 

Summary

 

ODBC function “NQSSetSessionValue” has an unexpected and very powerful trick up its sleeve.  It has the ability to dynamically create session variables in the repository, as well as updating the values of those that already exist.  This feature – similar to that of dynamically giving a name to a section of memory in a programming language – opens up many possible applications.  In particular, it means that front-end developers have a simple means of allocating session-persistent storage for use by Answers and Dashboards, without having to modify the repository.  By helping to decouple report building and maintenance from repository building and maintenance this feature helps to simplify and lower the cost of building and maintaining OBIEE applications.

 

 

 

Everything Oracle

Home

Everything Oracle

 

Copyright © 2009 PWG Consulting, All Rights Reserved