single LTS and multiple LTS

 The logical table created at the BMM layer can be based on the data from a single physical layer table, when it is called single Logical Table source. When the specific logical layer table is dependent on the columns of different physical layer tables, it is called Multiple LTS. Most of the time we will be dealing with Multiple LTS.

Exactly what is a request variable?

   Exactly what is a request variable?  

Introduction

It’s a question that OBIEE developers often ask.  And there is a good reason for the uncertainty that shrouds this species of variable.  The OBIEE documentation only contains a single reference:

  In the Set Variable list, you can choose whether or not to populate a variable for the dashboard prompt using a server request variable or a presentation variable.

This “singularly informative” statement – as is so often the case with the OBIEE documentation – leaves us none the wiser as to what a request variable does or doesn’t do.

After playing around with request variables for a while, most developers conclude that they have something to do with setting session variables, but, frustratingly, sometimes this “setting” seems to work and sometimes it doesn’t.

Definition

So exactly what is a request variable?  Well, put succinctly:

  A request variable is a variable that is used to temporarily override the value of a session variable.

And, with a little more precision, a request variable is:

  A variable that is defined within the Presentation Services;

  A variable whose scope (page or dashboard) is that of the associated dashboard prompt that defines the request variable and its value;

  A variable whose name should not match that of a repository variable or a security system session variable; and

  A variable that overrides the value of the corresponding session variable (should it exist) for the purpose of BI Server request parsing and physical SQL construction for all requests that are issued from dashboard pages that fall within the scope of the variable’s definition.

Note, using a request variable does not change the actual value of a session variable – neither temporarily nor permanently.  It simply appears to change the value for certain purposes during the execution of a request.

How it works

Request Variable Definition

Here’s the output from a simple dashboard request to select the values of column “LOC” from table “DEPT” in the standard Oracle schema “SCOTT”:

Dashboard Request

In the absence of a request variable being defined within the scope of the dashboard page from which the request is invoked, the logical SQL sent to the BI Server is (with a little formatting) as follows:

Logical SQL without Request Variable

Now let’s assume that we add a dashboard prompt defined to populate a request variable “SN_TEST”:

Dashboard Prompt with Request Variable Definition

We enter a value of “Value” in the edit box:

Dashboard Prompt with Request

Then when we press the “Go” button the specified value is assigned to the request variable within the Presentation Services (in this respect a request variable is much like a presentation variable).  Internally, in terms of pseudocode, we can imagine the following assignment:

<request variable name> := <request variable value>

or

SN_TEST := 'Value';

Note that if we just had the “Go” button and no request then no logical SQL would be sent to the BI Server – assigning a value to a request variable does not, in and of itself, modify anything in the BI Server.

Request Variable Dispatch

Once a request variable has been set, then whenever any request that falls within the scope of the prompt (page or dashboard) is sent to the BI Server, a request variable assignment is added as a prefix to the logical SQL that defines the request.  In the case of our example, the logical SQL becomes:

Logical SQL with Request Variable

We can see that a request variable assignment, “SN_TEST=’Value’”, has been added as part of the prefix to the select statement.  So now we can see why the variable is called a “request variable”: it’s a variable that accompanies requests on their journey from the Presentation Services to the BI Server.

Note that a request variable can have any name we like.  It does not have to match the name of an existing server variable, though it’s unlikely to be of much use unless it does.

Note that this request variable assignment will be sent for all requests sent from the dashboard page if the scope of the prompt is the page and for all requests sent from all dashboard pages belonging to the context dashboard if the scope of the prompt is the dashboard.  The request variable assignment is sent irrespective of whether or not requests reference the value of the request variable.

Request Variable Processing

Once a request, accompanied by a request variable assignment, reaches the BI Server then how is it processed?  The following pseudocode illustrates the observed behaviour while processing a request:

IF <request contains request variable assignment> THEN
   IF <request variable name equals security session variable
         name> OR
      <request variable name equals repository variable name>
   THEN
      <raise error>
   ELSE
      <create temporary session variable with request variable
      name and assign to it the request variable value>
   END IF
END IF

IF <request processing references a session variable> THEN
   IF <temporary session variable of same name exists> THEN
      <use value of temporary session variable>
   ELSE
      <use value of original session variable>
   END IF
END IF

IF <temporary session variable was created> THEN
   <destroy temporary session variable>
END IF

Note to avoid an error the name of the request variable must not match that of an existing repository variable or a security system session variable.

Session Variable Override is Temporary

