Variables in the Oracle BI Repository

There are two classes of variables:

1. Repository variables (Server Variables): holds the same value across all sessions

2. Session variables: holds a value specific to each user session.

Caution:

Values in repository and session variables are not secure, because object permissions do not apply to variables. Anybody who knows or can guess the name of the variable can use it in an expression in Answers or in a Logical SQL query. Because of this, do not put sensitive data like passwords in session or repository variables.

Repository variables: 

A repository variable has a single value at any point in time, I.e., holds the same value across all sessions. Repository variables can be used instead of literals or constants.

There are two types of repository variables:

1.  Static Repository Variables

2. Dynamic Repository Variables.

Static  Repository Variables: 

Static repository variables holds constant values for all sessions in the server. This value persists, and does not change until an administrator decides to change it.

Assume that, you are using a value 'California' (head office of client) very often in formula of reports or in Expression Builder of BMM layer in repository. Then instead of using direct value, we can assign this constant value to a static variable and use this variable wherever required. That will be more understandable to users than direct values.

Lets create a static repository variable called HEAD_OFFICE.

1. In the Administration Tool, select Manage > Variables.

Select Static from left pane, then right click on the right pane of the Variables and select New Static variable.

In the Variable dialog, type a name(HEAD_OFFICE in our case) for the variable.

Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables. 

To add a Default initializer value, type the value in the Default initializer box, or click the Expression Builder button to use Expression Builder.

