Work with 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.

This section contains the following topics:

About Using Initialization Blocks with Variables

An initialization block contains the SQL statement that's run to initialize or refresh the variables associated with that block.

The SQL statement must reference physical tables that can be accessed using the connection pool specified in the Connection Pool field in the Initialization Block dialog.

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 was defined when the initialization block is instantiated, the string is used, otherwise, a default initialization SQL string is used.

By default, when you open the Initialization Block dialog for editing in online mode, the initialization block object is automatically checked out. While the initialization block is checked out, the Oracle BI Server can continue to refresh the value of dynamic variables updated by this initialization block, depending on the refresh interval rate. When you check in the initialization block, the values of the dynamic variables are reset to the values shown in the Default initializer. If you don't want to reset the value, use the Undo Check Out option.

This section contains the following topics:

Initialize Dynamic Repository Variables

The values of dynamic repository variables are set by queries defined in the Default initialization string field of the Initialization Block dialog.

You can set up a schedule that the Oracle BI Server follows to run the query and periodically refresh the value of the variable. If you stop and restart the Oracle BI Server, the server automatically runs the SQL statements in repository variable initialization blocks, reinitializing the repository variables.

The Oracle BI Server logs all SQL queries issued to retrieve repository variable information in obis1_query.log located in the DOMAIN_Home/servers/obis1/logs when the logging level for the administrator account, set upon installation, is set to 2 or higher. You should set the logging level to 2 for the administrator to provide the most useful level of information.

Initialize Session Variables

As with dynamic repository variables, session variables obtain their values from initialization blocks. Unlike dynamic repository variables, session variables aren't 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 duration of the session.

Processing of session variable initialization blocks during session logon can be deferred until their associated session variables are actually accessed within the session, see Create Initialization Blocks.

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

Qeries and errors are stored in the obis1_query.log located in the DOMAIN_Home/servers/obis1/log directory.

See Manage the Query Log in Administering Oracle Analytics Server.

About Row-Wise Initialization

You can use the row-wise initialization option 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.

For example, suppose 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 the unique identifiers of the users
NAME, containing values that represent session variable names
VALUE, containing values that represent session variable values

The table shows the example.

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

To use row-wise initialization, create an initialization block and select the Row-wise initialization option, see Create Initialization Blocks. For this example, provide the following SQL statement for the initialization string:

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 would be created:

When John connects to the Oracle BI Server, his session contains 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 contains three session variables from row-wise initialization: LEVEL, containing the value 8; STATUS, containing the value FULL-TIME; and GRADE, containing the value AAA.

Initialize a Variable with a List of Values

You can 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.

This information and example pertain to Logical SQL. If you're using Physical SQL to initialize a variable with a list of values, then you must use the VALUELISTOF function. For example, to get the customers assigned to the user names in the variable LIST_OF_USERS, use the following statement:

For example, you 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')

Select 'LIST_OF_CUSTOMERS', Customer_Name from RW_CUSTOMERS where RW.CUSTOMERS.USER_NAME in (VALUELISTOF(NQ_SESSION.LIST_OF_USERS))

To filter by specific values in the list, use ValueNameof similar to the following example. The first value is 0, not 1.

Select 'LIST_OF_CUSTOMERS', Customer_Name from RW_CUSTOMERS where RW.CUSTOMERS.USER_NAME in '(ValueNameOf(0,NQ_SESSION.LIST_OF_USERS))

Create Initialization Blocks

Learn about initialization blocks in these topics.

To create initialization blocks, perform the steps in the following sections:

Create Session Variable Initialization Blocks

Use these steps to create a session variable initialization block.

When using Database as the data source type, you can use a default initialization string or a database-specific SQL statement, with a valid connection pool. You can test the connection before saving the changes.

When using LDAP Server as the data source type, select an existing LDAP Server or define a new server with the hostname, port number, password, and other LDAP specific configuration information.

When using a Custom Authenticator as the data source type, select an existing custom authenticator, or select a new authenticator and supply the required configuration properties.

In the Model Administration Tool, click the Manage menu, and select Variables.
In the Variable Manager, from the Action menu, select Session, and then select Initialization Block.
In the Session Variable Initialization Block, type a name for the initialization block.
Optional: Specify when the initialization block runs, select Disabled.
Click Edit Data Source, select a Data Source Type and complete the remaining fields specific to the selected data source type.
Click Edit the Target Variable, and edit or define the variable to use with the initialization block.
Click Edit the Execution Precedence, and Add or Remove initialization blocks that run before this initialization block.
Optional: Click Test to verify that your initialization block performs as expected.
Click OK.

