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

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

Articles Related

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

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")

In a physical table of type select

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

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

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
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
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at del.icio.us
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at Digg
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at Ask
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at Google
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at StumbleUpon
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at Technorati
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at Live Bookmarks
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at Yahoo! Myweb
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at Facebook
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at Yahoo! Bookmarks
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at Twitter
  • Bookmark "OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?" at myAOL
 
dat/obiee/set_request_variable_dashboard_prompt.txt · Last modified: 2010/12/09 09:28 by gerardnico