If you initialize a variable using a character string, enclose the string in single quotes ( ' ). Static repository variables must have default initializers that are constant values.

Type the value  'California' in the Default initializer box for our case.

3.      Click OK.

Now, to see all the static variables in the server, select Static under Variables in Variable manager, then you can see all the static variables in right pane.

Dynamic  Repository Variables: 

These variables can have values that vary over time . 

When defining a dynamic repository variable, you create an initialization block. You also set up a schedule that the Oracle BI Server will follow to execute the query and periodically refresh the value of the variable.

When the value of a dynamic repository variable changes, all cache entries associated with a business model that reference the value of that variable are purged automatically.

Dynamic repository variables are useful for defining the content of logical table sources. For example, we need to refer count of employee in some places, and then we can create a dynamic variable called COUNT_OF_EMP and schedule it to every 1 day (because a new employee may join on any day).

To create a repository variable:

Lets create a Dynamic variable called  COUNT_OF_EMP, we have table called Emp. The query to get count of employees is : select count( distinct empkey) from EMP.

1. In the Administration Tool, select Manage, then select Variables.

2. In the Variable Manager dialog, select dynamic from left pane and right click on right pane, then  select New Dynamic Variable.

3. In the Variable dialog, type a name for the variable.

Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables.

4.  To create a new initialization block, click New.

5.      In Repository Init Block dialogue, set the initialization block name, schedule the refresh interval(in my case I have given 1 hour as refresh interval. So the BI server will send the query to database and get the data for every one hour). 

Notice that in our case, I have scheduled the variable refresh interval  to one hour. So the value of this variable will be refreshed for every hour by the BI Server.

6.      Next, click on the Edit Data Source, write the SQL Query, select the Connection Pool and click on OK.

7.      Next click on Edit Data Target, and define the Dynamic Variables and click on OK.

8.   Click OK.

Session Variables :
Session variables are similar to dynamic repository variables in that they obtain their values from initialization blocks. Unlike dynamic repository variables, however, the initialization of session variables is not scheduled. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.

Unlike a repository variable, there are as many instances of a session variable as there are active sessions on the Oracle BI Server. Each instance of a session variable could be initialized to a different value.

Session variables are primarily used when authenticating users against external sources such as database tables or LDAP servers. If a user is authenticated successfully, session variables can be used to set filters and permissions for that session.

Session Variables can be further classified as :
• System Session Variables
• Nonsystem Session Variables
System Session Variables
System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names that cannot be used for other kinds of variables (such as static or dynamic repository variables and nonsystem session variables).

When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable LOGLEVEL, set the filter to the variable NQ_SESSION.LOGLEVEL.

Variable	Description
USER	Holds the value the user enters as his or her logon name. This variable is typically populated from the LDAP profile of the user.
PROXY	Holds the name of the proxy user. A proxy user is a user that has been authorized to act for another user. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about the PROXY system session variable.
GROUP	Contains the groups to which the user belongs. Exists only for compatibility with previous releases. Legacy groups are mapped to  application roles automatically.
WEBGROUPS	Specifies the Catalog groups (Presentation Services groups) to which the user belongs, if any. Note that the recommended practice is to use application roles rather than Catalog groups.
USERGUID	Contains the global unique identifier (GUID) of the user, typically populated from the LDAP profile of the user.
ROLES	Contains the application roles to which the user belongs.
ROLEGUIDS	Contains the global unique identifiers (GUIDs) for the application roles to which the user belongs. GUIDs for application roles are the same as the application role names.
PERMISSIONS	Contains the permissions held by the user, such as oracle.bi.server.impersonateUser or oracle.bi.server.manageRepository.
DISPLAYNAME	Used for Oracle BI Presentation Services. It contains the name that  is displayed to the user in the greeting in the Oracle BI Presentation  Services user interface. It is also saved as the author field for catalog objects. This variable is typically populated from the LDAP  profile of the user.
PORTALPATH	Used for Oracle BI Presentation Services. It identifies the default dashboard the user sees when logging in (the user can override this  preference after logged on).
LOGLEVEL	The value of LOGLEVEL (a number between 0 and 5) determines the logging level that the Oracle BI Server uses for user queries. This system session variable overrides a variable defined in the Users object in the Administration Tool. If the administrator user (defined upon install) has a Logging level defined as 4 and the session variable LOGLEVEL defined in the repository has a value of 0 (zero), the value of 0 applies.
REQUESTKEY	Used for Oracle BI Presentation Services. Any users with the same  nonblank request key share the same Oracle BI Presentation Services cache entries. This tells Oracle BI Presentation Services that these users have identical content filters and security in the Oracle BI Server. Sharing Oracle BI Presentation Services cache entries is a way to minimize unnecessary communication with the Oracle BI Server.
SKIN	Determines certain elements of the look and feel of the Oracle BI Presentation Services user interface. The user can alter some elements of the user interface by picking a style when logged on to Oracle BI Presentation Services. The SKIN variable points to an Oracle BI Presentation Services folder that contains the nonalterable elements (for example, figures such as GIF files). Such directories begin with sk_. For example, if a folder were called sk_companyx, the SKIN variable would be set to companyx.
DESCRIPTION	Contains a description of the user, typically populated from the LDAP profile of the user.
USERLOCALE	Contains the locale of the user, typically populated from the LDAP profile of the user.
DISABLE_CACHE_HIT	Used to enable or disable Oracle BI Server result cache hits. This variable has a possible value of 0 or 1.
DISABLE_CACHE_SEED	Used to enable or disable Oracle BI Server result cache seeding. This variable has a possible value of 0 or 1.
DISABLE_SUBREQUEST_CACHE	Used to enable or disable Oracle BI Server subrequest cache hits and seeding. This variable has a possible value of 0 or 1.
SELECT_PHYSICAL	Identifies the query as a SELECT_PHYSICAL query. See "Syntax and Usage Notes for SELECT_PHYSICAL" for more information.
DISABLE_PLAN_CACHE_HIT	Used to enable or disable Oracle BI Server plan cache hits. This variable has a possible value of 0 or 1.
DISABLE_PLAN_CACHE_SEED	Used to enable or disable Oracle BI Server plan cache seeding. This variable has a possible value of 0 or 1.
TIMEZONE	Contains the time zone of the user, typically populated from the LDAP profile of the user.

Nonsystem Session Variables

You use the same procedure to define nonsystem session variables as for system session variables.

A common use for nonsystem session variables is setting user filters. For example, you could define a nonsystem variable called SalesRegion that would be initialized to the name of the sales region of the user.

You could then set a security filter for all members of a group that would allow them to view only data pertinent to their region.

When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable SalesRegion, set the filter to the variable NQ_SESSION.SalesRegion.

Creating Session Variables

1. In the Administration Tool, select Manage, then select Variables.
2. In the Variable Manager dialog, select Action > New > Session > Variable.
3. In the Session Variable dialog, type a variable name.
   Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables.
4. For session variables, you can select the following options:
• Enable any user to set the value. Select this option to set session variables after the initialization block has populated the value (at user login) by calling the ODBC store procedure NQSSetSessionValue() or by using Request variable. For example, this option lets non-administrators to set this variable for sampling.
5. Use the Initialization Block list to select an initialization block that will be used to refresh the value on a continuing basis.
6. To add a Default Initializer value, type the value in the Default Initializer box, or click the Expression Builder button to use Expression Builder.
7. Click OK.

Syntax for using these variables :
Repository variables can be used in two ways : 
1. VALUEOF(variable name) 
2. biServer.variables['variable name']



Session variables can be used in two ways : 
1. VALUEOF(NQ_SESSION.variable name) 
2. biServer.variables['variable name']