OBIEE - (Row-wise|List of Values) server variable

Bi Server Architecture With Client

About

A server variable can be initialized to a list of values (vector) with the row-wise initialization option of an Init block.

Obiee Row Wise

As you see, the values can be cached then be careful with diagnostic.

How to

Populates it

SELECT
   MY_VARIABLE_NAME,
   MY_VALUE
FROM
   MY_TABLE

This SQL statement populates the variable MY_VARIABLE_NAME with a list of value of MY_VALUE, separated by colons.

Example MY_TABLE:

MY_VARIABLE_NAME MY_VALUE
GROUPTEST ADMIN
GROUPTEST XMLP_ADMIN

By clicking on the test button, you will get:

Obiee Row Wise Test

Use it in an Analytics (SQL)

In a filter, add the following:

MY_COLUMN IN VALUELISTOF(NQ_SESSION."MyRowWiserVariable")

Supported since 11g.

Use it in the repository

In a formula column

with a case when:

CASE  WHEN 'ADMIN' =  VALUEOF(NQ_SESSION."GROUPTEST") THEN 'Permitted' ELSE 'Not Permitted' END 

will be transformed in

CASE when 'ADMIN' in ('ADMIN', 'XMLP_ADMIN') then 'Permitted' else 'Not Permitted' end

In a predicate

Once populate, you can use the variable:

  • In a filter
where TABLE.MY_COLUMN = valueof(NQ_SESSION.MY_VARIABLE_NAME)
CASE WHEN TABLE.MY_COLUMN = valueof(NQ_SESSION.MY_VARIABLE_NAME) ELSE ... END

The logical equality clause expands into a physical IN clause such as:

where TABLE.USER_NAME in ('JOHN', 'JANE').

Support

Restricted to usage with equality comparisons

When you try to use a row-wise variable in a column formula, you will throw this error:

State: HY000. Code: 2057371920. 
[NQODBC] [SQL_STATE: HY000] 
[nQSError: 10058] A general error has occurred. 
[nQSError: 42040] The session variable, NQ_SESSION.MY_VARIABLE, is defined as Row-Wise Initialization.
It is restricted to usage with equality comparisons. (HY000)

To avoid it in 11g, you can use the function VALUELISTOF.

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...
Saw Object
OBIEE - Dashboard Prompt with data level security

Example of dashboard prompt with a data level security. session variablerow-wise Logical SQL Result : Logical SQL Result :
Obiee User Group Permission Filter
OBIEE - Data security (visibility of data)

This article is data security or authorization in OBIEE. Data-level security controls the visibility of data (content rendered in subject areas, dashboards, Oracle BI Answers, and so on) based on the...



Share this page:
Follow us:
Task Runner