The key point to remember, and one that many OBIEE developers find confusing given the dearth of documentation on the topic, is that if a session variable with the same name as that of a request variable already exists in the repository, then the request variable assignment does not permanently change the value of the session variable with which it is associated: it only appears to be changed, and even then only for certain purposes, for the duration of request processing and execution. 

To illustrate the temporary nature of the session variable override let’s start off with an initialization block for a session variable “SN_TEST”:

Session Variable Initialization Block

If the user does not have Administration privileges, then the “Enable any user to set the value” checkbox in the variable definition pop-up window must be ticked, or, alternatively, row-wise initialization must be used instead (with row-wise initialization the privilege is granted automatically).

Let’s create two connection scripts, pre- and post-query, to log the value of the session variable before and after the request execution:

      
            Logging Connection Scripts

Here “DP_LOG_REQUEST” is a standalone stored procedure that contains the pragma “autonomous_transaction”, an insert into a logging table, and a “commit”.

If we run our request first from Answers, without the prompt being in effect, and then run the request from the dashboard after entering a value of “Value” for “SN_TEST” and pressing the “Go” button we get the following log entries:

Session Variable Log Entries

The first pair of entries with session variable values of “Initial” shows that the session variable has its normal value assigned when the initialization block was executed.  The second pair of entries with session variable values of “Value” shows that the session variable value has been temporarily overridden by the value assigned to the request variable.

But this change to the session variable reference is not a permanent change to the session variable.  If we examine the value of the session variable using the Session Manager in the repository after the request has completed execution we see that its value equals that set by the initialization block:

Session Variables in Session Manager

(see the next section for what happens while the request is executing).

One consequence of this behaviour is that we cannot use a request variable to propagate a change from one dashboard to another – something that OBIEE developers often try to do.  This would only be possible if the value of the session variable in the repository was changed on a permanent basis (note, changing the session variable permanently can be achieved by calling ODBC function “NQSSetSessionValue” – but that’s another article).

Change of Reference not a change of Functionality

Change of Reference

Whenever a session variable is referenced in the course of constructing the physical SQL to be sent to the database, the temporary value associated with the corresponding request variable assignment is used instead.

For example, we might add a “where” clause on the “Content” tab of the logical table source for logical table “DEPT”:

Where Clause in Logical Table Source

we might create a new logical column “Upper Location” that references the session variable:

Session Variable Reference in Logical Column Definition

and we might reference both “Upper Location” and the session variable directly within a request:

Session Variable Reference in Request

Then when we enter a value of “BOSTON” for our prompt value we get:

Dashboard Prompt and Request

(well, yes, “lower” might have been a better choice!)  So all the references to the session variable, both within the incoming request SQL and within the repository, evaluate to the request variable value.

No change of Functionality

In the case of system session variables we have in addition to the value of the variable the functionality represented by that variable.  A request variable assignment effectively causes the BI server to make a copy of the associated session variable, and this copy takes precedence over the original when it comes to variable references made during request processing and execution, but it does not alter the value of the original session variable or propagate the changes in functionality that are normally associated with session variable initialization.

For example, let’s set up an initialization block to populate the session variable “DISPLAYNAME”:

Initialization Block for Display Name

If the user does not have Administration privileges, then the “Enable any user to set the value” checkbox in the variable definition pop-up window must be ticked, or, alternatively, row-wise initialization must be used instead (with row-wise initialization the privilege is granted automatically).

Let’s set up a request to display the value of the session variable:

Long Running Request

Request execution will last for at least 10 seconds due to the “EVALUATE” call to standalone stored function “DF_WAIT”, a function that loops for a specified period of time before returning a value:

Evaluate Function Call

This delay allows us to examine what happens during request execution.

On entry to the dashboard we get a display name of “SIEBEL”:

Default Request Running

and, when the query completes, we can see that in the absence of the request variable being set the referenced value of the session variable “DISPLAYNAME” is also equal to “SIEBEL” as expected from the initialization block:

Default Request Completed

If we now set the value of the request variable to “ORACLE” and initiate the request, then we can see that while the request is running the display name remains unchanged:

Request Variable Request Running

When the query completes the display name still remains unchanged, but we can see that the value of session variable “DISPLAYNAME” that was referenced while the request was executing was in fact “ORACLE”:

Request Variable Request Completed

If, while the request is executing, we look in the Session Manager we can see that the request status is “executing”:

Request Executing in Session Manager

and if we look at the variable values (after a refresh):

Session Variable Values in Session Manager

