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:
An initialization block contains the SQL statement that is executed 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 has been defined when the initialization block is instantiated, this string is used. Otherwise, a default initialization SQL string is used.
Caution:
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 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 in the initialization block, 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.
This section contains the following topics:
he 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 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 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.
The queries and errors are logged to nqquery.log when using an Oracle BI EE 11g version.
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 duration of the session.
Execution of session variable initialization blocks during session logon can be deferred until their associated session variables are actually accessed within the session. See Creating Initialization Blocks for more information.
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.
In Oracle BI EE 12c, the queries and errors are logged in the obis1_query.log located in the DOMAIN_Home/servers/obis1/logs.
For more information about user-level logging, see Managing the Query Log in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
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, refer to Creating Initialization Blocks. For this example, you would provide the following SQL statement for the initialization string:
SELECT NAME, VALUE FROM RW_SESSION_VARS WHERE USERID='VALUEOF(NQ_SESSION.USERID)'
Note that 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.
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.
For 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')
Note:
The above information and example pertain to Logical SQL. If you are 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:
Select 'LIST_OF_CUSTOMERS', Customer_Name from RW_CUSTOMERS where RW.CUSTOMERS.USER_NAME in (VALUELISTOF(NQ_SESSION.LIST_OF_USERS))
To filter by only specific values in the list, then use ValueNameof as show in the below example. Note that 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))
Learn about initialization blocks in these topics.
See About Using Initialization Blocks with Variables for more information about initialization blocks.
To create initialization blocks, perform the steps in the following sections:
For repository variables, you can specify the day, date, and time for the start date, as well as a refresh interval.
To assign a name and schedule to initialization blocks:
The next step is to select 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.
It is 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 blacklisted and subsequent initialization blocks for that connection pool are skipped. See About Connection Pools for Initialization Blocks for more information.
If you select Database as the data source type:
If you select Database as the data source type, and do not select the Use OBI EE Server option
The SQL statement used to refresh the variable must reference physical tables that can be accessed through the connection pool specified in the Connection Pool field. The tables do not have to be included in the Physical layer of the metadata. At run time, if an initialization string for the database type has been defined, this string is used. Otherwise, the default initialization SQL for the database type is used. You can overtype this 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. See Testing Initialization Blocks for more information.
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 will still work 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 will work with other data sources supported by the Oracle BI Server, for example, ADF, SQL Server, Oracle, and XML files. When you select the Use OBI EE Server option, there is 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. See Testing Initialization Blocks for more information.
To select a data source and connection pool for initialization blocks:
In the Administration Tool, select Manage, then select Variables.
In the Variable Manager dialog, double-click the initialization block you want to edit. You can edit Repository initialization blocks, or Session initialization blocks.
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. See Managing Session Variables for more information.
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:
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 do not select a connection pool before typing the initialization string, you receive a message prompting you to select the connection pool.
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 is not available for selection. This behavior ensures that you cannot 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 is not recommended. See Setting Administration Tool Options for more information.
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. See Examples of Initialization Strings for examples.
If you are 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. See Initialization Strings Used in Variables to Override Selection Steps for more information.
(Optional) Click Test to test the data source connectivity for the SQL statement.
Click OK to return to the Initialization Block dialog.
Select Use OBI EE Server, and then perform the following steps:
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 is supported by the Oracle BI Server, the string will work with different data sources.
For example, an initialization block might use the function pi(), which is specific to SQL Server. However, if you select Use OBI EE Server, the query is rewritten by the Oracle BI Server for the appropriate database. In other words, if you change the SQL Server back-end database to Oracle, the query will still work.
See Examples of Initialization Strings for additional examples.
Click OK to return to the Initialization Block dialog.
If you selected LDAP Server for your data source type, perform the following steps:
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.
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:
Click Browse to select an existing custom authenticator, or click New to create one.
Click OK to return to the Initialization Block dialog.
Click OK.
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
]
}
]
}
These examples show you how to initialize strings.
This section contains the following initialization string examples:
Example 19-1 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' (note the colon and single quotes) is the ID the user types when logging in.
':PASSWORD' (note 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 do not need to set up the PASSWORD variable, and you can use this variable in a database connection pool to allow passthrough 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.
For more information, see System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
Example 19-2 A SQL Statement When Site Does Not 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.
':PASSWORD' (note the colon and the 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 do not need to set up the PASSWORD variable, and you can use this variable in a database connection pool to allow passthrough login using the user ID and password of the user. You can also use this variable in a SQL statement.
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.
Example 19-3 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, SQLServer, Oracle and XML Files). The query works because when you select the Use OBI EE Server option, the query is rewritten by the BI Server for the specified data sources.
You should test the SQL statement using the Test button or a SQL tool such as the Oracle BI EE 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 do not 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.
To test initialization blocks (optional):
The next step is to associate variables with the initialization block.
The SQL SELECT statement in the Default initializer list can contain multiple columns.
The order of the columns in the SQL statement and order of the variables associated with the initialization block determine the column value that is assigned to each variable. Therefore, when you associate variables with an initialization block, the value returned in the first column is assigned to the first variable in the list.
For repository variable initialization blocks, 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 can be different from the number of columns being retrieved. If there are fewer variables than columns, extra column values are ignored. If there are more variables than columns, the additional variables are not refreshed (they retain their original values, whatever they may be). Any legal SQL can be executed using an initialization block, including SQL that writes to the database or alters database structures, assuming the database permits the user ID associated with the connection pool to perform these actions.
If you stop and restart the Oracle BI Server, the server automatically executes 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 initializationoption. 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 for more information.
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 for more information.
To associate variables with initialization blocks:
The next step is to establish execution precedence.
When a repository has multiple initialization blocks, you can set the order (establish the precedence) in which the blocks will be initialized.
First, you open the block that you want to be executed last and then add the initialization blocks that you want to be executed before the block you have open. For example, suppose a repository has two initialization blocks, A and B. You open initialization block B, and then specify that block A will execute before block B. This causes block A to execute according to block B's schedule, in addition to its own.
To establish execution precedence:
Note:
When you select the Use OBI EE Server option for an initialization block:
Execution precedence does not apply, because during user login, an initialization block with the Use OBI EE Server option selected is executed 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 executed after authentication.
Execution of session variable initialization blocks cannot be deferred in some circumstances.
When the execution of session variable initialization blocks cannot be deferred, a message is displayed that explains why.
See Assigning a Name and Schedule to Initialization Blocks for more information.
The following list summarizes the scenarios in which execution of session variable initialization blocks cannot be deferred:
The Row-wise initialization option is selected in the Session Variable Initialization Block Variable Target dialog and the variables have not 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 is not 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 does not 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.
You can use the Variable Manager in the Oracle BI Administration Tool to enable and disable initialization blocks.
To enable or disable an initialization block: