Siebel Business Analytics Server Administration Guide > Using Variables in the Analytics Repository >

Using Initialization Blocks

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

Table 43 lists some of the initialization blocks common to all Siebel Analytics applications and their purposes. Initialization blocks that are specific to each Siebel Analytics applications area are not listed here. For example, the LAST_SYND_DATES block sets certain dates for Pharmaceutical Siebel Business Analytics and can be ignored by other applications that do not use syndicated data.

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 Analytics Server will follow to execute the query and periodically refresh the value of the variable. If you stop and restart the Analytics Server, the server automatically executes the SQL in repository variable initialization blocks, reinitializing the repository variables.

The Analytics Server logs all SQL queries issued to retrieve repository variable information in the NQQuery.log file when the administrator logging level is set to 2 or higher. You should set the logging level to 2 for the 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 Analytics Server software installation folder (\Siebel Analytics). For more information about user-level logging, see 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 Analytics Server creates new instances of those variables whenever a user begins a new session. The values remain unchanged for the session's duration.

The Analytics 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 Analytics Server software installation folder (\Siebel Analytics). For more information about user-level logging, see Administering the Query Log.

Row-Wise Initialization

The row-wise initialization feature 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:

You create an initialization block and select the Row-wise initialization check box (see Creating and Editing 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 Analytics 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 Analytics 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 feature 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')