Usage Tracking In OBIEE 11g

Usage tracking is one-off the important feature in obiee .

We need to configure the usage tracking to track and monitor the usability of the catalog objects in obiee

 

Oracle Business Intelligence database created during the installation of OBIEE 11g which has RCU-created tables can be used for usage tracking storage.

NOTE – Make sure that XXX_BIPLATFORM should have S_NQ_ACCT (used to store logical query details) and S_NQ_DB_ACCT (used to store physical query details)

Setting Direct Insertion Parameters

For setting up direct insertion for new installations we have to use MBean Browser in Fusion Middleware Control (Enterprise Manager), it is different than what we used to do in OBIEE 10g.

1. Open Enterprise Manager – put in username/password to login  

 http://machine_name.101:7001/em

2. Choose bifoundation_domain which is a part of Web Logic Domain, right click and choose  System Mbean Browser  

3. Expand Application Defined MBeans > Expand oracle.biee.admin > Expand Domain:bifoundation_domain > Expand BIDomain and Select the BIDomain MBean where group=Service

4. Lock the domain

1. Expand BIDomain and select the BIDomain MBean where Group=Service
2. Display the Operations tab
3. Click the lock link , ‘invoke’ and return

5. Expand BIDomain.BIInstance.ServerConfiguration, and then select the BIDomain.BIInstance.ServerConfiguration MBean, Ensure that UsageTrackingCentrallyManaged attribute is set to true.

NOTE – The usage tracking attributes can be managed using NQSConfig.INI file rather than System MBean Browser. One need to set UsageTrackingCentrallyManaged attribute to false. I would recommend to use centrally managed option for ease and simplicity.

6. Set Usage Tracking Attributes as below   

NOTE – The UsageTrackingPhysicalTableName attribute 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: "My_DB"."DEV_BIPLATFORM"."S_NQ_ACCT"

The UsageTrackingConnectionPool attribute 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: "My_DB"."Usage Connection Pool"

  

7. Release Lock for domain and Commit 
1. Return to the BIDomain MBean where Group=Service
2. Display the Operations tab
3. Click on the commit operation

  

8. Go to Overview Page and click Restart to start Oracle Business Intelligence Services 

Setting Optional Parameters

The optional parameters in the Usage Tracking section of the NQSConfig.INI located at

$BI_INSTANCE/config/OracleBIServerComponent/coreapplication_obis1.

BUFFER_SIZE = 250 MB;

-       Allocated memory for buffering the insert statement , improves insert throughput and no wait for normal analyses

BUFFER_TIME_LIMIT_SECONDS = 5; 

-       Maximum amount of time that an insert statement remains in the buffer , ensure quick insert statements

NUM_INSERT_THREADS = 5;

-         Number of inserts treads should be equal to the maximum connections setting in the connection pool

MAX_INSERTS_PER_TRANSACTION = 1;

-       Maximum number of inserts per transaction, larger number will increase throughput but there is potential failure of statement due to deadlocks.

Repository Metadata Design

Physical Layer

Import the usage tracking tables into the Physical layer of the Oracle BI repository and then expose it to logical layer and presentation layer.

The physical data model and physical joins are as follows.

Logical Layer

Logical Dimension tables, logical tables and Logical Facts tables are build as below by dragging physical tables to Business Layer.

Business Model diagram for usage tracking – Two Facts ( Logical and Physical Queries Fact ) and 4 degenerative dimension – Logical Query, Physical Query, Time and User

I believe logical dimensions with level based or value based hierarchy is one of the most important feature we should use for analysis. The logical dimensions with hierarchies for logical queries dimension, user dimension and time are very useful for reporting and analysis.

The important measures included for analyses of logical and physical query analysis are shown below. Most of them are very straight forward and directly mapped from the physical layer to logical layer.

Presentation Layer

Drag and drop from logical layer to presentation.

Analysis (Report) Build 

The following are some of the sample reports which one can build based on usage tracking logical and physical statistical data.

C U Next Time