The Oracle BI Server supports the collection of usage tracking data. It is becoming very important with OBIEE 11g as Oracle BI Summary Advisor feature works in conjunction with the usage tracking feature. There is a significant value addition from previous version (10g) to the latest version of Usage Tracking with OBIEE 11g. It has been changed for better, but I still believe there are ample opportunities to get value out of this solution apart from simple log of logical and physical queries.
This blog series on usage tracking is an attempt to explain how usage tracking can be enhanced to provide a value to business users along with the technical and backend support resources. Business Intelligent solution should provide business intelligent inputs. In Part A I would be focusing on setup and admin of usage tracking, next blog will cover more than how to integrate usage tracking log data with human resource data within organization.
Why Usage Tracking
There are many business challenges which might come across when we deploy OBIEE/OBIA reporting solution.
The Oracle BI may not be optimized for querying that actually occurs
- End Users queries may not match with what is expected, so cache is not seeded with appropriate queries
- Additional aggregate table may need be created to speed up query performance or may need to remove aggregate tables which are not been used etc
The DBA or Support may like to investigate the physical query for business logic as well as for database tuning activities.
The company may like to track reporting and database usage at user or department level, this might be to charge the user/department based on report/database usage or there may be legal /regulatory requirement around usage tracking and data protection compliances.
Solution – Oracle Usage Tracking
To fulfill all business challenges mentioned above, Oracle Usage Tracking provides business solution. Oracle Usage Tracking mechanism tracks and stores Oracle BI Server usage at detail query(logical and physical) level.
The accumulation of usage tracking statistics that can be used in a variety of ways such as database performance optimization, aggregation strategies (summary advisor) and users and department level based billing for the resources ( reports or databases) consumption.
OBIEE provides ability to analyze usage results
Sample Usage Tracking Implementation
A sample usage tracking implementation is provided with the Oracle Business Intelligence installation at:
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obisn\sample\usagetracking
NOTE – The repository and web cat content provided in this folder works with earlier version OBIEE 10g. Require upgrade utility to upgrade repository and web cat. I ran upgrade utility as suggested, once migration is finished what I have found is that usage tracking for logical queries does not reflect the new additions done for OBIEE 11g and repository does not have physical query table exposed for reporting purpose.
Setting up Direct Insertion
Direct insertion is the recommended method for setting up usage tracking, although option to write in file is also available.
Usage Tracking Statistics Database
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
- Choose bifoundation_domain which is a part of Web Logic Domain, right click and choose System Mbean Browser
- Expand Application Defined MBeans > Expand oracle.biee.admin > Expand Domain:bifoundation_domain > Expand BIDomain and Select the BIDomain MBean where group=Service
- Lock the domain
- Expand BIDomain and select the BIDomain MBean where Group=Service
- Display the Operations tab
- Click the lock link , ‘invoke’ and return
- 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
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"
- 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
$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.
I know there is nothing new in this blog, but still I hope you find this helpful at least as a starting point to set up usage tracking in OBIEE 11g.
No comments:
Post a Comment