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

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

1 - 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.

Advertising

3 - 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) ?

3.1 - Syntax

For a session variable:

VALUEOF("NQ_SESSION.Variable Name")

For a repository variable:

VALUEOF("Variable Name")

3.2 - Repository Objects

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

3.3 - 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.

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

3.4 - Connection script

OBIEE - Connection Script

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

Advertising

3.5 - 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.

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

3.6 - 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.

4 - In Presentation Service

4.1 - Logical SQL in Answers

OBIEE - Logical Sql

VALUEOF(NQ_SESSION."Variable Name")
Advertising

4.2 - In Delivers

@{NQ_SESSION.Variable Name}

4.3 - 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:

5 - Documentation / Reference