we can see that the value of session variable “DISPLAYNAME” is unchanged from that set by the initialization block.

So, from the perspective of session variable references the session variable appears to have changed, but the value has not actually been changed, even while the request was executing.  And the functionality associated with the session variable has not been changed either – the dashboard display name has not been updated, either temporarily or permanently, by sending the request variable assignment to the BI Server.

Coding Request Variable Assignments Directly

As well as being generated by dashboard prompts, request variable assignments can also be directly coded into the logical SQL, even if no request variable has been defined or none is within scope.

Advanced Tab – Literal Value

For example, if the code:

SET VARIABLE SN_TEST='Direct Assignment';

is entered into the “Prefix” field on the “Advanced” tab in Answers, the logical SQL is set to:

         
            Request Logical SQL

The assigned value will be used to create a temporary session variable whenever the request is sent to the BI Server (note, adding the code to the “Postfix” field does not work in the same way).

Advanced Tab – Presentation Variable Reference

In practice, however, the value of a direct request variable assignment is likely to be more useful if a presentation variable reference is used instead of a literal.  For example, let’s use a presentation variable instead of a request variable in the dashboard prompt:

Presentation Variable Definition

If we add code to the “Prefix” field on the “Advanced” tab that contains a reference to the presentation variable:

Advanced Prefix Field with Presentation Variable Reference

then we get a value for “SQL Issued” of:

         
            Request Logical SQL with Presentation Variable Substitution

On entry to the dashboard we get the results of the default query:

Dashboard with Default Request

and when we set the presentation variable value to “Value” we get:

Dashboard with Request Variable Request

Note that even though the “SQL Issued” shows that the default value for the presentation variable has been substituted, when a request is actually issued and sent to the BI Server the contents of the “Prefix” field is re-evaluated using the current value of the presentation variable and the SQL Issued is updated before the logical SQL is sent to the BI Server.

Using a direct request variable assignment and a presentation variable produces a similar result to creating a request variable, except that the scope of the request variable assignment is restricted to a particular request, rather than to all requests that are within the scope of the prompt.

SQL Results

Though not very useful, a request variable assignment can also be incorporated into the “SQL Results” option of the “Show” column in a dashboard prompt (no request variable is defined by the prompt):

Dashboard Prompt with Custom SQL LOV

In this case, the temporary session variable would only be defined for the duration of the request that retrieves the values for the prompt and not for any subsequent requests issued from the dashboard.

Exactly what is a request variable?

   Exactly what is a request variable?  

Introduction

It’s a question that OBIEE developers often ask.  And there is a good reason for the uncertainty that shrouds this species of variable.  The OBIEE documentation only contains a single reference:

  In the Set Variable list, you can choose whether or not to populate a variable for the dashboard prompt using a server request variable or a presentation variable.

This “singularly informative” statement – as is so often the case with the OBIEE documentation – leaves us none the wiser as to what a request variable does or doesn’t do.

After playing around with request variables for a while, most developers conclude that they have something to do with setting session variables, but, frustratingly, sometimes this “setting” seems to work and sometimes it doesn’t.

Definition

So exactly what is a request variable?  Well, put succinctly:

  A request variable is a variable that is used to temporarily override the value of a session variable.

And, with a little more precision, a request variable is:

  A variable that is defined within the Presentation Services;

  A variable whose scope (page or dashboard) is that of the associated dashboard prompt that defines the request variable and its value;

  A variable whose name should not match that of a repository variable or a security system session variable; and

  A variable that overrides the value of the corresponding session variable (should it exist) for the purpose of BI Server request parsing and physical SQL construction for all requests that are issued from dashboard pages that fall within the scope of the variable’s definition.

Note, using a request variable does not change the actual value of a session variable – neither temporarily nor permanently.  It simply appears to change the value for certain purposes during the execution of a request.

How it works

Request Variable Definition

Here’s the output from a simple dashboard request to select the values of column “LOC” from table “DEPT” in the standard Oracle schema “SCOTT”:

Dashboard Request

In the absence of a request variable being defined within the scope of the dashboard page from which the request is invoked, the logical SQL sent to the BI Server is (with a little formatting) as follows:

Logical SQL without Request Variable

Now let’s assume that we add a dashboard prompt defined to populate a request variable “SN_TEST”:

Dashboard Prompt with Request Variable Definition

We enter a value of “Value” in the edit box:

Dashboard Prompt with Request

Then when we press the “Go” button the specified value is assigned to the request variable within the Presentation Services (in this respect a request variable is much like a presentation variable).  Internally, in terms of pseudocode, we can imagine the following assignment:

