Intra-Session   ODBC   Calls     
       
          Intra-Session ODBC Function Calls  

 

 

 

Introduction

 

ODBC function calls can prove very useful in implementing OBIEE functionality.  For example, a call to function “SAPurgeAllCache” will purge the cache, while a call to function “NQSSetSessionValue” will set a session variable to a specified value.

 

Let’s start with a definition of an “intra-session” ODBC function call.  In the context of a Presentation Services session, an intra-session ODBC function call is one that is initiated from within the context Presentation Services session, whereas an “extra-session” ODBC function call is one that is initiated from within a different Presentation Services session.

 

OBIEE has an executable, “nQCmd.exe”, that creates a new session, reads a file containing ODBC function calls, executes these calls in turn, and then closes the session.  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”.  At best, this method of invocation is rather convoluted, and it can introduce time delays, depending on the nature of the back-end functionality.  Moreover, since “nQCmd.exe” opens a new BI Server session when it starts up and then closes that session when it completes, it only provides support for extra-session ODBC function calls.  Using “nQCmd.exe” is acceptable when the impact of an ODBC function call is system wide, such as when purging the cache.  But “nQCmd.exe” can’t be used to make intra-session changes, such as persistently changing the value of a session variable within the context Presentation Services session.

 

In this article we’ll describe a method for making intra-session ODBC function calls.  This method can be used for making all ODBC calls.  Its use is essential for session specific calls, but it will also speed up and provide a better alternative to using “nQCmd.exe” in those instances where the scheduled or batch execution of ODBC function calls is not required.

 

 

Using the Raw SQL Window – Manually

 

Answers provides us with a mechanism for making intra-session ODBC function calls.  If we navigate to “Settings => Administration => Issue SQL”, then the Raw SQL window opens up:

*
Raw SQL Window

 

For example, let’s suppose that in the repository we have a session variable “SN_TEST”, whose current value is “Initial”.  We can examine the value of “SN_TEST” using the Session Manager:

*
Session Manager showing Default Session Variable Value

 

If, in the Raw SQL window, we enter an ODBC function call to set the value of “SN_TEST” to “New Value” and press the “Issue SQL” button, then we get a result code of one, indicating success:

*
Successful ODBC Function Call

 

And the Session Manager indicates that the variable has been updated correctly:

*
Session Manager showing Updated Session Variable Value

 

 

Using the Raw SQL Window – Programmatically

 

The problem with using the Raw SQL window manually is that it’s of very little practical value, apart from its occasional use by administrators.  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.

 

The first task is to devise a means to enter the Raw SQL window and then issue the ODBC function call automatically.  The URL in the Raw SQL browser bar is:

*
Raw SQL Browser Bar URL

 

If we add the “SQL” parameter with the ODBC function call to the URL and then URL encode the spaces, we get:

*
Browser Bar URL with ODBC Function Call

 

This URL will open up the Raw SQL window and will issue the ODBC function call in one step.

 

Our next problem is that we want to hide or close the Raw SQL window so that it’s not visible to the user.  In the article entitled “Hiding Web Pages on Processing-Only Calls” we show how to hide a called web page within the calling page using a dimensionless “iframe” called from a text object:

*
IFRAME to call Raw SQL Window

 

So now we are left with only one final issue to resolve.  The URL makes a reference to the subcategory identifier, “scid”, which is generated dynamically by the Presentation Services.  So we need to parse the value for the “scid” out of the context browser bar and then dynamically generate the URL needed to access the Raw SQL window.  In the article entitled “Parsing the Web Browser URL” we show how to extract elements from the browser bar using the Javascript REX engine.  In this case, the code required equals:

*
Extracting the Subcategory Identifier

 

Note, as an alternative we could also extract the subcategory identifier from the context web page using HTML DOM.

 

By combining these techniques we can make an ODBC function call using the following Javascript code fragment:

*
Javascript to make ODBC Function Call

 

Here, we’ve also added code to extract the URL prefix – containing the machine and port – from the browser URL for completeness (we could get by by using a URL prefix beginning “/analytics” instead).

 

Note that we could use the “post” method instead of a URL to achieve the same objective.

 

 

Bypassing the Presentation Services Cache

 

