Initialization Blocks


It is recommended to create a dedicated connection pool for initialization blocks. For more information, refer to Creating or Changing Connection Pools.
For more information about initialization blocks, refer to About Using Initialization Blocks With Variables.
To create an initialization block, perform the following steps:
  1. Assigning a Name and Schedule to Initialization Blocks
  2. Selecting and Testing the Data Source and Connection Pool.
  3. Associating Variables With Initialization Blocks
  4. Establishing Execution Precedence

ssigning a Name and Schedule to Initialization Blocks

This task is a step in Process of Creating Initialization Blocks.
For repository variables, you can specify the day, date, and time for the start date and a refresh interval.
To assign a name and schedule to initialization blocks
  1. From the Administration Tool menu bar, select Manage > Variables.
  2. In the Variable Manager dialog box, from the Action menu, choose New > Repository (or Session) > Initialization Block.
  3. In the Variable Init Block dialog box, type a name for the block. (The NQ_SYSTEM initialization block name is reserved.)
  4. (Repository init blocks) In the Schedule area, select a start date and time and the refresh interval.
  5. (Session init blocks) Select the following check boxes when appropriate:
    • Disabled. When selected, disables the initialization block.
      NOTE:  In the Variables Manager, the right-click menu for an existing initialization block contains a Disable or Enable toggle value. This allows you to change this property without having to open the initialization block dialog box.
    • Required for authentication. Used when creating an initialization block for authenticating users.
The next step is to select the data source and connection pool.

Selecting and Testing the Data Source and Connection Pool