Assign a Name and Schedule to Initialization Blocks

For repository variables, you can specify the day, date, and time for the start date, as well as a refresh interval.

The session initialization block options are:

Disabled. If you select this option, the initialization block is disabled.

You can also right-click an existing initialization block in the Variable Manager and choose Disable or Enable. This option enables you to change this property without opening the initialization block dialog.
Allow deferred execution. If you select this option, processing of the initialization block is deferred until an associated session variable is accessed for the first time during the session.

This option prevents processing of all session variable initialization blocks during the session logon stage, giving a shorter logon time. Session variables that aren't needed during the session don't have their initialization blocks run. This saves the resources which would've been used to run these unnecessary initialization blocks.

The deferred run of an initialization block also triggers the processing of all unprocessed predecessor initialization blocks. All associated variables of the initialization block and its unprocessed predecessors are updated with the values returned from the deferred execution.

The Allow deferred execution option is unavailable in some circumstances; see When Processing of Session Variable Initialization Blocks Can't Be Deferred.
Required for authentication. Oracle doesn't recommend that you use this setting because initialization blocks for authentication is a deprecated feature.

If you select this option, this initialization block must succeed for users to log in. Users are denied access to Oracle Analytics Server if the initialization block fails to run. Failure to run can occur if the wrong credentials have been defined in the initialization block, or if there's an error in the default initialization string.

The initialization block success requirement is waived for internal processes, like Delivers that use impersonation, if a single user session variable has been associated with the initialization block. In this case, the trusted internal process can connect regardless of whether the initialization block succeeds or fails.

In the Model Administration Tool, select Manage, then select Variables to assign a name and schedule to initialization blocks.
In the Variable Manager, expand Session or Respository, and then select Initialization Block.
In the [Repository|Session] Variable Initialization Block dialog, type a name for the block. The NQ_SYSTEM initialization block name is reserved.
(Repository initialization blocks only) In the Schedule area, select a start date and time and the refresh interval.
(Session init blocks only) Select an option.

The next step is to select the data source and connection pool.

Select and Test the Data Source and Connection Pool

If you select Database as the data source type for an initialization block, the values returned by the database for the columns in your SQL statement are assigned to variables that you associate with the initialization block.

For session variable initialization blocks, you can also select LDAP Server or Custom Authenticator.

It's recommended that you create a dedicated connection pool for initialization blocks where you select Database as the data source type. In addition, if an initialization block fails for a particular connection pool during Oracle BI Server start-up, no more initialization blocks using that connection pool are processed. Instead, the connection pool is blocklisted and subsequent initialization blocks for that connection pool are skipped.

See:

If you select Database as the data source type, and don't select the Use OBI EE Server option.

The SQL statement used to refresh the variable must reference physical tables accessed through the connection pool specified in the Connection Pool field. You don't have to include the tables in the Physical layer of the metadata. At run time, if an initialization string for the database type has been defined, the initialization string is used. If the initialization string for the database type wasn't defined, the default initialization SQL for the database type is used. You can overwrite the default string.

When you create SQL and submit it directly to the database, for example, when using database-specific SQL in initialization blocks, the SQL statement bypasses the Oracle BI Server. The order of the columns in the SQL statement and the order of the variables associated with the initialization block determine which columns are assigned to each variable.

You should test this SQL using the Test button in the [Repository|Session] Variable Initialization Block Data Source dialog. If the SQL statement contains an error, the database returns an error message.

If you select Database as the data source type, and select the Use OBI EE Server option.

The SQL statement you use to refresh the variable might be written for a specific database. However, it still works with other data sources because the SQL statement is processed by the Oracle BI Server. The Oracle BI Server can also provide functions such as PI that might not be available in the data source, and the SQL statement works with other data sources supported by the Oracle BI Server, for example, ADF, Oracle, and XML files. When you select the Use OBI EE Server option, there's no need for a connection pool, because the SQL statement is sent to the Oracle BI Server and not directly to the underlying database.

