Oracle^® Business Intelligence Server Administration Guide > Administering the Oracle BI Server Query Environment > Administering Usage Tracking >

Setting Up a Log File to Collect Information for Usage Tracking

This is an alternate method for setting up usage tracking. It is recommended that you use direct insertion to collect information for usage tracking. For more information, refer to Setting Up Direct Insertion to Collect Information for Usage Tracking.

This section contains the following topics:

• Selecting an Output Location
• File Naming Conventions
• Output File Format
• Performance Considerations

Selecting an Output Location

The parameter STORAGE_DIRECTORY in the Usage Tracking section of the NQSConfig.INI file determines the location of usage tracking log files. If usage tracking is enabled, but no storage folder is specified, the files are written in the Log folder in the software installation folder (\OracleBI).

Current files are periodically written to disk, and new files are created. The parameter CHECKPOINT_INTERVAL_MINUTES controls the frequency with which usage tracking data is flushed to disk, and the parameter FILE_ROLLOVER_INTERVAL_MINUTES controls the frequency with which the current usage tracking log file is closed and a new file created.

When usage tracking is enabled, every query is logged to a usage tracking log file. This may require a large amount of available storage. For example, assume an average of 300 bytes of data output for each query and 10 queries per second over an 8 hour day. This results in approximately 83 MB of usage tracking data written to storage per day. If this example is extended to a 24 x 7 operation, the result is approximately .25 GB of storage per day.

The Oracle BI Server has no limit on the size or quantity of usage tracking log files that can exist in the specified location. It is the responsibility of the user to make sure that sufficient space is available, and to remove or archive old usage tracking files.

NOTE:  Insufficient storage space may cause you to lose usage tracking data. If the Oracle BI Server encounters an error accessing a usage tracking output file, it immediately discontinues the collection of usage tracking statistics and issues an error message to the NQServer.log and, in Windows, to the Windows Event log. Even if additional storage space is made available, the collection of usage tracking statistics will not resume until the server is restarted.

File Naming Conventions

The file naming scheme for the usage tracking log files is NQAcct.yyyymmdd.hhmmss.log, where yyyy is the year, mm is the month, dd is the day, hh is the hour, mm is the minute, and ss is the second of the timestamp when the file was created. For example, if the server creates the usage tracking log file at 07:15:00 AM on February 12, 2003, the filename would be NQAcct.20030212.071500.log. After the specified rollover interval, this file is flushed to disk and closed and a new log file, with the current date and timestamp, is created.

Output File Format

The usage tracking log files are text files, in semicolon-delimited ( ; ) format.
(A semicolon is used as the column delimiter because the logical SQL text contains commas.) A line feed delimits the end of each row of data.

The schema is described in Table 24. For more information about the contents of each column, refer to Description of the Usage Tracking Data.

NOTE:  All data in the output file is in character format. The data in columns 12 through 18 are output as text representations of integer numbers. Therefore, they behave more like Varchar(10) columns than integers. For example, if the row count is one million rows, then 1000000 appears in the output file in column 13 (Row count). This constitutes seven bytes of data, even though the data represents a 4-byte internal integer value.

• In column 12, a Success indicator value of 0 signifies a successful query. All nonzero values indicate failure. The following failure indicators are currently defined:
  • 1 indicates timeout
  • 2 indicates row limit violation
  • 3 indicates unknown error

The subsequent columns are valid only if the Success indicator signifies a successful query (value is 0):

• The Start timestamp and End timestamp columns indicate the wall clock time when the logical query started and finished. Each value is 19 bytes of character data representing a SQL-92 timestamp. The format is yyyy-mm-dd-hh:mm:ss. The related columns, Start date and End date, contain just the date component from the respective timestamps (in the yyyy-mm-dd format). Finally, the related columns, Start hourMin and End hourMin, contain just the hour and minute components from the respective timestamps (in a char hh:mm format).

While there is no guaranteed unique key for the usage tracking data, a combination of User name, Node ID, Start timestamp and Query text will usually be sufficient.

For information about sample scripts to help you extract data from usage tracking log files and load it to appropriately formatted relational database tables, refer to Oracle BI Server Usage Tracking Data Descriptions and Using the Log File Method.

Performance Considerations

When usage tracking is enabled, the Oracle BI Server collects usage tracking data for every query. This data, however, is only written to disk at user-specified intervals, known as checkpoints. The default setting is to checkpoint every 5 minutes.

While this value can be modified in theNQSConfig.INI file (refer to Oracle Business Intelligence Infrastructure Installation and Configuration Guide), reducing the interval adds overhead and, if set low enough, could potentially impact server performance. Setting the value higher increases the amount of usage tracking data that could be lost in the unlikely event of an abnormal shutdown of the Oracle BI Server.

The Oracle BI Server periodically initiates usage tracking log file rollovers. A rollover consists of closing the current usage tracking log file and opening a newly created one for writing subsequent data. The frequency at which rollovers occur is called a rollover interval. The default rollover interval is 240 minutes (every 4 hours).

Usage tracking log files that are closed are available for analysis. Setting a lower rollover interval will make usage tracking log files available for analysis sooner, but at the cost of additional overhead.

If the checkpoint interval equals or exceeds the rollover interval, only the rollover occurs explicitly; the checkpoint only occurs implicitly when the old usage tracking log file is closed.