When using the Raw SQL window it’s possible to use, or to bypass, the Presentation Services cache.  The relevant parameter isn’t documented or displayed on screen, but – with a little web page “archaeology” – it turns out to be “UseCache”, with valid values of “yes” and “no”.

 

 

Packaging

 

To make ODBC function calls easy to use, let’s create the outline of a Javascript function, “jf_make_obdc_call”, to encapsulate the coding:

         *
    Javascript Function to make ODBC Function Call

 

Apart from the text of the ODBC function call, “p_odbc_text”, we’ve added a cache bypass parameter, “p_use_cache” with valid values of “yes” and “no”, and a debug parameter, “p_debug” with valid values of “true” and “false”.

 

If the debug parameter equals “true”, then the contents of the “iframe” is displayed within the context dashboard for debugging purposes.

 

Not every window contains the subcategory identifier in the browser bar on entry, so the execution of the ODBC function call is skipped if a value is not found.  However, in many instances the value of the “scid” will be populated prior to its reference: for example, if a dashboard prompt is contained on the page, then the browser bar will be populated with the “scid” when the “Go” button is pressed, and it will be available for reference by subsequent Javascript calls within the same page.  As an alternative, the subcategory identifier can always be obtained by parsing the context web page using HTML DOM.

 

The ODBC function call is also skipped if the text is empty.  This may occur when the Javascript function is called using a presentation variable reference as a parameter – when the variable has not been set and no ODBC function call is available as a default.  In these circumstances, a null default value for the presentation variable, such as “{pv_name}{}”, will ensure that the text is set to ‘’ on entry to the Javascript function.

 

In the previous examples, we’ve manually URL encoded the ODBC text, but in this function we’ve added an encoding function, “encodeURI”, for this purpose.

 

The Javascript function should be placed in script “common.js” in the “b_mozilla” subdirectory:

*
Location of Common.js

 

File “common.js” is loaded automatically for each web page generated by the Presentation Services.

 

To make an ODBC function call, all we have to do is to call this Javascript function from any OBIEE object, such as a text object, that will accept HTML as input; for example:

*
Sample Javascript Call

 

If the text of the ODBC function call is to be passed as the value of a variable, then the containing object will need to support variable substitution.

 

 

Permissions

 

By default only Presentation Server Administrators can issue Raw SQL.  Raw SQL falls under the category of “direct SQL”, so the group to which the relevant users belong must be granted the “Issue SQL Directly” privilege; for example, in the following “Manage Privileges” window the privilege is granted to “Everyone”:

*
Setting Issue SQL Directly Privilege

 

Note that granting this privilege does not give users the ability to issue direct database requests.

 

 

Example – Setting a Session Variable

 

How about some examples?  Let’s start with a session variable “SN_TEST” with a current value of “Initial”, as can be seen from the following Session Manager window:

*
Session Manager showing Initial Session Variable Value

 

If we add the following Javascript call to a text object:

*
Javascript Call to set Session Variable

 

then the debug window shows that the ODBC call has succeeded:

*
Successful ODBC Function Call

 

And the Session Manager window has now been updated to:

*
Session Manager showing updated Session Variable Value

 

indicating that a permanent change to the session variable has been made (note that no such change occurs when using a request variable or a “set variable=’value’” prefix assignment to temporarily set the value for a copy of the session variable).

 

 

Example – Purging the Cache

 

Let’s take an example of purging the BI Server cache.  In the following window the Cache Manager shows that the cache contains two cache entries:

*
Cache Manager showing Cache Entries

 

If we add the following Javascript call to a text object:

*
Javascript Call to purge Cache

 

then the debug window shows that the ODBC call has succeeded:

*
Successful ODBC Function Call

 

And the Cache Manager window, after being refreshed, reveals an empty BI Server cache:

*
Cache Manager showing Empty Cache

 

 

Example – Seeding the Cache

 

The Cache Manager currently shows an empty cache:

*
Cache Manager showing Empty Cache

 

So having empted the cache, let’s seed it with a request.  If we add the following Javascript call to a text object:

*
Javascript Call to seed Cache

 

then the debug window shows that the ODBC call has succeeded:

*
Successful ODBC Function Call

 

And the Cache Manager window, after being refreshed, reveals the logical SQL of the seeding request:

*
Cache Manager showing Seeded Cache