Oracle Analytics Cloud retains a lot of functionality that OBIEE offers. One such feature is Usage Tracking, which, most companies who have implemented in OBIEE will want to implement in OAC as well. Usage Tracking is even more relevant in a cloud implementation because one needs to know the usage so that they can make decisions to upscale/downscale(which impacts the costs incurred).

In this article, let’s go through the steps required to setup Usage Tracking in Oracle Analytics Cloud(OAC). Below are some key points about Usage Tracking

  1. Usage Tracking is used to monitor system and ad-hoc query performance and to provide input on usage trends for use in billing users and departments for resources.
  2. Usage tracking is particularly useful in determining user queries that are creating performance bottlenecks, based on query frequency and response time.
  3. When usage tracking is enabled, the Oracle BI Server collects usage tracking data for each query and writes statistics to a usage tracking log file or inserts them directly to a database table
  4. Administrators set up the criteria to track user queries and generate usage reports that can be used in a variety of ways such as database optimization, aggregation strategies, or billing users or departments based on the resources that they consume


  1. Oracle Analytics Developer Client Tool to manage your data model (RPD file)
  2. Usage Tracking requires an access permission to a database where you want to store usage information. User login with Create table privileges on the database schema.

Steps to configure Usage Tracking in OAC:

  • Create a dedicated connection pool for Usage Tracking in RPD. This connection should point to a database where Usage Tracking tables will be created and maintained
  • Open RPD which is deployed in OAC, create a new database in Physical layer and provide a name(Usage_Tracking in this example) and choose the database type

 Oracle Analytics Cloud UT_1 | Tangenz Corporation

** Make sure the names given for database and connection pool doesn’t have spaces.

  • Create new connection pool under this database and provide name (UsageTrackingConnection). Provide the database details where Usage Tracking tables need to be created and provide login credentials to the schema

 UT_2 | Tangenz Corporation

  • Create a physical schema in RPD. The name of the schema in RPD should be the same as database schema name(UT_DB)

Oracle Analytics Cloud(OAC) UT_3 | Tangenz Corporation

  • Save the RPD and upload it in OAC.
  • Log in to OAC DV URL
  • Click on Console from the Hamburger menu at the left top

Oracle Analytics Cloud | Tangenz Corporation

  • Click on System Settings

Oracle Analytics Cloud(OAC) UT_5 | Tangenz Corporation

Now, let’s understand the Usage Tracking settings and respective formats. Make sure the formats you enter in these respective fields match exactly with the RPD setup that was done above.

  1. Usage Tracking Init Block Table:

Format: <Database>.<Catalog>.<Schema>.<Table> or <Database>.<Schema>.<Table>

Example: Usage_Tracking.UT_DB.INIT_BLOCK_INFO

  1. Usage Tracking Connection Pool:

Format: <Database>.<ConnectionPool>

Example: Usage_Tracking.UsageTrackingConnection

  1. 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

Format: <Database>.<Catalog>.<Schema>.<Table> or <Database>.<Schema>.<Table>

Example: Usage_Tracking.UT_DB.PHYSICAL_QUERIES

  1. Usage Tracking Logical Query Logging Table:

This is the table where details about the logical SQLs executed by BI Server are tracked

Format: <Database>.<Catalog>.<Schema>.<Table> or <Database>.<Schema>.<Table>

Example: Usage_Tracking.UT_DB.LOGICAL_QUERIES

How to Set up Usage Tracking in Oracle Analytics Cloud(OAC) UT_6 | Tangenz Corporation

  • One can also control the max number of rows that the Usage tracking tables can hold. Enter 0 if you want to set it to Unlimited.
  • Once the settings are complete, restart the OBI Server by clicking in Restart button that gets enabled on the top right side of the page. This gets enables only after we change any settings in the System Settings.

How to Set up Usage Tracking in Oracle Analytics Cloud(OAC) | Tangenz Corporation

Oracle Analytics Cloud(OAC) UT_8 | Tangenz Corporation

Once the restart is complete, the 3 Usage Tracking tables will be created in the database and the Usage will be logged into the tables. One can view the data by connecting to the database from the Backend or by building a Subject area using these tables in the RPD.

Below are some of the important columns from the Usage Tracking Tables

Important Columns in Logical Queries Table


End time of query execution


Error message if the query has errored out


Primary key


Actual query text


Total response time of query in seconds


Number of rows returned by the query


Dashboard name where query is getting generated


Session ID from the user firing the query


Start time of the query execution


Subject area used for the query


User ID who has executed the query

Important Columns in Physical Queries Table


Primary key


Foreign key from the logical queries table


Query text


Time taken by query to complete


Number of rows returned by the query


Start time of the query


End time of the query

The join between these 2 tables can be performed using the join condition:

LogicalQueries.ID = PhysicalQueries.Logical_Query_ID

Once Usage Tracking is enabled, the system usage can be analyzed from DV too. Create a DV connection to the Usage Tracking database, create datasets for the Physical Queries and Logical Queries tables and analyze them within a DV project.

As you can see, setting up Usage Tracking in OAC is a very easy process. 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.

If you need any help with your Oracle Analytics Cloud Implementations, Please drop a line here. Our Oracle HCM Cloud experts will contact you promptly.