Create and Configure Initialization Blocks

This topic describes what you need to know to understand, create, and configure initialization blocks.

Create an Initialization Block

Create an initialization block specifically for the type of variable that you want to create.

For a global or session variable, its initialization block contains the variable definition and the initialization query that supplies the variable with its value. For a static variable, its initialization block contains the variable definition, including the variable's default value. A static variable's initialization block doesn't contain an initialization query.

For information about how to define an initialization block for a specific type of variable, see Create a Global Variable, Create a Session Variable, or Create a Static Variable.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Variables.
Click Create and then click Create Initialization Block.
In Create Initialization Block, go to Name and enter an initialization block name.
Go to the Type field and select the type of variable that you want to create. Click OK.

Open an Initialization Block

Open an initialization block to view or update its configuration, and to view, update, or add variable definitions.

For information about how to add variable definitions to an initialization block, see Create a Global Variable, Create a Session Variable, or Create a Static Variable.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Variables.
In the Variables pane, browse or search for the initialization block that you want to open and double-click it.

Defer Session Variable Processing

To decrease logon time and save system resources, you can defer the processing of an initialization block containing many session variables.

Any new initialization blocks that you create are set to deferred execution by default. When you import a model created in Data Modeler or Model Administration Tool, the deferred execution property set in its variables are imported into Semantic Modeler.

If you defer the processing of a session variable initialization block, then any variable included in the initialization block is processed when it's accessed for the first time during the session rather than at logon time. And Oracle Analytics doesn't run initialization blocks that contain session variables that aren't used during the session.

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

A message is displayed when you can't defer the processing of a session variable initialization block. See When You Can't Defer Session Variable Processing.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Variables.
In the Variables pane, browse or search for the initialization block that you want to defer execution for and double-click it.
In the Initialization Block, click the General tab and then click Allow deferred execution.
Click Save.

When You Can't Defer Session Variable Processing

This topic explains when you can't defer session variable initialization block processing and the example messages that Semantic Modeler displays.

You can't defer variable initialization block processing when:

The Variable names and values option is selected in the Query Returns field 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 initialization block contains variables with the Security Sensitive option selected.

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

About Dynamically Creating Session Variables and Setting Their Values

In a session variable's initialization block, you can select the Variable names and values option in the Query Returns field to create session variables dynamically and set their values when the session begins.

The names and values of the session variables reside in an external data source that you access through a connection pool. The variables receive their values from the initialization query that you provide.

Example 1 - Initialization Query With a Single Value Variable

Suppose you want to create session variables using values contained in a table named RW_SESSION_VARS. This table contains three columns:

USERID - Contains values that represent the unique identifiers of the users.
NAME - Contains values that represent session variable names.
VALUE - Contains values that represent session variable values.

This table shows the example columns and their values.

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

To implement this example, create a session variable initialization block and select Variable names and values in the Query Returns field. Then in the Select Statement field, enter this initialization query:

SELECT NAME, VALUE
FROM RW_SESSION_VARS
WHERE USERID='VALUEOF(NQ_SESSION.USERID)'

NQ_SESSION.USERID is a system session variable that Oracle Analytics initializes for each user when they log on.

When initialized, this example creates the following session variables:

When John connects to Oracle Analytics, his session contains two session variables: LEVEL containing the value 4, and STATUS containing the value FULL_TIME.
When Jane connects to Oracle Analytics, her session contains three session variables: LEVEL containing the value 8, STATUS containing the value FULL-TIME, and GRADE containing the value AAA.

Example 2 - Initialization Query With a Multiple Value Variable

Suppose you want to create session variables using values contained in a table named RW_SESSION_VARS. This table contains three columns:

ROLE_NAME - Contains values that represent user roles.
NAME - Contains values that represent session variable names.
VALUE - Contains values that represent session variable values.

This table shows the example columns and their values.

ROLE_NAME	NAME	VALUE
Role1	LEVEL	4
Role1	STATUS	FULL-TIME
Role2	GRADE	AAA

SELECT NAME, VALUE
FROM RW_SESSION_VARS
WHERE ';' || 'valueof(NQ_SESSION.ROLES)' || ';' like '%;' || ROLE_NAME || ';%'

If a user is assigned Role1 and Role 2, then valueof(NQ_SESSION.ROLES) returns the value Role1;Role2.

NQ_SESSION.ROLES is a system session variable that Oracle Analytics initializes for each user when they log on.

When initialized, this example creates the following session variables:

When users assigned to Role1 connect to Oracle Analytics, their sessions contains two session variables: LEVEL containing the value 4, and STATUS containing the value FULL-TIME.
When users assigned to Role2 connect to Oracle Analytics, their sessions contain one session variable GRADE containing the value AAA.
When users assigned to Role 1 and Role2 connect to Oracle Analytics, their sessions contain three session variables: LEVEL containing the value 4, STATUS containing the value FULL-TIME, and GRADE containing the value AAA.

