Oracle^® Business Intelligence Server Administration Guide > Using Variables in the Oracle BI Repository > Process of Creating Initialization Blocks >

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.