This task is a step in Process of Creating Initialization Blocks.
If you select Database as the data source type, the values returned by the database for the columns in your SQL statement will be assigned to variables that you associate with the initialization block. For session variable initialization blocks, you can select LDAP or Custom Authenticator.
If you select Database as the Data Source Type, the SQL 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 will be used. Otherwise, the default initialization SQL for the database type will be 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 bypasses Oracle BI Server. The order of the columns in the SQL statement and the order of the variables associated with the init block determine which columns are assigned to each variable.
NOTE:  You should test this SQL using the Test button in the Variable Init block Data Source dialog box. If the SQL contains an error, the database will return an error message.
This following example topics contain examples of initialization strings that might be used with Delivers.
Example of an 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 the single quotes) equals the ID the user types when logging in.
':PASSWORD' (again, 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's user ID and password. You can also use this variable in a SQL statement if you so desire.
When using external table authentication with Delivers, the portion of the SQL statement that makes up the :PASSWORD constraint needs to be embedded between NQS_PASSWORD_CLAUSE clauses.
The query will return 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, refer to About Oracle BI Delivers and Database Authentication.
Example of an 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 enters when the user logged in.
':PASSWORD' (again, 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's user ID and password. You can also use this variable in a SQL if you so desire.
The query will return 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.
To select a data source and connection pool for initialization blocks
  1. From the Administration Tool menu bar, select Manage > Variables.
  2. In the Variable Manager dialog box, double-click the variable.
  3. In the Variable Initialization Block dialog box, click Edit Data Source.
  4. In the Variable Initialization Block Data Source dialog box, from the Data Source Type drop-down list, select one of the following types.

    Data Source Type
    Description
    Database
    Repository and session variables.
    XML
    Repository and session variables.
    LDAP
    Session variables.
    Custom Authenticator
    Session variables. For more information, see About Authenticating Users Using Initialization Blocks.
  5. If you selected Database in the Data Source Connection drop-down list, perform the following steps:
    1. Select the connection pool associated with the database where the target information is located by clicking Browse.
      CAUTION:  If you do not select a connection pool before typing the initialization string, you will receive a message prompting you to select the connection pool.
    2. In the Browse dialog box, select the connection pool and click OK.
      NOTE:  Select a connection pool before typing an initialization string.
      (Optional) Select the Use Database Specific SQL check box and in the Database pane, expand and select the database and its associated string.
    3. In the Initialization string text box, type the SQL initialization string needed to populate the variables.
    4. (Optional) Click Test. Tests the data source connectivity for the SQL statement.
  6. If you selected XML in the Data Source Connection area, perform the following steps:
    1. Select the connection pool associated with the database where the target information is located by clicking Browse.
    2. In the Initialization string text box, type the SQL initialization string needed to populate the variables.
  7. If you selected LDAP in the Data Source Connection area, 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 box and create an LDAP Server.
    2. Click OK to return to the Initialization Block dialog box.
      The LDAP server name and the associated domain identifier appear in the Name and Domain identifier columns.
  8. If you selected Custom Authenticator in the Data Source Connection area, complete the fields using the following list as a guide.

    Field
    Description
    Authenticator plug-in
    Type or browse for the DLL authenticator file.
    Configuration parameters
    Can be used to specify a configuration file.
    Cache never expires
    When selected, cache never expires and has to be purged manually.
    Cache persistence time
    When selected, a text box and drop-down list become available, allowing you to type a number in the text box and select days, hours, minutes, or seconds as the time increment. The cache will automatically expire after this time passes.
    Number of cache entries
    Maximum number of cache entries.
  9. Click OK.

Testing the Initialization Block

You should test the SQL using the Test button or an SQL tool such as the Oracle BI Client utility. If you use an SQL tool, be sure to use the same DSN or one set up identically to the DSN in the specified connection pool.
In Online editing mode, Initialization Block tests will 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 box appears so that you can populate :USER and :PASSWORD.
To test the initialization block (optional)
  1. From the Administration Tool menu bar, select Manage > Variables.
  2. In the Variable Manager dialog box, double-click the last variable that you want to be initialized.
  3. In the Variable Initialization Block dialog box, click Test.
  4. In the Set value for the variables dialog box, verify the information is correct, and then click OK.
  5. In the View Data from Table dialog box, type the number of rows and the starting row for your Query, and then click Query.
    The Results dialog box lists the variables and their values.
The next step is to associate variables with the initialization block.

Associating Variables With Initialization Blocks

This task is a step in Process of Creating Initialization Blocks.
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 will be assigned to the first variable in the list.
For more information, see About Using Initialization Blocks With Variables.
Repository Variables
When you open a repository in online mode, the value shown in the Default initializer field of the Initialization Block dialog box is the current value of that variable as known to the Oracle BI Server.
NOTE:  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 in the repository variable initialization blocks, re-initializing the repository variables.
Session Variables
For session variable initialization blocks, you can select Row-wise initialization. The Cache variables check box is automatically selected when you select the Row-wise initialization check box. Selecting the cash variables option directs the Oracle BI Server to store the results of the query in a main memory cache. For more information, refer to Row-Wise Initialization.
The Oracle BI Server uses the cached results for subsequent sessions. This can reduce session startup time. However, the cached results may 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 clear this check box.
To associate variables with the initialization block
  1. From the Administration Tool menu bar, select Manage > Variables.
  2. In the Variable Manager dialog box, double-click the variable.
  3. In the Variable Initialization Block dialog box, click Edit Data Target.
  4. In the Variable Initialization Block Variable Target dialog box, you can select one of the following:
    • Variables. Associates variables with the initialization block.
    • Row-wise initialization. Used with session init blocks only. For more information, see Row-Wise Initialization.
      If you select Row-wise initialization, the Use caching check box becomes available.
  5. If you select the Variables option, perform one of the following steps:
    1. Click new, and in the Variable dialog box, create a new variable.
      NOTE:  For the Custom Authentication data source type (Session variables), the variable USER is required.
      For information about creating variables, see Using the Variable Manager.
    2. Click Link, to associate an existing variable with an initialization block.
      • In the Browse dialog box, select the variable to be refreshed by this initialization block, and then click OK.
  6. To reorder variables, select a variable, and then click Up or Down.
  7. To remove a variable from association with this block, select the variable, and then click the remove button.
  8. Click OK.
The next step is to establish execution precedence.

Establishing Execution Precedence

This task is a step in Process of Creating Initialization Blocks.
When a repository has more than one initialization block, 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
  1. From the Administration Tool menu bar, select Manage > Variables.
  2. In the Variable Manager dialog box, double-click the last variable that you want to be initialized.
  3. In the Variable Initialization Block dialog box, click Edit Execution Precedence.
  4. In the Variable Initialization Block Execution Precedence dialog box, click Add.
    NOTE:  Add is only available if unselected initialization blocks are available.
  5. In the Browse dialog box, select the blocks that should be initialized before the block that you have open, and then click OK.
    CAUTION:  Make sure you add the blocks in the order that you want them to be initialized.
  6. To remove a block, in the Variable Initialization Block Execution Precedence dialog box, select the block you want to remove, and then click Remove.
  7. Click OK.
  8. If you wish the initialization block to be required, in the Variable Initialization Block dialog box, select the Required for authentication check box.
  9. Click

  11.  


Labels:physical layer

1 comment:

Subscribe to: Post Comments (Atom)

Popular Posts