OBIEE-Usage Tracking

Introduction

Let us first understand that what is usage tracking data.Usage Tracking Data are nothing but the statistic collected as per the usability of the catalog objects in obiee.

This is the functionality of Oracle BI Server to collects usage tracking data for each query and writes statistics to a usage tracking log file or inserts them directly to a database table.

Statistics can be anything like who is running what query,how many times a query is fired against the Oracle BI Server,figuring out most used reports,statistic helpful in database optimization, aggregation strategies etc.This information can help in data warehouse optimization and optimization of the BI system as a whole.

How it works?

When we 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, the Oracle BI Server directly inserts the usage tracking data into a relational database table. Note that by default Usage Tracking is recorded in the xxx_BIPLATFORM schema that gets created by the Repository Creation Utility at the start of the OBIEE installation. You can place it somewhere else, but this blog assumes you haven’t.

A sample usage tracking implementation is provided with the Oracle Business Intelligence installation at:

ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obisn\sample\usagetracking

There are two methods for enabling usage tracking:

1. Direct Insertion [Oracle BI Server inserts statistics for every query directly into a relational database table]

2. Log File [Oracle BI Server inserts statistics for every query into a log file]

It is strongly recommended that you use direct insertion instead of writing to a log file.

 

Steps to enable usage tracking

1. Create an Usage Tracking table

2. Import the Usage Tracking table into the Physical layer of RPD.

3. Build the Usage Tracking Business Model.

4. Enable Usage Tracking by modifying the NQSConfig.ini file.

5. Enable Direct Insertion by modifying the DIRECT_INSERT = YES in NQSConfig.ini file.

6. Set the physical table parameter by modifying the PHYSICAL_TABLE_NAME to the value of "S_NQ_ACCT" in NQSConfig.ini file.

7. Set the connection pool parameter

8. Set the additional required parameter in NQSConfig.ini file.

1. Create an Usage Tracking table

S_NQ_ACCT is a database table that holds all data log of the usage tracking functionality.By default S_NQ_ACCT is created in the xxx_BIPLATFORM schema that gets created by the Repository Creation Utility at the start of the OBIEE installation.

But if you do not want to use the S_NQ_ACCT which is created in the xxx_BIPLATFORM schema then you have to create the S_NQ_ACCT table in your database schema and define the connection in the connection pool.

Create Time Series Tables and View (Optional).S_ETL_DAY and S_ETL_TIME_DAY time series table.

  

2. Import the Usage Tracking table into the Physical layer of RPD.

3. Build the Usage Tracking Business Model.

Build the BMM layer in the RPD using the columns of S_NQ_ACCT as shown below:-

 

4. Enable Usage tracking by modifying the NQSConfig.ini file.

 

The NQSConfig.ini by default with Installer comes in a location “…/Middleware/Oracle_BI1/…”.But once you install the product it copies the file from “…/Middleware/Oracle_BI1/…” to “/scratch/obiee117/Oracle/Middleware/instances/instance1/config/OracleBIServerComponent/coreapplication_obis1” and does the required changes as per your host details.

NQSConfig.ini

 

6. Set the physical table parameter by modifying the PHYSICAL_TABLE_NAME to the value of "S_NQ_ACCT" in NQSConfig.ini file.

Set the physical table name parameter where it is going to write the usage statistic.In default case it should be into the S_NQ_ACCT table.

 NQSConfig.ini

  

7. Set the connection pool parameter

The CONNECTION_POOL is an important parameter which will be used while BI Server tries to write the statistics or insert records into S_NQ_ACCT table.

You can find the connection pool information from the physical layer of RPD by opening the RPD in Admin tool.

NQSConfig.ini

8. Set the additional required parameter in NQSConfig.ini file.

·          BUFFER_SIZE. This parameter indicates how much memory the BI Server should allocate 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 should typically equal 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 Usage Marathon 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.

 

Configure your usage tracking setting through System MBean Browser

An MBean is a managed Java object, similar to a JavaBeans component, that follows the design patterns set forth in the JMX specification. An MBean can represent a device, an application, or any resource that needs to be managed. MBeans expose a management interface that consists of the following:

·          A set of readable or writable attributes, or both.

·          A set of invokable operations.

·          A self-description.

You can view the System MBean Browser for many entities, including an Oracle WebLogic Server domain, an Administration Server, a Managed Server, or an application. You can search for an MBean, filter the list of MBeans, and refresh the list of MBeans in the MBean navigation tree.

Enterprise Manager (em)

 

To Configure the Usage Tracking using Mbean Browser follow below steps :-

1.     Lock the domain so that other administrator cannot make configuration changes while you perform Usage Tracking steps.

 

emàApplication Defined Beansàoracle.biee.adminàBIDomain

Click the lock link and then click the “Invoke and Return” buttons when prompted.

2.     Go to em-->Application Defined Beans-->oracle.biee.admin-->BIDomain.BIInstance.ServerConfiguration

Select the BIDomain.BIInstance.ServerConfiguration MBean.Ensure that attribute tab is selected, and then you will see number of attribute names, descriptions and their corresponding values.

A.    Ensure that UsageTrackingCentrallyManaged is set to true.

B.    Please set the following parameter:-

 

3.     To commit your changes and release the lock on the domain, return to BIDomain MBean(where group =service) under oracle.biee.admin.Select the Operation Tab and click the topmost commit link.Once done Click Invoke and Return.

4.     To start using new configuration setting, you need to Restart Oracle BI Instances.