<request variable name> := <request variable value>

or

SN_TEST := 'Value';

Note that if we just had the “Go” button and no request then no logical SQL would be sent to the BI Server – assigning a value to a request variable does not, in and of itself, modify anything in the BI Server.

Request Variable Dispatch

Once a request variable has been set, then whenever any request that falls within the scope of the prompt (page or dashboard) is sent to the BI Server, a request variable assignment is added as a prefix to the logical SQL that defines the request.  In the case of our example, the logical SQL becomes:

Logical SQL with Request Variable

We can see that a request variable assignment, “SN_TEST=’Value’”, has been added as part of the prefix to the select statement.  So now we can see why the variable is called a “request variable”: it’s a variable that accompanies requests on their journey from the Presentation Services to the BI Server.

Note that a request variable can have any name we like.  It does not have to match the name of an existing server variable, though it’s unlikely to be of much use unless it does.

Note that this request variable assignment will be sent for all requests sent from the dashboard page if the scope of the prompt is the page and for all requests sent from all dashboard pages belonging to the context dashboard if the scope of the prompt is the dashboard.  The request variable assignment is sent irrespective of whether or not requests reference the value of the request variable.

Request Variable Processing

Once a request, accompanied by a request variable assignment, reaches the BI Server then how is it processed?  The following pseudocode illustrates the observed behaviour while processing a request:

IF <request contains request variable assignment> THEN
   IF <request variable name equals security session variable
         name> OR
      <request variable name equals repository variable name>
   THEN
      <raise error>
   ELSE
      <create temporary session variable with request variable
      name and assign to it the request variable value>
   END IF
END IF

IF <request processing references a session variable> THEN
   IF <temporary session variable of same name exists> THEN
      <use value of temporary session variable>
   ELSE
      <use value of original session variable>
   END IF
END IF

IF <temporary session variable was created> THEN
   <destroy temporary session variable>
END IF

Note to avoid an error the name of the request variable must not match that of an existing repository variable or a security system session variable.

Session Variable Override is Temporary

The key point to remember, and one that many OBIEE developers find confusing given the dearth of documentation on the topic, is that if a session variable with the same name as that of a request variable already exists in the repository, then the request variable assignment does not permanently change the value of the session variable with which it is associated: it only appears to be changed, and even then only for certain purposes, for the duration of request processing and execution. 

To illustrate the temporary nature of the session variable override let’s start off with an initialization block for a session variable “SN_TEST”:

Session Variable Initialization Block

If the user does not have Administration privileges, then the “Enable any user to set the value” checkbox in the variable definition pop-up window must be ticked, or, alternatively, row-wise initialization must be used instead (with row-wise initialization the privilege is granted automatically).

Let’s create two connection scripts, pre- and post-query, to log the value of the session variable before and after the request execution:

      
            Logging Connection Scripts

Here “DP_LOG_REQUEST” is a standalone stored procedure that contains the pragma “autonomous_transaction”, an insert into a logging table, and a “commit”.

If we run our request first from Answers, without the prompt being in effect, and then run the request from the dashboard after entering a value of “Value” for “SN_TEST” and pressing the “Go” button we get the following log entries:

Session Variable Log Entries

The first pair of entries with session variable values of “Initial” shows that the session variable has its normal value assigned when the initialization block was executed.  The second pair of entries with session variable values of “Value” shows that the session variable value has been temporarily overridden by the value assigned to the request variable.

But this change to the session variable reference is not a permanent change to the session variable.  If we examine the value of the session variable using the Session Manager in the repository after the request has completed execution we see that its value equals that set by the initialization block:

Session Variables in Session Manager

(see the next section for what happens while the request is executing).

One consequence of this behaviour is that we cannot use a request variable to propagate a change from one dashboard to another – something that OBIEE developers often try to do.  This would only be possible if the value of the session variable in the repository was changed on a permanent basis (note, changing the session variable permanently can be achieved by calling ODBC function “NQSSetSessionValue” – but that’s another article).

Change of Reference not a change of Functionality

Change of Reference

Whenever a session variable is referenced in the course of constructing the physical SQL to be sent to the database, the temporary value associated with the corresponding request variable assignment is used instead.

For example, we might add a “where” clause on the “Content” tab of the logical table source for logical table “DEPT”:

Where Clause in Logical Table Source

we might create a new logical column “Upper Location” that references the session variable:

Session Variable Reference in Logical Column Definition

