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

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

1 - About

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

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

Advertising

3 - How to

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

3.2 - Use it in an Analytics (SQL)

In a filter, add the following:

MY_COLUMN IN VALUELISTOF(NQ_SESSION."MyRowWiserVariable")

Supported since 11g.

Advertising

3.3 - Use it in the repository

3.3.1 - 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

3.3.2 - 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').
Advertising

4 - Support

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

5 - Documentation / Reference