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

Creating and Editing Initialization Blocks

When you create SQL and submit it directly to the database (bypassing Siebel 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.

Use the instructions in this section to create a new initialization block or to edit properties of an existing initialization block.

To create or edit an initialization block

  1. From the Administration Tool menu bar, select Manage > Variables.
  2. Choose Action > New > Initialization Block.
  3. In the General tab, type a name for the block. (The NQ_SYSTEM initialization block name is reserved.)
  4. Select one of the following options:
    • associate with repository variable
    • associate with session variable
  5. If you selected associate with session variable in the preceding step, 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.
    1. Select the Row-Wise Initialization check box.
    2. Select the Cache Variables check box to direct the Siebel Analytics Server to store the results of the query in a main memory cache.

      If you select this option, the Siebel 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 should not choose this option.

    3. Type the SQL initialization string needed to populate the variables.
  6. Specify a refresh interval in the Refresh time area.
    1. From the Start on drop-down list, select a date and time.

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

    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.

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

  7. In the Data Source Connection area, select Database, or LDAP if you are authenticating LDAP users.

    TIP:  You should test the SQL using the Test button or an SQL tool such as the Siebel Analytics Client utility. Use the same DSN or one set up identically to the DSN in the specified connection pool.

  8. If you selected 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.
    1. To select the connection pool associated with the database where the target information is located, click Browse.
    2. In the Browse dialog box, select the connection pool and click OK.

      NOTE:  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. You can click the Test button to test the connection.

  9. If you selected Database in the Data Source Connection area, in the Initialization Block dialog box, type the initialization string to use.

    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.

    • For sites not using Siebel Delivers, a sample SQL statement follows:

    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 Siebel Analytics Server, substituting valid values for the USER and PASSWORD variables.

    • For sites that are using Siebel Delivers, a sample SQL statement follows:

    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 Siebel 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 Siebel Analytics Server substituting valid values for the USER and PASSWORD variables and removing the NQS_PASSWORD_CLAUSE clause.

    • For initialization strings that are database-specific, select the database type from the drop-down list above the Initialization String 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.

  10. If you selected LDAP in the Data Source Connection area, click Browse to select the LDAP Server this block will use, or click New... to open the General tab of the LDAP Server dialog box and create an LDAP Server. Click OK to return to the Initialization Block dialog box.
  11. (Optional) Click Test in the Data Source Connection area.

    The Results dialog box lists the variables and their values.


 Siebel Analytics Server Administration Guide
 Published: 11 March 2004