Bookshelf Home | Contents | Index | PDF     

Siebel Business Analytics Server Administration Guide > Using Variables in the Analytics Repository > Using Initialization Blocks >

Creating and Editing Initialization Blocks

When you create SQL and submit it directly to the database, bypassing Analytics Server (for example when creating initialization blocks), you should test the SQL using the Test button. If the SQL contains an error, the database returns an error message.

It is recommended to create a dedicated connection pool for initialization blocks. For more information, see Creating or Changing Connection Pools.

Use the instructions in this section to create a new initialization block or to edit properties of an existing initialization block. Table 44 contains descriptions of some of the elements in the Initialization Block dialog box. For more information about initialization blocks, see Using Initialization Blocks.

To create or edit an initialization block

  1. From the Administration Tool menu bar, select Manage > Variables.
  2. In the Variable Manager dialog box, from the Action menu, choose New > Initialization Block.
  3. In the Initialization Block dialog box, complete the fields using Table 44 as a guide.
  4. In the General tab, type a name for the block. (The NQ_SYSTEM initialization block name is reserved.)
  5. From the drop-down list, select one of the following values:
    • associate with repository variable
    • associate with session variable

      NOTE:  If you select associate with session variable, you can use the row-wise initialization feature to dynamically create session variables and set their values when a session begins. For more information, see Row-Wise Initialization.

  6. To use row-wise initialization, select the Row-wise initialization check box.

    The Cache Variables check box is automatically selected.

  7. Specify a refresh interval in the Refresh time area (repository variables and database data sources only).
    1. From the Start on drop-down list, select a date and time.
    2. In the Refresh interval field, type the periodic duration of the refresh interval.
    3. From the Refresh interval drop-down list, select days, hours, or minutes.
  8. In the Data Source Connection drop-down list, select Database or select LDAP if you are authenticating LDAP users.
  9. 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.
    2. In the Browse dialog box, select the connection pool and click OK.

      NOTE:  Select a connection pool before typing an initialization string.

    3. For initialization strings that are database-specific, in the Initialization string drop-down list, select the type of database.
    4. In the Initialization string text box, type the SQL initialization string needed to populate the variables.

      CAUTION:  If you have not selected a connection pool before typing the initialization string, you will receive a message prompting you to select the connection pool.

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

  11. (Optional) Click Test in the Data Source Connection area.
  12. In the Set value for the variables dialog box, verify the information is correct, and then click OK.
  13. 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.

    NOTE:  You should test the SQL using the Test button or an SQL tool such as Oracle's Siebel Analytics 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.

Table 44. Initialization Block Dialog Box Description
Field
Description

association drop-down list

An initialization block can associated with repository or session variables for database data source connections but can be associated with only session variables for LDAP data source connections.

Enable (check box)

When selected, enables the initialization block.

In the Variables Manager, when you right-click an existing initialization block, the menu contains a Disable or Enable toggle value, allowing you to change this property without having to open the Initialization Block dialog box.

Row-wise initialization (check box)

Can be selected for session variables only. For more information, see Row-Wise Initialization.

Refresh time

Start on

Refresh interval

The Refresh time area only applies to repository variables. It is unavailable for session variables.

You can specify the day, the month, the day of the month, the year, and the hours, minutes and seconds.

Data Source Connection

Choose Database or LDAP.

If you select Database as the data source connection, the values returned by the database in the columns in your SQL will be assigned to variables. The order of the variables and the order of the columns will determine which columns are assigned to which variables.

Test (button)

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.

Initialization string:

(drop-down list)

If you use an initialization string that is database-specific, you would select the type of database. Otherwise, use Default.

Initialization string:

(SQL text box)

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.

If you select Database in the Data Source Connection area, 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.

The order of the variables and the order of the columns determine which columns are assigned to each variable.

For examples of SQL initialization strings, see Examples of Initialization String SQL Statements.

Cache variables (check box)

The Cache variables check box is automatically selected when you select the Row-wise initialization check box. Selecting the cash variables option directs the Analytics Server to store the results of the query in a main memory cache.

The Analytics Server uses the cached results for subsequent sessions. This can reduce, often significantly, 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 cleat this check box.

Connection pool

Used for Database data source connections.

Examples of Initialization String SQL Statements

The examples in this section are 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 Analytics Server substituting valid values for the USER and PASSWORD variables and removing the NQS_PASSWORD_CLAUSE clause.

For more information, see About 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 Analytics Server, substituting valid values for the USER and PASSWORD variables.

  
Siebel Business Analytics Server Administration Guide