UsageTracking in OBIEE 12c

UsageTracking:

The Oracle BI Server supports the accumulation of usage tracking statistics that can be used in a variety of ways such as database optimization, aggregation strategies, or billing users or departments based on the resources that they consume. The BI Server tracks usage at the detailed query level.

When you enable usage tracking, statistics for every query are inserted into a database table or are written to a usage tracking log file. If you use direct insertion, then the BI Server directly inserts the usage tracking data into a relational database table. It is recommended that you use direct insertion to write statistics to a database table.

When the BI Server starts, it validates the column names in the metadata against the list of valid columns in the usage tracking table. The following events occur:

To set up the usage tracking statistics database:

If we have the OBIEE in place already then we have RCU schmeas ready and Installed in our database which has all the tables required for Usagetracking

The RCU-created table names for usage tracking are S_NQ_ACCT, S_NQ_DB_ACCT, and S_NQ_INITBLOCK.

 Setting up direct insertion usage tracking:

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";

3. Save and close the file.

4. Restart the Oracle BI Server.

Note:

A sample usage tracking repository model is provided with the Oracle Business Intelligence installation at: ORACLE_HOME/bi/bifoundation/samples/usage_tracking.bar.

This path applies to 12c versions, but does not apply to earlier versions.

To use the sample usage tracking repository, you modify the connection pool to point to your database, then merge the usage tracking repository with your existing repository.

Refer here: https://docs.oracle.com/middleware/1221/biee/BIESG/usage_track.htm#BIESG189

Cheers!

Bose