How to configure Usage Tracking in OAC?

Oracle Analytics Cloud supports the accumulation of Usage Tracking (UT) statistics that helps in monitoring system usage and performance. 

These statistics can be used in a variety of ways such as in system or database optimization, aggregation strategies, or internal billing of users/departments based on the resources that they consume. Usage tracking is particularly useful in determining user queries that are creating performance bottlenecks, based on query frequency and response time. 
Oracle BI Server tracks usage at a detailed query level. When Usage Tracking is enabled, BI Server collects data records for every query that is executed and writes it all to database tables. Both logical and physical SAL queries are tracked and logged in separate tables, along with various performance measures : 

The following video gives a high level overview of how to set up Usage Tracking on your OAC environment. This blog content below gives more details about this capability.

Prerequisites to enable Usage Tracking

Following conditions need to be met in order to enable Usage Tracking on your environment:

1) Usage Tracking requires a full RPD (Metadata Repository) to be enabled. It will track usage on any queries, even outside the RPD, but it only requires an RPD for its configuration. For example, UT cannot be enabled when only a TCM (Thin Client Modeler, ie web based admin-tool) model is active on your OAC.

2) UT requires an access to a Database to write data back into tables. So a user login with Create table privileges on the database schema will be needed during the configuration.

Steps to configure Usage Tracking

UT configuration broadly involves the following two steps: 
1) Defining a Database connection in RPD: using Admintool client (editor for RPD repository), define a database connection in the physical layer of your RPD. This connection should point to a database where Usage Tracking tables will be created and maintained. This database can be anywhere on the Cloud as long as it is accessible for write accss by OAC. 

2) System Settings in OAC : Configure Usage Tracking parameters like connection pool name, physical query table name and logical query table name.

Step 1: Define a database connection in rpd

Open the RPD which is uploaded in OAC and create a new database in the physical layer. Provide an appropriate name (eg: UsageTracking) and choose the database type as Oracle 12c.

Under this database, create a new Connection Pool with an appropriate name (Eg: UTConnectionPool). Provide the connection details to the database where Usage Tracking is to be configured and login credentials to the schema. 
Note:- This database user needs to have create table privileges on the schema. 

Next, create a physical schema in the RPD with the same name as the Database schema to be used by UT (Eg:- UT_demo). 

Once these definitions are completed, the physical layer should look like this. 

Save the RPD and upload it onto OAC using the Replace Data Model option within the Service Console.

Step 2:System Configuration

On OAC, open the System settings screen from the Console Tab. 

Scroll down and modify the following properties.

1) Toggle on the Enable Usage Tracking button

2) Usage Tracking Connection Pool: 
Enter the connection pool name in the format : <Database>.<ConnectionPool>. 
Eg:- UsageTracking.UTConnectionPool

3) Usage Tracking Physical Query Logging Table: 
This the table where details about the physical queries executed by BI Server against the underlying database are tracked. 
Enter the name in the format :  <Database>.<Schema>.<Table>
Eg:- UsageTracking.UT_demo.PhysicalQueries

4) Usage Tracking Logical Query Logging Table: 
This is the table where details about the logical SQLs executed by BI Server are tracked. Enter the name in the format : <Database>.<Schema>.<Table>
Eg:- UsageTracking.UT_demo.LogicalQueries

5) Usage Tracking Max Rows : Specifies the number of rows after which the UT tables will be purged. The default value is 0 which implies there is no purging. You can set it to a any desired value and the records would be purged once the threshold is reached.

After entering these details, restart Oracle BI Server. Once the server is restarted, Usage Tracking is enabled and the two UT tables are created in the database.

UT Tables Explained

Open SQL Developer, login to the UT database and observe that the 2 tables are created.

To generate some usage tracking data, login to OAC and click around some DV projects: both XSA as well as subject area based. Also open the BI classic home and open few dashboards to generate some queries. Now observe that the tables for logical and physical queries are populated with usage tracking information.

Important Columns in Logical Queries table

END_TS	End time of query execution
ERROR_TEXT	Error message if the query has errored out
ID	Primary key
QUERY_TEXT	Actual Query Text
RESP_TIME_SEC	Total response time of query in seconds
ROW_COUNT	No of rows returned by the query
SAW_DASHBOARD	Dashboard name where query is getting generated
SESSION_ID	Session ID from the user firing the query
START_TS	Start time of the query execution
SUBJECT_AREA_NAME	Subject area used for the query
USER_NAME	User ID who has executed the query

Important Columns in Physical Queries table

ID	Primary Key
LOGICAL_QUERY_ID	Foreign Key from the logical queries table
QUERY_TEXT	Query Text
TIME_SEC	Time taken by query to complete
ROW_COUNT	No of rows returned by Query
START_TS	Start time of the query
END_TS	End time of query

The join between the 2 tables can be performed using the join condition 
   LogicalQueries.ID  =  PhysicalQueries.Logical_Query_ID. 
Note that not all logical queries would generate a corresponding physical query. Some of the logical queries may hit cache and return results from cache and not generate a physical query.

Analyzing UT Data

Once UT is enabled, the system usage can be analyzed from DV. In order to do this, create a DV connection to the UT database, create datasets for the PhysicalQueries and LogicalQueries table and analyze them within a DV project. 

Following is a sample analysis built on the UT tables that shows # of sessions, # of queries,  most frequently used subject areas, most frequently used dashboards etc. 

Conclusion

Usage Tracking provides a mechanism for administrators to keep track of the usage of the OAC system. These statistics can be leveraged to take decisions to scale up, scale down, restrict access during certain time periods, pause/resume the system and so on.

Are you an Oracle Analytics customer or user?

We want to hear your story!

Please voice your experience and provide feedback with a quick product review for Oracle Analytics Cloud!