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

Bi Server Architecture With Client

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

How-to

In the repository

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

Obiee Session Non System Repository Variable

Setting the session variable

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.

"orcl SH"."".SH."TIMES Fact".CALENDAR_YEAR =  VALUEOF(NQ_SESSION."MyYear")

Obiee Year Where Clause Lts Times Fact

In a physical table of type select

Obiee Physical Table Select Session Variable

In the BI Presentation Service

In BI Presentation service, create :

  • a dashboard prompt
  • and a report

and add them into a dashboard.

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

Obiee Prompt Set Variable

The report

Select only the column Calendar.“Calendar Year”.

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']} 

Obiee Dashboard Result Server Variable 1995

The result

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

Obiee Dashboard Result Server Variable 1996

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
from 
     SH.TIMES T3883 /* TIMES Fact */ 
where  ( T3883.CALENDAR_YEAR = '1996' ) 
order by c1

Support

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

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.

In the presentation service

The presentation service can also send this error :

Odbc driver returned an error (SQLExecDirectW).
  Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
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).

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 10.3.4.1).

SET VARIABLE DISABLE_CACHE_HIT='1';SELECT Calendar."Calendar Year" saw_0 
FROM SH ORDER BY saw_0





Discover More
Obiee Answers Advanced Sql Clauses
OBIEE - How and where can I set a Request variable (SET VARIABLE) ?

A request variable is an OBI Server Session Variable but with the scope of the request. The value of the session variable will not be change for the session. You can set it up in three main ways : ...
Obiee Dashboard Prompt Scope
OBIEE - Request variable

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 : Its aim is to set an OBI server...
Obiee Session Variable In Connection Pool
OBIEE - Where can I use a BI Server variable (session/repository) ?

When you have create a server variable, you have to reference it. You can use them : in BI Server in BI Presentation Service in Delivers in a logical sql statement through a client connexion....



Share this page:
Follow us:
Task Runner