OBIEE - Request variable

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - About

Request Variables are used to update Session Variables in the scope of a logical sql.

You will find it in a OBIEE logical Sql in its SET VARIABLE part.

For instance :

SET VARIABLE MyYear='2000';SELECT Calendar."Calendar Year" saw_0 FROM SH ORDER BY saw_0

Its aim is to set an OBI server session variable :

  • either System
  • or Non System
  • and without security sensitivity

More: OBIEE - How and where can I set a Request variable (SET VARIABLE) ?

Advertising

3 - Syntax

SET VARIABLE MyVariable=MyVale;Logical SQL

For:

  • a string: SET VARIABLE MyVariable='MyString'
  • a date: SET VARIABLE MyDateVariable=date '2013-05-01'
  • a timestamp: SET VARIABLE MyTimeStampVariable=timestamp '2013-05-01 12:12:12'

4 - What is the scope of a request variable in Presentation Service ?

By using a dashboard prompt to define the request variable, the scope of the request variable (as a presentation variable) depend then of the dashboard prompt scope (that you can find in the dashboard prompt definition)

Then you can use it exclusively in a dashboard and not for the entire session. For instance, if you leave the dashboard by opening a new report or going into an other dashboard, the value will be reinitialized to the BI server Session Variable.

To modify a repository session variable for the entire session scope, you can fired this odbc function:

call NQSSetSessionValue('Float MyRepositoryVariable=200;')

Advertising

5 - Example

5.1 - To set a session non system repository variable

SET VARIABLE MyYear='2000';SELECT Calendar."Calendar Year" saw_0 FROM SH ORDER BY saw_0

5.2 - System Repository Variable

5.2.1 - To set the log level for further analyse

This variable allow you to set the LOGLEVEL for further analyse.

SET VARIABLE LOGLEVEL=4;SELECT "Customer"."Name" FROM SH ...

5.2.2 - To disable the query cache hit

The variable DISABLE_CACHE_HIT disable the use of the query cache of OBI Server.

SET VARIABLE DISABLE_CACHE_HIT=1;SELECT "Customer"."Name" FROM SH ...

6 - Reference / Documentation

Advertising