Initialization Blocks With Variables

Initialization blocks are used to initialize dynamic repository variables, system session variables, and nonsystem session variables. For example, the NQ_SYSTEM initialization block is used to refresh system session variables.

An initialization block contains the SQL that will be executed to initialize or refresh the variables associated with that block. The SQL must reference physical tables that can be accessed using the connection pool specified in the Connection Pool field in the Initialization Block dialog box.

If you want the query for an initialization block to have database-specific SQL, you can select a database type for that query. If a SQL initialization string for that database type has been defined when the initialization block is instantiated, this string will be used. Otherwise, a default initialization SQL string will be used.

CAUTION:  By default, when you open the Initialization Block dialog box for editing in online mode, the initialization block object is automatically checked out. While the initialization block is checked out, the Oracle BI Server may continue to refresh the value of dynamic variables refreshed by this initialization block, depending on the refresh intervals that are set. When you check the initialization block in, the value of the dynamic variables is reset to the values shown in the Default initializer. If you do not want this to occur, use the Undo Check Out option.

Initializing Dynamic Repository Variables

The values of dynamic repository variables are set by queries defined in the Initialization string field of the Initialization Block dialog box. 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. If you stop and restart the Oracle BI Server, the server automatically executes the SQL in repository variable initialization blocks, reinitializing the repository variables.

The Oracle BI Server logs all SQL queries issued to retrieve repository variable information in the NQQuery.log file when the Oracle BI Administrator logging level is set to 2 or higher. You should set the logging level to 2 for the Oracle BI Administrator user ID to provide the most useful level of information. The default location for the NQQuery.log file is the Log folder in the Oracle BI Server software installation folder (\OracleBI). For more information about user-level logging, refer to Administering the Query Log.

Initializing Session Variables

As with dynamic repository variables, session variables obtain their values from initialization blocks. Unlike dynamic repository variables, session variables are not updated at scheduled time intervals. Instead, the Oracle BI Server creates new instances of those variables whenever a user begins a new session. The values remain unchanged for the session's duration.

The Oracle BI Server logs all SQL queries issued to retrieve session variable information if Logging level is set to 2 or higher in the Security Manager User object or the LOGLEVEL system session variable is set to 2 or higher in the Variable Manager.

The default location for the NQQuery.log file is the Log folder in the Oracle BI Server software installation folder (\OracleBI). For more information about user-level logging, refer to Administering the Query Log.

Row-Wise Initialization

The row-wise initialization option allows you to create session variables dynamically and set their values when a session begins. The names and values of the session variables reside in an external database that you access through a connection pool. The variables receive their values from the initialization string that you type in the Initialization Block dialog box.

For example, you want to create session variables using values contained in a table named RW_SESSION_VARS. The table contains three columns: USERID, containing values that represent users' unique identifiers; NAME, containing values that represent session variable names; and VALUE, containing values that represent session variable values.

The content of the table is as follows:

USERID	NAME	VALUE
JOHN	LEVEL	4
JOHN	STATUS	FULL-TIME
JANE	LEVEL	8
JANE	STATUS	FULL-TIME
JANE	GRADE	AAA

You create an initialization block and select the Row-wise initialization check box (refer to Process of Creating Initialization Blocks).

For the initialization string, you type the following SQL statement:

select NAME, VALUE
from RW_SESSION_VARS
where USERID='VALUEOF(NQ_SESSION.USERID)'

NQ_SESSION.USERID has already been initialized using another initialization block.

The following session variables are created:

• When John connects to the Oracle BI Server, his session will contain two session variables from row-wise initialization: LEVEL, containing the value 4; and STATUS, containing the value FULL_TIME.
• When Jane connects to the Oracle BI Server, her session will contain three session variables from row-wise initialization: LEVEL, containing the value 8; STATUS, containing the value FULL-TIME; and GRADE, containing the value AAA.

Initializing a Variable with a List of Values

You can also use the row-wise initialization option to initialize a variable with a list of values. You can then use the SQL IN operator to test for values in a specified list.

Example: Using the table values in the previous example, you would type the following SQL statement for the initialization string:

select 'LIST_OF_USERS', USERID
from RW_SESSION_VARS
where NAME='STATUS' and VALUE='FULL-TIME'

This SQL statement populates the variable LIST_OF_USERS with a list, separated by colons, of the values JOHN and JANE; for example, JOHN:JANE. You can then use this variable in a filter, as shown in the following WHERE clause:

where TABLE.USER_NAME = valueof(NQ_SESSION.LIST_OF_USERS)

The variable LIST_OF_USERS contains a list of values, that is, one or more values. This logical WHERE clause expands into a physical IN clause, as shown in the following statement:

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

About Authenticating Users Using Initialization Blocks

You can create a customized authentication module using initialization blocks. An authenticator is a DLL (or shared object on UNIX) written by a customer or developer that conforms to the Oracle BI Authenticator API Specification and can be used by Oracle BI Server to perform authentication and other tasks at run-time. The dynamically loadable authenticator framework (authentication module) is an Oracle BI Server module with a cache layer that uses the authenticator to perform authentication and related tasks at run-time.

Only one authenticator is allowed for each repository. The authentication for the user Administrator is always performed against the repository. Users in the repository are always authenticated against the repository.

Two sample authenticator plug-ins are installed when you install Oracle BI. One is only available for the Windows platform. The other one uses a text file for user information storage and is available to all platforms. We will provide a header file for all of the types that will be used in the dynamically loadable authenticator.

The Oracle BI Administrator asks a developer to implement a dynamically loadable authentication module according to the Oracle BI Authenticator API specification. For more information about this specification, refer to Oracle BI Server Authentication APIs.

After the Oracle BI Administrator creates an authentication object (authenticator plug-in) and specifies a set of parameters for the authentication module, such as configuration file path, number of cache entries, and cache expiration time. The Oracle BI Administrator then associates the authentication object with an initialization block. The Oracle BI Administrator associates the USER variable (required) and other variables with the initialization blocks.

When a user logs in, if the authentication is successful, Oracle BI Server populates a list of variables, as specified in the initialization block.