INTRODUCTION

Oracle enables you to track and store the Oracle BI server usage at the detailed level.

• It supports the accumulation of usage tracking statistics that can be used in variety of ways, such as:

  • Database performance Optimization

  • Aggregation Strategies

  • Billing users or departments based on the resources they consume

• Provides ability to analyze usage results with end-user reporting tools.

• You can analyze all the queries and have the reports of users and their usage queries

1. Tracking Methods

There are two methods for enabling usage tracking:

Any user has a record. In any environment if there is a column change, audit table can be referred to find the change made by user and inserted in the table.

1. Direct Insertion (Recommended approach)

Oracle BI Server inserts statistics for every query directly into a relational database table.

Log file

Oracle BI Server inserts statistics for every query into a log file.

Example

Set up Oracle BI usage tracking to track and store usage statistics at the detailed query level.

Fig 1:

Using this table, the analysis can be done

2. To Create Usage Tracking Table

Use the provided SAACCT.<db>.sql script to create S_NQ_ACCT usage tracking table.

This table stores the usage tracking data when queries are run against Oracle BI Server.

The schema folder contains CREATE TABLE scripts for Oracle, DB2, SQL Server, and Teradata: <ORACLE_INSTANCE>\bifoundation\OracleBIServerComponent\coreapplication_obis1\schema

3. Import table and model it

After creating usage tracking table, we have to import that into the rpd and build a usage tracking business model using the columns in the S_NQ_ACCT table:

Build a usage tracking business model using the columns in the S_NO_ACCT table:

Fig 2:

4. Enable Usage Tracking in NQSConfig.ini

The NQSConfig.ini file should be modified to enable usage tracking.

Fig 3:

1. Enable Direct Insertion

Set the DIRECT_INSERT parameter to YES to specify that statistics are inserted directly into a database table.

Fig 4:

In the NQSConfig.ini file, the DIRECT_INSERT parameter specifies whether statistics are inserted directly into a database table or written to a local file.

• When DIRECT_INSERT is set to NO, data is written to a flat file.

• When DIRECT_INSERT is set to YES, data is inserted to a table and the other parameters in NQSConfig.ini file such as PHYSICAL_TABLE_NAME, CONNECTION_POOL, BUFFER_SIZE, BUFFER_TIME_LIMIT_SECONDS, NUM_INSERT_THREADS, and MAX_INSERTS_PER_TRANSACTION become valid.

Note: Direct Insertion into a database table is recommended. Therefore, the default value is YES.

2. Set Physical Table Parameter

Set the PHYSICAL_TABLE_NAME_PARAMETER to specify the table into which to insert records corresponding to the query statistics.

• The table name is fully qualified name as it appears in the Physical Layer of the Admin Tool.

Fig 5:

• In the NQSConfig.ini file, scroll to the “Parameters used for inserting data into a table” section and locate the PHYSICAL_TABLE_NAME parameter.

• To insert query statistics information into a table, you must provide the fully qualified name consisting of four parameters such as database name, catalog name, schema name, and table name.

• Each component is surrounded by double quotes (“) and separated by a period (.). The fully qualified physical table name must match a table name in the physical layer of the loaded repository.

• Two format options are provided for the PHYSICAL_TABLE_NAME parameter. One has three components and the other has four components. In the above figure, the fully qualified path to the usage tracking physical table consists of three components namely a database component (ABC Usage Tracking), a schema component (ABC Usage Tracking Schema), and a physical table (S_NQ_ACCT).

3. Set Connection Pool Parameter

In the NQSConfig.ini file, set the CONNECTION_POOL parameter to specify the connection pool to use for inserting records into the usage tracking table.

The fully-specified connection pool name has two parts: database object name and connection pool name. Each part is surrounded by double quotes (*) and separated by a period (.).

Fig 6:

In the figure above, the parameter is “ABC Usage Tracking”, ”ABC Usage Tracking Connection Pool”; The fully qualified connection pool name should match a connection pool name in the physical layer of the loaded repository.

For usage tracking inserts to succeed, the connection pool should be configured with a used ID that has write access to the back-end database.

4. Set Additional Parameters

BUFFER_SIZE specifies the amount of memory which is used to temporarily store insert statements. The buffer allows the insert statement to be issued to the usage tracking table independent of the query that produced the statistics to be inserted. When the buffer is full, subsequent query statistics are discarded until insert threads service the buffer entries.

Example: BUFFER_SIZE=10 MB;

BUFFER_TIME_LIMIT_SECONDS specifies the maximum amount of time that an insert statement remains in the buffer before it is issued to the usage tracking table. The time limit ensures that Oracle BI Server issues the insert statements in a timely manner even during periods of extended quiescence.

Example: BUFFER_TIME_LIMIT_SECONDS = 5;

NUM_INSERT_THREADS specifies the number of threads to remove insert statements from the buffer and issues them to the usage tracking table. The number of threads should not exceed the total number of threads assigned to the connection pool.

Example: NUM_INSERT_THREADS=5;

MAX_INSERTS_PER_TRANSACTION specifies the number of groups to group together as a single transaction when inserting into the usage tracking table. Increase in the number may slightly improve the performance, but it also increases the possibility of inserts being rejected due to deadlocks in the database.

Example: MAX_INSERTS_PER_TRANSACTION=1;

5. Results

Use the Usage Tracking subject area to build and run queries:

Fig 7:

6. Analyze Usage Tracking Data

• Long running queries typically ad hoc reporting should be focussed. End users may need training working with ad hoc queries since ad hoc queries don’t hit the cache for the first time.

• You may need to assign query blocking or restrictions on how long queries can run or how many records are returned using certain filters. The database requires additional indexes or tuning.

• The usage audits should be performed in order to ensure regulatory compliance and security.

• Determine whether a query should be used to seed the cache or be removed from the cache-seeding queries.

• Identify aggregation strategies. The users or departments must be billed based on the resources that they consume.

Selvi

Add comment