You can only test this SQL statement using the Test button in the [Repository|Session] Variable Initialization Block Data Source dialog when in online mode. If the SQL statement contains an error, the database returns an error message.

In the Model Administration Tool, select Manage, then select Variables.
In Variable Manager, select the initialization block to edit.
Click Edit Data Source next to the Connection Pool field.
From the Data Source Type list, select one of the following types.
- Database: For repository and session variables.
- LDAP Server: For session variables.
- Custom Authenticator: For session variables.
If you selected Database for your data source type, perform one of the following steps:
- Select Default initialization string or Use database specific SQL, and then perform the following steps:
  1. Click Browse next to the Connection Pool field to select the connection pool associated with the database where the target information is located. If you don't select a connection pool before typing the initialization string, you receive a message prompting you to select the connection pool.
  2. In the Select Connection Pool dialog, select the connection pool and click Select. You must select a connection pool before typing an initialization string.
    
    By default, the first connection pool under the database object in the Physical layer isn't available for selection. This behavior ensures that you can't use the same connection pool for initialization blocks that you use for queries.
    
    You can change this behavior so that the first connection pool is available for selection by selecting Allow first Connection Pool for Init Blocks in the Options dialog, although this isn't recommended.
  3. If you selected Use database specific SQL, then in the Database pane, expand and select the database. Then, enter its associated string.
    
    Otherwise, in the Default initialization string box, type the SQL initialization string needed to populate the variables.
    
    If you're editing an initialization block to be used by a variable to override a hierarchy column's selection steps, then in the Default initialization string box, type the JSON initialization string.
  4. (Optional) Click Test to test the data source connectivity for the SQL statement.
  5. Click OK to return to the Initialization Block dialog.
- Select Use OBI EE Server, and then perform the following steps:
  1. In the box, enter the SQL initialization string needed to populate the variables.
    
    The string you enter here is processed by the Oracle BI Server, and therefore as long as it's supported by the Oracle BI Server, the string works with different data sources.
  2. Click OK to return to the Initialization Block dialog.
If you selected LDAP Server for your data source type, perform the following steps:
1. Click Browse to select an existing LDAP Server, or click New to open the General tab of the LDAP Server dialog and create an LDAP Server.
2. Click OK to return to the Initialization Block dialog.
  
  The LDAP server name and the associated domain identifier appear in the Name and Domain identifier columns.
If you selected Custom Authenticator for your data source type, perform the following steps:
1. Click Browse to select an existing custom authenticator, or click New to create one.
2. Click OK to return to the Initialization Block dialog.
Click OK.

Initialization Strings Used in Variables to Override Selection Steps

For analyses that contain hierarchical columns, selection steps can be overridden with session variables or repository variables.

Session and repository variables intended for this purpose must contain valid JSON syntax, rather than SQL syntax, in their initialization strings.

Using JSON, you must define type, column, and members with the following syntax.

{
  "type": "Hierarchy",
  "column": {
    "subject_area":"your_subject_area",
    "hier_id":"your_hier_id",
    "dim_id":"your_dim_id",
    "table_name":"your_table_name"
  },
  "members": [
    {
      "level_id":"your_level_id",
      "values": [
        your_value,
        your_value
      ]
    },
    {
      "level_id":"your_level_id",
      "values": [
        your_value
      ]
    }
  ]
}

Where:

"type" indicates hierarchy type.

"column" indicates the hierarchy column's information such as subject area and table name.

"dim_id" is the logical dimension name.

"members" indicates which hierarchy level and which member ID.

"level_id" is the presentation level name.

Example of Standard Hierarchy Syntax

{
  "type": "Hierarchy",
  "column": {
    "subject_area": "A - Sample Sales",
    "hier_id": "H2 Offices",
    "dim_id": "H3 Offices",
    "table_name": "Offices"
  },
  "members": [
    {
      "level_id": "Company",
      "values": [
        10001,
        10002
      ]
    },
    {
      "level_id": "Organization",
      "values": [
        1005
      ]
    }
  ]
}

Example of Parent-Child Hierarchy Syntax

{
  "type":"Hierarchy",
  "column":{
    "subject_area":"A - Sample Sales",
    "hier_id":"Sales Rep Hierarchy",
    "dim_id":"H5 Sales Rep",
    "table_name":"Sales Person"
  },
  "members":[
    {
      "level_id":"Grand Total",
      "values":[
        27,
        24,
        18,
        16
      ]
    }
  ]
}