and we might reference both “Upper Location” and the session variable directly within a request:

Session Variable Reference in Request

Then when we enter a value of “BOSTON” for our prompt value we get:

Dashboard Prompt and Request

(well, yes, “lower” might have been a better choice!)  So all the references to the session variable, both within the incoming request SQL and within the repository, evaluate to the request variable value.

No change of Functionality

In the case of system session variables we have in addition to the value of the variable the functionality represented by that variable.  A request variable assignment effectively causes the BI server to make a copy of the associated session variable, and this copy takes precedence over the original when it comes to variable references made during request processing and execution, but it does not alter the value of the original session variable or propagate the changes in functionality that are normally associated with session variable initialization.

For example, let’s set up an initialization block to populate the session variable “DISPLAYNAME”:

Initialization Block for Display Name

If the user does not have Administration privileges, then the “Enable any user to set the value” checkbox in the variable definition pop-up window must be ticked, or, alternatively, row-wise initialization must be used instead (with row-wise initialization the privilege is granted automatically).

Let’s set up a request to display the value of the session variable:

Long Running Request

Request execution will last for at least 10 seconds due to the “EVALUATE” call to standalone stored function “DF_WAIT”, a function that loops for a specified period of time before returning a value:

Evaluate Function Call

This delay allows us to examine what happens during request execution.

On entry to the dashboard we get a display name of “SIEBEL”:

Default Request Running

and, when the query completes, we can see that in the absence of the request variable being set the referenced value of the session variable “DISPLAYNAME” is also equal to “SIEBEL” as expected from the initialization block:

Default Request Completed

If we now set the value of the request variable to “ORACLE” and initiate the request, then we can see that while the request is running the display name remains unchanged:

Request Variable Request Running

When the query completes the display name still remains unchanged, but we can see that the value of session variable “DISPLAYNAME” that was referenced while the request was executing was in fact “ORACLE”:

Request Variable Request Completed

If, while the request is executing, we look in the Session Manager we can see that the request status is “executing”:

Request Executing in Session Manager

and if we look at the variable values (after a refresh):

Session Variable Values in Session Manager

we can see that the value of session variable “DISPLAYNAME” is unchanged from that set by the initialization block.

So, from the perspective of session variable references the session variable appears to have changed, but the value has not actually been changed, even while the request was executing.  And the functionality associated with the session variable has not been changed either – the dashboard display name has not been updated, either temporarily or permanently, by sending the request variable assignment to the BI Server.

Coding Request Variable Assignments Directly

As well as being generated by dashboard prompts, request variable assignments can also be directly coded into the logical SQL, even if no request variable has been defined or none is within scope.

Advanced Tab – Literal Value

For example, if the code:

SET VARIABLE SN_TEST='Direct Assignment';

is entered into the “Prefix” field on the “Advanced” tab in Answers, the logical SQL is set to:

         
            Request Logical SQL

The assigned value will be used to create a temporary session variable whenever the request is sent to the BI Server (note, adding the code to the “Postfix” field does not work in the same way).

Advanced Tab – Presentation Variable Reference

In practice, however, the value of a direct request variable assignment is likely to be more useful if a presentation variable reference is used instead of a literal.  For example, let’s use a presentation variable instead of a request variable in the dashboard prompt:

Presentation Variable Definition

If we add code to the “Prefix” field on the “Advanced” tab that contains a reference to the presentation variable:

Advanced Prefix Field with Presentation Variable Reference

then we get a value for “SQL Issued” of:

         
            Request Logical SQL with Presentation Variable Substitution

On entry to the dashboard we get the results of the default query:

Dashboard with Default Request

and when we set the presentation variable value to “Value” we get:

Dashboard with Request Variable Request

Note that even though the “SQL Issued” shows that the default value for the presentation variable has been substituted, when a request is actually issued and sent to the BI Server the contents of the “Prefix” field is re-evaluated using the current value of the presentation variable and the SQL Issued is updated before the logical SQL is sent to the BI Server.

Using a direct request variable assignment and a presentation variable produces a similar result to creating a request variable, except that the scope of the request variable assignment is restricted to a particular request, rather than to all requests that are within the scope of the prompt.

SQL Results

Though not very useful, a request variable assignment can also be incorporated into the “SQL Results” option of the “Show” column in a dashboard prompt (no request variable is defined by the prompt):

Dashboard Prompt with Custom SQL LOV

In this case, the temporary session variable would only be defined for the duration of the request that retrieves the values for the prompt and not for any subsequent requests issued from the dashboard.