Use a List of Values to Initialize a Session Variable

You can configure a session variable's initialization block to initialize a session variable with a list of values.

To configure an initialization block to initialize a session variable with a list of values, in the initialization block configuration you must select Variable names and values in the Query Returns field.

When you select the Variable names and values field, the Query Returns field is displayed. Selecting the Cache Query result option puts the query's results in a main memory cache. The Oracle Analytics query engine 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.

The information and example in this topic pertain to Logical SQL. If you're using Physical SQL to initialize a variable with a list of values, then use the VALUELISTOF function.

For example, to get the customers assigned to the user names in the variable LIST_OF_USERS, use the following SQL statement in the initialization query:

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 colon-separated list 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 one or more values. The physical IN clause replaces the logical WHERE 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 a list, use ValueNameof. The first value is 0, not 1. For example:

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

Create a Schedule to Update Global Variable Values

You can schedule how often an initialization block's global variables' values are updated.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Variables.
In the Variables pane, browse or search for the initialization block that you want to schedule and double-click it.
In the Initialization Block, click the General tab.
Go to the Run every field and specify how frequently you want to refresh the initialization block's variable values.
Go to the Starting On field and select the date and time when you want the initialization block's refresh schedule to begin.
Click Save.

Add an Additional Database Query to an Initialization Block

An initialization block must have a default initialization query. You can specify additional initialization queries specific to the data sources that your company is using (for example, Oracle Snowflake or DB2).

When you define an additional initialization query and the corresponding variable is used in a data filter or expression, Oracle Analytics skips the initialization block's default query. The data source-specific query bypasses the Oracle Analytics query engine to instantiate the initialization block and populate the variables.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Variables.
In the Variables pane, browse or search for the initialization block that you want to add database query to and double-click it.
In the Initialization Block, click the Variables tab and then click Specify query for additional databases.
Click Add database-specific query and then select the database that you want to write a query for.
In the Select Statement: <your_database> field, write a select statement.
Click Save.

Initialization Queries Used in Variables to Override Selection Steps

For analyses that contain hierarchical columns, global variables or session variables can override selection steps.

Global and session variables intended for this purpose must use valid JSON syntax.

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 hierarchy 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
      ]
    }
  ]
}

Test an Initialization Block's Query

Test the initialization block's initialization query to confirm that the connection pool is working properly, the query returns the expected values, and the values are correctly assigned to the variables that you defined.

It is best practice to create and use a dedicated connection pool for initialization blocks. See About Connection Pools for Initialization Blocks.

If an initialization block fails because of a particular connection pool, then no more initialization blocks using that connection pool are processed. Instead, the connection pool is blocked and subsequent initialization blocks for that connection pool are skipped.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Variables.
In the Variables pane, browse or search for the initialization block that you want to test and double-click it.
In the Initialization Block, click the Variables tab and then click Test Query.

Change the Order of Variables in an Initialization Block

If you test an initialization block's query and the variables are populated with the wrong values, then you might need to change the order of the variables.

The initialization query's column order and the variable order specified in the initialization block determines the column value assigned to each variable. When you associate variables with an initialization block, the first column specified in the query is assigned to the first variable in the list. If the initialization query's column order doesn't match the variables' order, then the variables are populated with the wrong values.

The number of associated variables could differ from the number of columns 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 and the variables retain their original values.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Variables.
In the Variables pane, browse or search for the initialization block that you want to disable or enable and double-click it.
In the Initialization Block, click the Variables tab.
Go to the variables list and click to highlight the variable that you want to move. Click Move Up or Move Down.
Click Save.

Add Dependencies to an Initialization Block

When a semantic model has multiple initialization blocks, you can set the order that the blocks are initialized in.

If you don't set dependencies, then Oracle Analytics runs all initialization blocks at the same time. This results in null values because the variable values aren't returned in the necessary order.

To add dependencies, you first open the initialization 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 opened. For example, suppose a semantic model has two initialization blocks, A and B. You open initialization Block B, and then specify that Block A runs before Block B. If you're setting dependencies for session initialization blocks that include schedules, then Block A runs according to Block B's schedule in addition to its own schedule.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Variables.
In the Variables pane, browse or search for the initialization block that you want to add a dependency to.
In the Initialization Block, click the Dependencies tab.
Click Add Initialization Block and browse for and select an initialization block to add it to the Dependencies list.
Click Save.

Disable or Enable an Initialization Block

You can disable or enable any global or session initialization blocks. By default, initialization blocks are enabled.

You might disable or enable an initialization block for testing purposes.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Variables.
In the Variables pane, browse or search for the initialization block that you want to disable or enable and double-click it.
In the Initialization Block, click the General tab and then click Disable to disable the initialization block, or clear Disable to enable the initialization block.
Click Save.