Examples of Initialization Strings

These examples show you how to initialize strings.

A SQL Statement When Site Uses Delivers

SELECT username, groupname, dbname, schemaname FROM users
WHERE username=':USER' 
NQS_PASSWORD_CLAUSE(and pwd=':PASSWORD')NQS_PASSWORD_CLAUSE

This SQL contains two constraints in the WHERE clause:

':USER' , use the colon and single quotes, is the ID the user types when logging in.

':PASSWORD' , use the colon and single quotes, is the password the user enters. This is another system variable whose presence is always assumed when the USER system session variable is used. You don't need to set up the PASSWORD variable, and you can use this variable in a database connection pool to allow pass through login using the user ID and password of the user. You can also use this variable in a SQL statement.

When using external table authentication with Delivers, the portion of the SQL statement that makes up the :PASSWORD constraint must be embedded between NQS_PASSWORD_CLAUSE clauses.

The query returns data only if the user ID and password match values found in the specified table. You should test the SQL statement outside of the Oracle BI Server, substituting valid values for the USER and PASSWORD variables and removing the NQS_PASSWORD_CLAUSE clause.

A SQL Statement When Site Doesn't Use Delivers

SELECT username, groupname, dbname, schemaname FROM users
WHERE username=':USER' 
AND pwd=':PASSWORD'

This SQL statement contains two constraints in the WHERE clause:

':USER' (note the colon and the single quotes) is the ID the user types when logging in.

A SQL Statement Joining Tables From Multiple Data Sources - When Using the 'OBI EE Server' Setting

select WUSER.name, wuser_detail.email 
from "db-11g/orcl"."NAME"."WUSER', 
"sqlexpress"."master"."dbo"."wuser_detail" 
where username=:USER:

The above query example in the initialization block uses a join query with multiple tables from different data sources, for example, Oracle and XML Files. The query works because when you select the Use OBI EE Server option, the query is rewritten by the Oracle BI Server for the specified data sources.

Test Initialization Blocks

You should test the SQL statement using the Test button or a SQL tool such as the Oracle Analytics Server Client utility.

If you use a SQL tool, be sure to use the same DSN or one set up identically to the DSN in the specified connection pool.

In online mode, Initialization Block tests don't work with connection pools set to use :USER and :PASSWORD as the user name and password. In offline mode, the Set values for variables dialog is displayed so that you can populate :USER and :PASSWORD.

In the Model Administration Tool, select Manage, then select Variables.
In the Variable Manager dialog, double-click the initialization block.
In the [Repository|Session] Variable Initialization Block dialog, click Edit Data Source.
In the [Repository|Session] Variable Initialization Block Data Source dialog, click Test.

The Test button is disabled when the Use OBI EE Server option is selected in offline mode.
In the Set value for the variables dialog, verify the information is correct, and then click OK.
In the View Data from Table dialog, type the number of rows and the starting row for your query, and then click Query.

The Results dialog lists the variables and their values.

The next step is to associate variables with the initialization block.

Variable Order in Initialization Blocks

The column order in the SQL statement and variable order associated with the initialization block determines the column value that's assigned to each variable.

When you associate variables with an initialization block, the value returned in the first column is assigned to the first variable in the list.

When you open a repository in online mode, the value shown in the Default initialization string field of the Initialization Block dialog is the current value of that variable as known to the Oracle BI Server. The number of associated variables could differ from the number of columns that are retrieved. If there are fewer variables than columns, extra column values are ignored. If there are more variables than columns, the additional variables aren't refreshed. The variables retain their original values. You can run any legal SQL using an initialization block, including SQL that writes to the database or alters database structures, when user ID associated with the connection pool has permissions to perform these actions.

If you stop and restart the Oracle BI Server, the server automatically runs the SQL statement in the repository variable initialization blocks, re-initializing the repository variables.

For session variable initialization blocks, you can select Row-wise initialization. The Use caching option is automatically selected when you select the Row-wise initialization option. Selecting the Use caching option directs the Oracle BI Server to store the results of the query in a main memory cache. See About Row-Wise Initialization.

The Oracle BI Server uses the cached results for subsequent sessions. This can reduce session startup time. However, the cached results might not contain the most current session variable values. If every new session needs the most current set of session variables and their corresponding values, you should clear this option.

See About Using Initialization Blocks with Variables.

Associate Variables with Initialization Blocks

Use this procedure to associate repository or session variables with initialization blocks.

See:

For the Custom Authenticator data source type (session variables only), the variable USER is required.

If you select Row-wise initialization, the Use caching option is available. See About Row-Wise Initialization

In the Model Administration Tool, select Manage, then select Variables to associate variables with initialization block.
In the Variable Manager dialog, double-click the initialization block to edit the repository initialization blocks or session initialization blocks.
Click Edit Data Target.
In the Repository|Session Variable Initialization Block Variable Target dialog, do one of the following:
- Click New, and in the Variable dialog, create a new variable to associate with the initialization block.
- Click Link, in the Browse dialog, select the variable to associate the variable with the initialization block, and then and click OK.
- For session variable initialization blocks only, select Row-wise initialization.
To remove a variable from association with this block, select the variable and click Remove.
Click OK.

Establish Execution Precedence

When a repository has multiple initialization blocks, you can set the order (establish the precedence) in which the blocks are initialized.

First, you open the block that you want to be run last and then add the initialization blocks that you want to be run before the block you've open. For example, suppose a repository has two initialization blocks, A and B. You open initialization block B, and then specify that block A runs before block B. This causes block A to run according to block B's schedule, in addition to its own.

When you select the Use OBI EE Server option for an initialization block:

Execution precedence doesn't apply, because during user login, an initialization block with the Use OBI EE Server option selected is run after initialization blocks with the Use OBI EE Server option not selected.
The Required for authentication option is dimmed, because this type of initialization block is run after authentication.

In the Model Administration Tool, select Manage, then select Variables.
In the Variable Manager dialog, double-click the last initialization block that you want to be initialized.
In the [Repository|Session] Variable Initialization Block dialog, click Edit Execution Precedence.
In the [Repository|Session] Variable Initialization Block Execution Precedence dialog, click Add.

Add is only available if there are initialization blocks that haven't yet been selected.
In the Browse dialog, select the blocks that should be initialized before the block that you've open, and then click OK.
To remove a block, in the [Repository|Session] Variable Initialization Block Execution Precedence dialog, select the block you want to remove and click Remove.
Click OK.
If you want the initialization block to be required, in the [Repository|Session] Variable Initialization Block dialog, select the Required for authentication option.
Click OK.

When Processing of Session Variable Initialization Blocks Can't Be Deferred

Processing of session variable initialization blocks can't be deferred in some circumstances.

When the processing of session variable initialization blocks can't be deferred, a message is displayed that explains why.

See Assign a Name and Schedule to Initialization Blocks.

The following list summarizes the scenarios in which processing of session variable initialization blocks can't be deferred:

The Row-wise initialization option is selected in the Session Variable Initialization Block Variable Target dialog and the variables haven't been declared explicitly with default values.

Example message: "The execution of init block 'A_blk' cannot be deferred as it is using row-wise initialization."
The Required for authentication option is selected in the Session Variable Initialization Block dialog.

Example message: "The execution of init block 'A_blk' cannot be deferred as it is required for authentication."
The Data Source Type isn't Database.

Example message: "The execution of init block 'A_blk' cannot be deferred as it does not have a connection pool."
The initialization block is used by session variables named PROXY or USER.

Example message: "The execution of init block 'A_blk' cannot be deferred as it is used by session variable 'PROXY'."
The initialization block is used by session variables where the Security Sensitive option is selected in the Session Variable dialog.

Example message: "The execution of init block 'A_blk' cannot be deferred as it is used by session variable 'A' which is security sensitive."
The initialization block is a predecessor to another initialization block which doesn't have the Allow deferred execution option selected.

Example message:"One of the successors for init block 'A_blk' does not have "Allow deferred execution" flag set. Init block 'B_blk' does not have "Allowed deferred execution" flag set.

Enable and Disable Initialization Blocks

You can use the Variable Manager in Model Administration Tool to enable and disable initialization blocks.

In the Model Administration Tool, select Manage, then select Variables.
In the Variable Manager, select Initialization Blocks under Repository or Session.
In the right pane, right-click the initialization block you want to enable or disable.
Choose Enable or Disable from the right-click menu.
Close the Variable Manager and save the repository.