OBIEE - Where can I use a BI Server variable (session/repository) ?

Bi Server Architecture With Client

About

When you have create a server variable, you have to reference it.

You can use them :

For the syntax, add NQ_SESSION if the variable is a session variable and see the relevant section for the function call.

In BI Server

You can modify the value of the session variable with a dashboard prompt. This article gives an How-to : OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?

Syntax

For a session variable:

VALUEOF("NQ_SESSION.Variable Name")

For a repository variable:

VALUEOF("Variable Name")

Repository Objects

You cannot use variables to represent columns or other repository objects.

Connection Pool

OBIEE - Connection Pool: The picture below show a repository variable call but you can use in the same way the session variable syntax.

Obiee Session Variable In Connection Pool

You can find an example of a dynamic essbase connection pool in this post of christian berg:

Connection script

OBIEE - Connection Script

SET SESSION 'APPLICATIONUSER' = 'VALUEOF(NQ_SESSION.USER)'

Obiee Connection Script Value Of

Opaque View / Select Statement

In the bookshel Version 10.1.3.2, it's written that Only repository variables can be used in the definition. An error will generate if a session variable is used in the opaque view.

Obiee Physical Table Select Session Variable

With the version 10.1.3.4, a warning is generated:

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

But BI Server process it without problem.

-------------------- SQL Request:
SET VARIABLE QUERY_SRC_CD='Report',SAW_DASHBOARD='/users/administrator/_portal',SAW_DASHBOARD_PG='MyYear',
SAW_SRC_PATH='/users/administrator/MyYear/Answers',MYYEAR='2001';SELECT TIMES_VIEW.calendar_year saw_0 FROM Test 
ORDER BY saw_0


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

-------------------- General Query Info:
Repository: Star, Subject Area: Test, Presentation: Test


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

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

RqList  distinct 
    TIMES_VIEW.calendar_year as c1 GB
OrderBy: c1 asc


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

-------------------- Execution plan:

RqList <<150282>> [for database 3023:156:orcl SH,31] distinct 
    TIMES_VIEW.calendar_year as c1 GB [for database 3023:156,31]
Child Nodes (RqJoinSpec): <<150293>> [for database 3023:156:orcl SH,31]
    (select time_id, calendar_year 
from times
where
calendar_year > VALUEOF(NQ_SESSION.MYYEAR)) as T6563
OrderBy: c1 asc [for database 3023:156,31]


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

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

select distinct T6563.calendar_year as c1
from 
     (select time_id, calendar_year 
from times
where
calendar_year > 2001) T6563
order by c1

in the Name of a Catalog or Schema

You can use a variable to specify the names of catalog and schema objects. For example, you have data for multiple clients and you structured the database so that data for each client was in a separate catalog. You would initialize a session variable named Client, for example, that could be used to set the name for the catalog object dynamically when a user signs on to the Oracle BI Server.

The Dynamic Name tab is not active unless at least one session variable is defined.

In Presentation Service

Logical SQL in Answers

OBIEE - Logical Sql

VALUEOF(NQ_SESSION."Variable Name")

In Delivers

@{NQ_SESSION.Variable Name}

Other places

You can call a server variable by using this syntax :

@{biServer.variables['VariableName']}
  • The S of Server must be in uppercase. biServer is good where biserver is not good.
  • variables is plural. variables is good where variable is bad
  • Don't forget the quotes which enclose the Variable Name.

Example in a dashboard text object:

Obiee Server Variable In Dashboard Text

Documentation / Reference





Discover More
Obiee Variable Manager
OBIEE - BI Server Variables (session and repository)

The OBI Server can handle two types of variable : repository variable: constant or refreshed periodically session variable: depend of the user and are then set up during the login process. system...
Obiee Repository Variable Date Type
OBIEE - How to set and use a server variable (system/repository) of type date ?

This article is a tutorial on how to set a date in a server variable (session variable of repository variable) and use it. This article use the schema SH : SH repository installation in the database and...



Share this page:
Follow us:
Task Runner