X

News and Views: Drive Smart Decisions with Cloud Analytics, Machine Learning and More

Understand User Behavior Using Oracle Analytics

Philippe Lions
Senior Director

How would you like to know what your software users are thinking and how they might react in the future? Oracle Analytics Cloud supports the accumulation of Usage Tracking (UT) statistics that help in monitoring system usage and performance to understand and predict user behavior. Knowing in advance what choices a user is likely to make helps increase efficiency and reduce errors.

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. 

Previous versions of Oracle Business Intelligence Server can track usage at a detailed query level. When Usage Tracking is enabled, it collects data records for every query that is executed and writes them all to database tables. Both logical and physical 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 Oracle Analytics Cloud environment.
 

 

Prepare to Enable Usage Tracking

We're going to dive into this process, but first you will need to meet the following conditions to enable Usage Tracking in your environment:

  1. Usage Tracking requires a full metadata repository database (RPD) 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 Thin Client Modeler (TCM) such as a web-based admin-tool model is active on your Oracle Analytics Cloud.
  2. UT requires access to a database to write data back into tables. A user login with create table privileges on the database schema will be needed during the configuration.

Configure Usage Tracking

UT configuration involves the following two steps: 

  1. Database connection in RPD: Using Administrator tool client (the editor for the 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 access by Oracle Analytics Cloud. 
  2. System Settings in Oracle Analytics Cloud: Configure Usage Tracking parameters, such as connection pool name, physical query table name, and logical query table name.
     

Define a Database Connection in RPD

Open the RPD which is uploaded in Oracle Analytics Cloud and create a new database in the physical layer. Provide an appropriate name (e.g., UsageTracking) and choose the database type as Oracle 12c.

Under this database, create a new Connection Pool with an appropriate name (e.g., UTConnectionPool). Provide the connection details to the database where UT is to be configured and login credentials to the schema. 


Note: This database user needs to have 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 (e.g., UT_demo). 

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

 

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

System Configuration

On Oracle Analytics Cloud, 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> 
Example: UsageTracking.UTConnectionPool

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

4) Usage Tracking Logical Query Logging Table: This is the table where details about the logical SQLs executed by Business Intelligence Server are tracked. Enter the name in the format: <Database>.<Schema>.<Table>
Example: 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 any desired value and the records will be purged once that threshold is reached.


After entering these details, restart Oracle Business Intelligence 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, log in to the UT database, and observe that the two tables have been created.

 

To generate some usage tracking data, log in to Oracle Analytics Cloud and click around some Data Visualization (DV) projects, both Extended Subject Areas (XSA) as well as subject area based. Also, open the BI classic home and open a 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

Number 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

Number of rows returned by the query

START_TS

Start time of the query

END_TS

End time of the 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 will generate a corresponding physical query. For example, some logical queries may hit cache and return results from cache. When this happens, logical queries will 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 Physical Queries and Logical Queries tables and analyze them within a DV project. 

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


Conclusion

Usage Tracking provides a mechanism for administrators to keep track of the usage of the Oracle Analytics Cloud 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.

To learn how you can benefit from the latest features in Oracle Analytics, visit Oracle.com/Analytics, and don't forget to subscribe to the Oracle Analytics Advantage blog and get the latest posts sent to your inbox.

Join the discussion

Comments ( 1 )
  • Tanvir Ahmed Tuesday, September 3, 2019
    That is a good information
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.