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.
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.
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, see 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.
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.
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, execution of the initialization block is deferred until an associated session variable is accessed for the first time during the session.
This option prevents execution of all session variable initialization blocks during the session logon stage, giving a shorter logon time. Session variables that are not needed during the session do not have their initialization blocks executed. This saves the resources which would have been used to execute these unnecessary initialization blocks.
The deferred execution of an initialization block also triggers the execution of all unexecuted predecessor initialization blocks. All associated variables of the initialization block and its unexecuted predecessors are updated with the values returned from the deferred execution.
Note:
The Allow deferred execution option is unavailable in some circumstances, see When Execution of Session Variable Initialization Blocks Cannot Be Deferred.
Required for authentication. If you select this option, this initialization block must succeed for users to log in. In other words, users are denied access to Oracle Business Intelligence if the initialization block fails to execute. Failure to execute can occur if the wrong credentials have been defined in the initialization block, or if there is an error in the default initialization string.
Note that this 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.
NQ_SYSTEM initialization block name is reserved.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:
Initialization Strings Used in Variables to Override Selection Steps
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 accessed through the connection pool specified in the Connection Pool field. You do not 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 was not 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 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. .
In the Oracle BI 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:
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.
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 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.
(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 works 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. If you change the SQL Server back-end database to Oracle, the query works.
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.
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 do not 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 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' , 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 do not 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.
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.
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, SQL Server, 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.
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.
The next step is to associate variables with the initialization block.
The column order in the SQL statement and variable order associated with the initialization block determines the column value that is 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 are not refreshed. The variables retain their original values. You can execute 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 to has permissions 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 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.
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 becomes available.
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.
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.
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.