Work with Session Variables

Learn about session variables and how to create them.

This section provides information about working with session variables, and contains the following topics:

About Session Variables

Session variables obtain their values from initialization blocks.

Unlike dynamic repository variables, however, the initialization of session variables isn't 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. When using session variables to set up security, see Manage Session Variables.

Note: Oracle Analytics doesn’t support the variables :user and :password in data source connection credentials.

This section contains the following topics:

About System Session Variables

Oracle BI Server and Oracle BI Presentation Services use system session variables for specific purposes.

System session variables have reserved names that can't be used for other kinds of variables such as static or dynamic repository variables and non-system 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.

The table describes the available system session variables.

Variable	Description
USER	Holds the value the user enters. The `USER` value always matches the `PROXY` variable that's the act as value. When the user logs in to act as some other user, the value of the `USER` session variable matches the `USERID` the user is acting as.
USERGUID	Contains the global unique identifier (GUID) of the user, populated from the LDAP or other profile for the user.
GROUP	Contains the groups that the user belongs to. Legacy groups are mapped to application roles automatically. When a user belongs to multiple groups, include the group names in the same column, separated by semicolons, for example, GroupA;GroupB;GroupC. If you must use a semicolon as part of a group name, precede the semicolon with a backslash character (\).
ROLES	Contains the application roles that the user belongs to. When a user belongs to multiple roles, include the role names in the same column, separated by semicolons, for example, RoleA;RoleB;RoleC. If a semicolon must be included as part of a role name, precede the semicolon with a backslash character (\).
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.manageRepositories.
PROXY	Holds the name of the proxy user that is authorized to act for another user. See Managing Security for Oracle Analytics Server for more information about the PROXY system session variable.
DISPLAYNAME	Used for Oracle BI Server. It contains the name that's displayed to the user in the greeting in the Oracle BI Presentation Services user interface. It's also saved as the author field for catalog objects. `DISPLAYNAME` variable is populated from the LDAP or other user profile.
LOGLEVEL	The `LOGLEVEL` value is a number between 0 and 5. `LOGLEVEL` specifies the logging level that the Oracle BI Server uses for user queries. `LOGLEVEL` 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.
DESCRIPTION	Contains a description of the user as populated from the LDAP or other user profile.
USERLOCALE	Contains the locale of the user as populated from the LDAP or other user profile.
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.
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 as populated from the LDAP or other user profile.
WEBLANGUAGE	Used for Oracle BI Presentation Services. Holds the user interface display language. Users can select a language on the sign-in page for Oracle Analytics Server, or they can change the language setting on the Preferences tab of the My Account dialog after signing in.
AUTHINITBLOCKONLY	Determines if the initialization blocks required for authentication are run. This variable has a value of Yes. The value is case-insensitive.
PORTALPATH	Used for Oracle BI Server. It identifies the default dashboard the user sees when logging in, the user can override this preference after signing onto Oracle Analytics Server.
REQUESTKEY	Used for Oracle BI Presentation Services. Any users with the same nonblank request key share the same cache entries. This tells Oracle BI Presentation Services that these users have identical content filters and security. Sharing cache entries is a way to minimize unnecessary communication with the Oracle BI Presentation Services.
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. The `SKIN` variable points to a folder that contains the non-alterable 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.

About Nonsystem Session Variables

A common use for nonsystem session variables is setting user filters.

For example, you could define a nonsystem variable called SalesRegion that's initialized to the name of the user’s sales region.

You can set a security filter for all members of a group that allow the group to view only the data pertinent to their region.

When you use these variables for Oracle BI Server, 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".

Create Session Variables

Use these steps to create session variables.

Create unique names for all variables. The names of system session variables are reserved. You can't use system session variable names for other types of variables.

The Enable any user to set the value option lets non-administrators set the variable for sampling.

The NQSSetSessionValues() stored procedure isn't supported for use through the Issue SQL page in Oracle BI Presentation Services Administration. You must select the Enable any user to set the value option to set a value for the variable.

When Security Sensitive is selected, the Oracle BI Server looks at the parent database object of each column or table that's referenced in the logical request projection list. If the database object has the Virtual Private Database option selected, the Oracle BI Server matches a list of security-sensitive variables to each prospective cache hit. Cache hits would only occur on cache entries that included and matched all security-sensitive variables.

If you're creating a session variable to override a hierarchy column's selection steps, then you must choose an initialization block with its initialization string written in JSON syntax. See Initialization Strings Used in Variables to Override Selection Steps and Create Initialization Blocks.

See Set Up an Expression.

In the Administration Tool, select Manage, then select Variables.
In the Variable Manager dialog, from the Action menu, select New, select Session , and then select Variable.
In the Session Variable dialog, in Name, type a variable name.
Optional: Select Enable any user to set the value to set the session variable after the initialization block has populated the value, at user login, by calling the ODBC stored procedure NQSSetSessionValue().
Optional: Select Security Sensitive to identify the variable as sensitive to security when using a row-level database security strategy, such as a Virtual Private Database (VPD).
From the Initialization Block list, select an initialization block to use to refresh the value on a continuing basis or click New to create a new initialization block.
In Default Initializer, type the value, or click the Expression Builder button to use Expression Builder.
Click OK.