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.
- Open Enterprise Manager – put in username/password to login
- 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.
- Set Usage Tracking Attributes as below
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"
- Release Lock for domain and Commit
- Return to the BIDomain MBean where Group=Service
- Display the Operations tab
- Click on the commit operation
- 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
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
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 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
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.
Drag and drop from logical layer to presentation.
C U Next Time