Variables in Oracle OBIEE 11g :-
There are basically 4 different types of variables in OBIEE 11g.
1. Session Variables
2. Repository Variables
3. Presentation Variables
4. Request Variables.
Session Variables:
1. As the name suggests, session variables are created during the creation of session
i.e., as soon as a user logs into the BI server. So, Every login has its own session
variable.
2. There are two types of session variables
1. System (which are defined by OBIEE and are reserved)
2. Non System
which are defined by developers.
3. Session Variables can be created only through Oracle BI Administration Tool.
Referencing session variable:
For displaying session variables, we should use
@{biServer.variables['NQ_SESSION.VariableName']} .
For using session variables in expression, we should use
VALUEOF(NQ_SESSION.“VariableName”).
System Session Variable
EXP :-
NQ_SESSION.LOGLEVEL
USER,USERGUID,GROUP,ROLES,PERMISSIONS,PROXY
Repository Variables:
1. A repository variable is a variable that has a single value at any point in time.
2. There are two types of repository variables
1. Static (which changes only if admin or developer changes it value).
2. Dynamic ( value is refreshed using a query).
3. Repository variables can be created only through Oracle BI Administration Tool.
Referencing repository variable:
For displaying repository variables, we should use @{biServer.variables.VariableName} or
@{biServer.variables['VariableName']}.
For using repository variables in expression, we should use VALUEOF(“VariableName”) for
static variable and for dynamic variable VALUEOF(“Dynamic Initialization Block
Name”.“VariableName”).
Variables should be used as arguments of the function
1. A presentation variable is a variable which can be created as a part of creation of
dashboard prompts. Dashboard prompts must be either Column Prompt or Variable
Prompt.
2. The value of presentation variable is set by the prompt for which it is created (upon
user selection).
Referencing presentation variable:
For displaying presentation variables, we should use either
1. @{variables.VariableName}[Format]{DefaultValue} or
1. Format and DefaultValue are optional
1. Scope should be used if you create variables with same name.
2. Scope can be analyses, dashboard etc.
3. Order of precedence is analyses, dashboard pages, dashboards.
For using presentation variables in expression, we should use @{“VariableName”}
{DefaultValue}. Default value is optional.
Request Variable:
1. Request Variable is used to overwrite the value of session variable and it happens only
during request initiation to the database from column prompt.
2. Can be created only during the creation of column prompt.
Referencing request variable:
Same as presentation variable.
There are basically 4 different types of variables in OBIEE 11g.
1. Session Variables
2. Repository Variables
3. Presentation Variables
4. Request Variables.
Session Variables:
1. As the name suggests, session variables are created during the creation of session
i.e., as soon as a user logs into the BI server. So, Every login has its own session
variable.
2. There are two types of session variables
1. System (which are defined by OBIEE and are reserved)
2. Non System
which are defined by developers.
3. Session Variables can be created only through Oracle BI Administration Tool.
Referencing session variable:
For displaying session variables, we should use
@{biServer.variables['NQ_SESSION.VariableName']} .
For using session variables in expression, we should use
VALUEOF(NQ_SESSION.“VariableName”).
System Session Variable
EXP :-
NQ_SESSION.LOGLEVEL
USER,USERGUID,GROUP,ROLES,PERMISSIONS,PROXY
Repository Variables:
1. A repository variable is a variable that has a single value at any point in time.
2. There are two types of repository variables
1. Static (which changes only if admin or developer changes it value).
2. Dynamic ( value is refreshed using a query).
3. Repository variables can be created only through Oracle BI Administration Tool.
Referencing repository variable:
For displaying repository variables, we should use @{biServer.variables.VariableName} or
@{biServer.variables['VariableName']}.
For using repository variables in expression, we should use VALUEOF(“VariableName”) for
static variable and for dynamic variable VALUEOF(“Dynamic Initialization Block
Name”.“VariableName”).
Variables should be used as arguments of the function
VALUEOF(). This happens automatically when you double-click the variables to paste them into the expression.VALUEOF("prime_end")
CASE WHEN "Hour" >= VALUEOF("prime_begin")AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END
Presentation Variable:1. A presentation variable is a variable which can be created as a part of creation of
dashboard prompts. Dashboard prompts must be either Column Prompt or Variable
Prompt.
2. The value of presentation variable is set by the prompt for which it is created (upon
user selection).
Referencing presentation variable:
For displaying presentation variables, we should use either
1. @{variables.VariableName}[Format]{DefaultValue} or
1. Format and DefaultValue are optional
1. Scope should be used if you create variables with same name.
2. Scope can be analyses, dashboard etc.
3. Order of precedence is analyses, dashboard pages, dashboards.
For using presentation variables in expression, we should use @{“VariableName”}
{DefaultValue}. Default value is optional.
Request Variable:
1. Request Variable is used to overwrite the value of session variable and it happens only
during request initiation to the database from column prompt.
2. Can be created only during the creation of column prompt.
Referencing request variable:
Same as presentation variable.
In an answer, in the advanced tab, you have the section “Advanced Sql Clauses”.
In the prefix field, you can add a “SET VARIABLE MYVARIABLE=MyValue;”.
Example with DISABLE_CACHE_HIT (to disable a cache hit):
SET VARIABLE DISABLE_CACHE_HIT=1;SELECT "Customer"."Name" FROM SH ...
3.2.1.2 - With a presentation variable
You can then use the value of a presentation variable to initialize a request variable.
Example of prefix with multiple presentation variables and request variables:
SET VARIABLE MyRequestVariable1='@{MyPresentationVariableName1}{DefaultValue}',
MyRequestVariable2='@{MyPresentationVariableName2}{DefaultValue}';
In a Dashboard prompt
SET VARIABLE DISABLE_CACHE_HIT=1, MY_SESSION_VARIABLE=MyValue; SELECT Calendar."Calendar Year" FROM SH
