Setting Up Direct Insertion to Collect Information for Usage Tracking

Direct insertion is the recommended method for setting up usage tracking.

This section describes how to set up direct insertion, and contains the following topics:

• Setting Up the Usage Tracking Statistics Database

• Setting Direct Insertion Parameters

• Setting Optional Direct Insert Parameters

Setting Up the Usage Tracking Statistics Database

Before you can use direct insertion usage tracking, you must set up a database to store the usage tracking statistics.

You must run the Repository Creation Utility (RCU) on the target database to create the required statistics schemas.

Typically, you use the database you installed for use with Oracle Business Intelligence as the statistics database because this database already has the RCU-created schemas. The RCU-created table names for usage tracking are S_NQ_ACCT, S_NQ_DB_ACCT, and S_NQ_INITBLOCK. See Description of the Usage Tracking Data.

You also need to import the database into the Physical layer of the Oracle BI repository.

1. Run the Repository Creation Utility on an external database of your choice. You can skip this step if you choose to use the database you installed for use with Oracle Business Intelligence for usage tracking statistics, because this database has the RCU-created tables already.
2. Open the Administration Tool and import the database into the Physical layer. See Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
3. Save and close the repository.

Setting Direct Insertion Parameters

You can set specific parameters for direct insertion on any new installation.

To set up direct insertion for new (non-upgraded) installations, use a text editor.

To set up direct insertion usage tracking use the steps below.

1. On the Oracle BI Server computer, open the NQSConfig.INI file in a text editor. You can find this file at:

   BI_DOMAIN/config/fmwconfig/biconfig/OBIS

   Make a backup copy of the file before editing.

2. In the [USAGE_TRACKING] section, update the following parameters:

   • Set ENABLE to YES.

   • Set DIRECT_INSERT to YES.

   • Set PHYSICAL_TABLE_NAME to the name of the fully-qualified database table for collecting query statistic information, as it appears in the Physical layer of the Oracle BI repository. For example:

     PHYSICAL_TABLE_NAME = "My_DB"."DEV_BIPLATFORM"."S_NQ_ACCT";
     

   • Set CONNECTION_POOL to the name of the fully-qualified connection pool for the query statistics database, as it appears in the Physical layer of the Oracle BI repository. For example:

     CONNECTION_POOL = "My_DB"."Usage Connection Pool";
     

   • Set INIT_BLOCK_TABLE_NAME to the name of the fully-qualified database table for inserting records that correspond to the initialization block statistics, as it appears in the Physical layer of the Oracle BI repository. For example:

     INIT_BLOCK_TABLE_NAME = "My_DB"."DEV.BIPLATFORM"."S_NQ_INITBLOCK;
     

   • Set INIT_BLOCK_CONNECTION_POOL to the name of the fully-qualified connection pool for the table for inserting records that correspond to the initialization block statistics, as it appears in the Physical layer of the Oracle BI repository. For example:

     INIT_BLOCK_CONNECTION_POOL = "My_DB"."Usage Connection Pool";
     

   Note:

   For Usage Tracking insertions to succeed, the connection pool must be configured with a user ID that has write access to the back-end database. Also, it is recommended that the connectivity type supports international data.
3. Save and close the file.
4. Restart the Oracle BI Server.

Setting Optional Direct Insert Parameters

The Usage Tracking section of the NQSConfig.INI file has several parameters.

In addition to the setup parameters described previously, you can also update the following optional parameters in the Usage Tracking section of the NQSConfig.INI file:

• BUFFER_SIZE. This parameter indicates how much memory the BI Server allocates for buffering the insert statements. Such a buffer lets the BI Server submit multiple insert statements as part of a single transaction, improving Usage Tracking insert throughput. It also means that ordinary analyses do not have to wait on Usage Tracking insertions, which improves average query response time. You might want to adjust this value based on available memory and memory utilization on the server computer.

• BUFFER_TIME_LIMIT_SECONDS. This parameter indicates the maximum amount of time that an insert statement remains in the buffer before the Usage Tracking subsystem attempts to issue it. This time limit ensures that the BI Server issues the insert statements quickly, even during periods of extended quiescence.

• NUM_INSERT_THREADS. This parameter indicates the number of threads that remove insert statements from the buffer and issue them to the Usage Tracking database. Assuming separate connection pools for readers and inserters, the number of insert threads typically equals the Maximum Connections setting in the connection pool.

• MAX_INSERTS_PER_TRANSACTION. This parameter indicates the maximum number of insert statements that the Usage Tracking subsystem attempts to issue as part of a single transaction. The larger this number, the greater potential throughput for UsageMarathon Tracking inserts. However, a larger number also increases the likelihood of transactions failing due to deadlocks. A small value for BUFFER_TIME_LIMIT_SECONDS can limit the number of inserts per transaction.

See NQSConfig.INI File Configuration Settings.