OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?

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

1 - About

A common way to set up a request variable in order to set a session repository variable is to create a dashboard prompt.

This article show you with the help of the sh schema a little example.

The goal is :

  • to create a year session variable
  • to include it in a “where clause” of the content tab of a logical table source
  • to create a dashboard prompt to set up this session variable
  • and to test it on a little answer with only the year as column

3 - How-to

3.1 - In the repository

3.1.1 - The session variable

In the administration tool :

  • go to Manage/Variable in the menu
  • the variable manager open
  • click on the node : Session/Variables/Non System
  • and create a session variable MyYear with for instance this default initialization string for the oracle database
SELECT '1995' FROM dual

3.2 - Setting the session variable

3.2.1 - In the logical table source

In the logical table source TIMES Fact from the logical fact table Times, let's fill in the where clause with the repository session variable.



3.2.2 - In a physical table of type select

3.3 - In the BI Presentation Service

In BI Presentation service, create :

  • a dashboard prompt
  • and a report

and add them into a dashboard.

3.3.1 - The dashboard prompt

Create a dashboard prompt with the following characteristics :

  • Column : Calendar.“Calendar Year”
  • Control : Edit Box
  • Set Variable : Request Variable and the value MyYear

3.3.2 - The report

Select only the column Calendar.“Calendar Year”.

3.3.3 - The dashboard

To see the value of the server variable, I have had a text box dashboard below the dashboard prompt with this code :

SESSION Variable My YEAR : @{biServer.variables['MyYear']} 


4 - The result

Then when you set for instance the dashboard prompt to 1996 and hit the go button, you will retrieve this result :

The value of the repository session variable is not changed. The new value is only valuable for the query. It's why you can't see in the session manager a new value for the session.

The obiee logical sql request doesn't contain any filter :

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

The logical request doesn't contain any filter :

-------------------- Logical Request (before navigation):

RqList  distinct 
    Times.Calendar Year as c1 GB
OrderBy: c1 asc

But the final query, send in the database, contains one.

-------------------- Sending query to database named orcl SH (id: <<103865>>):

select distinct T3883.CALENDAR_YEAR as c1
     SH.TIMES T3883 /* TIMES Fact */ 
where  ( T3883.CALENDAR_YEAR = '1996' ) 
order by c1

5 - Support

5.1 - The repository variable, NQ_SESSION.MyYear, has no value definition.

5.1.1 - In the repository

You can get this warning when you perform a check global consistency.

WARNINGS:GLOBAL:The repository variable, NQ_SESSION.MyYear, has no value definition.

You can leave it away.


5.1.2 - In the presentation service

The presentation service can also send this error :

Odbc driver returned an error (SQLExecDirectW).
  Error Details
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 23006] 
The session variable, NQ_SESSION.MyYear, has no value definition. (HY000)
SQL Issued: SET VARIABLE MYYEAR='1998';SELECT TIMES_VIEW.calendar_year saw_0 FROM Test ORDER BY saw_0

Try to use the same letter case for the name of the variable (upper and lower).

5.2 - DISABLE_CACHE_HIT always with quotes

If you can get to set a number request variable because it always add quotes, it's a bug because the value is independent from the data type of your presentation variable (works for the version

SET VARIABLE DISABLE_CACHE_HIT='1';SELECT Calendar."Calendar Year" saw_0