Best Practices from Oracle Development's A‑Team

Oracle Analytics Cloud (OAC) - Using Usage Tracking

Jay Pearson
Consulting Solutions Architect, Business Intelligence


* This blog was last tested on OAC 105.8.0-133 (Version 5.9) + ADW 18c *

Click here for recommended prerequisite reading on OAC Usage Tracking.

Usage Tracking is a powerful feature that has been available in Oracle Business Intelligence (OBI) since its early inception. It was first made available in Oracle Analytics Cloud (OAC) in Release 105.2 - March 2019.

This article walks through the steps to configure OAC Usage Tracking with an Autonomous Data Warehouse (ADW).

Note: Currently OAC Usage Tracking is not supported with Data Gateway (DG) using an on-premises databases. This blog will be updated if / when on-premise support becomes available.

Usage tracking currently supports ADW as well as ATP and Public Oracle Databases in OCI (including DBCS).

The blog demonstrates how to use Oracle Data Visualization (DV) to create a two canvas (tab) project that summarizes OAC Usage Tracking data by "Rolling 15 Days Usage Overview" and "Trend & Seasonality".

At the time of publishing, a live sample of the "Real Time Sandbox Usage USA" Usage Tracking Project was available from OACLive. OACLive is a free public analytics library packed with insightful Oracle Analytics demos and samples!

The "Real Time Sandbox Usage USA" DVA can be downloaded from here [Pwd: Admin123]. The ADW connection needs to be updated for your own connection credentials.

 Create Usage Tracking Database Schema User

From Oracle SQLDeveloper (or a SQL Query Tool of choice):

Create a Usage Tracking database schema user:


Grant the DWROLE (ADW Data Warehouse Developer) role to the user:


Grant unlimited or set limited amount of table space to the Usage Tracking user.


 Configuring Usage Tracking RPD

If not done already, upload the ADW wallet file to OAC.

OAC > Console > Connections > Hamburger (top right) -> Upload Wallet > Select the .sso file in wallet.


An RPD is required to configure Usage Tracking. Follow this A-Team blog for steps on "Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse".

From the Oracle Analytics Developer Client Tool:

Create a new connection to the Usage Tracking schema:

Data source name example:

(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=abcdefghijk_odiadw_medium.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

The Connection Pool can be referenced easier if the name does not contain spaces.

For ADW the database type should be set to Oracle 12c:

Sample RPD Physical Layer:

Save RPD Changes -> Publish or replace the existing RPD containing new Usage Tracking connection.

 Set Usage Tracking Parameters

Log into OAC -> Go to the Console -> System Settings

Note: If upgrading from OBI/BICS Usage Tracking to OAC Usage Tracking, it is recommended to let the OAC UI create new Usage Tracking tables. Do not create the tables manually in the database or try and use existing OBI/BICS  Usage Tracking tables.

In the example it is suggested to name the tables: INIT_BLOCK_INFO, PHYSICAL_QUERIES, and LOGICAL_QUERIES. These names are just a suggestions - and can be modified as desired.

The OBI/BICS usage tracking tables were named: S_NQ_ACCT, S_NQ_DB_ACCT, S_NQ_INITBLOCK. OAC Usage tracking data may not get populated when pointing to these old tables. For better results, let the UI create fresh tables then manually copy the historic usage tracking data from the old tables to the new.


Populate the Following Usage Tracking Parameters:

Usage Tracking Init Block Table: UsageTracking.USAGE_TRACKING.INIT_BLOCK_INFO

{RPD Physical Layer Database Name.DB_SCHEMA_USER.InitBlockTableName} 

Usage Tracking Max Rows: 100,000

{Max Rows Allowed is 100,000}

Usage Tracking Connection Pool: UsageTracking.UsageTrackingConnectionPool

{RPD Physical Layer DatabaseName.ConnectionPoolName}

Usage Tracking Physical Query Logging Table: UsageTracking.USAGE_TRACKING.PHYSICAL_QUERIES

{RPD Physical Layer Database Name.DB_SCHEMA_USER.PhyiscalQueryTableName} 

Usage Tracking Logical Query Logging Table: UsageTracking.USAGE_TRACKING.LOGICAL_QUERIES

{RPD Physical Layer Database Name.DB_SCHEMA_USER.LogicalQueryTableName} 

Restart the OBI Server

After the restart, the three defined Usage Tracking tables INIT_BLOCK_INFO, PHYSICAL_QUERIES, and LOGICAL_QUERIES will be available in the USAGE_TRACKING schema:

Confirm you can view the data in SQLDeveloper (or preferred SQL Query Tool):

 Create OAC Connection and Data Sets

Create a connection to the USAGE_TRACKING ADW schema:

Create a Data Set for LOGICAL_QUERIES:

Create a Data Set for PHYSICAL_QUERIES:

 Build Usage Tracking Project

The below sample project has been built using the LOGICAL_QUERIES data set.

This sample is intended to be starting point and not a comprehensive pre-built solution.

Oracle Data Visualization (DV) has been used to create this Project. OAC Analysis Reports and Dashboards may also be used to track/distribute OAC Usage Tracking information.

 Using the "Real Time Sandbox Usage USA" DVA

a) Create a new ADW Usage Tracking connection and Project. From the new Project go to Visualize -> Developer -> Data Sets -> Copy the ADW connection details -> Paste over the supplied project connection details.


   "XSA('first.last@email.com'.'LOGICAL_QUERIES')": {
      "datasetId": "'first.last@email.com'.'LOGICAL_QUERIES'",
      "subjectArea": "XSA('first.last@email.com'.'LOGICAL_QUERIES')",
      "displayName": "LOGICAL_QUERIES",
      "description": "External Data Set",
      "owner": "first.last@email.com",
      "ownerDisplayName": "first.last@email.com",
      "filelength": "0",
      "dataLastModified": "2020-09-09T22:52:31.461+0000",
      "metadataLastModified": "2020-09-09T22:52:31.844+0000",
      "createdDate": "2020-09-09T22:15:11.791+0000",
      "isCertified": false,
      "name": "LOGICAL_QUERIES",
      "namespace": "first.last@email.com",
      "businessModel": "\"\"",
      "connectionNamespace": "first.last@email.com",
      "type": "sql",
      "provider": "external",
      "providerType": "db",
      "localfileStats": null,
      "providerName": "dwcs",
      "connectionName": "Usage_Tracking",
      "active": true,
      "connectionDisplayName": "UsageTrackingADW",
      "privateConnection": false,
      "credentialExists": true,
      "indexable": "",
      "customAttrs": "",
      "isTemporary": false,
      "effectivePermissions": "lrwxdp",
      "bdp-profile": "{\"profile\":\"b172dc9f-b5a0-4bd4-9c7f-33f5e1be3301\"}",

b) Find and replace on DataSets tab:


c) Find and replace on XML (JSON Recommended) tab:

Find: LOGICALQUERIES -> Replace: LOGICAL_QUERIES (my table name)

d) Update the USER_NAME filter to include my user name … it was set to “visitor”

 Want to Learn More?

Click here to download the "Real Time Sandbox Usage USA" DVA [Pwd: Admin123]. The ADW connection needs to be updated for your connection credentials.

Click here for Oracle Analytics Cloud (OAC) Usage Tracking Documentation.

Click here for OACLive - for more Oracle Data Visualization (DV) live demos/samples.

Click here for more A-Team Oracle Analytics (OAC) Blogs.


This article walked through the steps to configure Usage Tracking with OAC and ADW. Usage Tracking was made available in Oracle Analytics Cloud (OAC) starting from Release 105.2 in March 2019. The blog demonstrates how to use Oracle Data Visualization (DV) to create a Usage Tracking Project. Other OAC topics covered in the blog include: creating a database schema, configuring an RPD, OAC console system settings, creating OAC connections/data sets, and DV projects.

Join the discussion

Comments ( 10 )
  • VM Thursday, April 2, 2020
    It will be great to get this sample project .dva as a starting point.
  • Jay Thursday, April 16, 2020
    A link has been added to download the "Real Time Sandbox Usage USA" DVA.
  • Marcelo Finkielsztein Monday, May 11, 2020
    Hello Jay,

    Any ideas or agenda when usage tracking will be available to OAC+RDG+Oracle_on_Premise_DB users?

    Marcelo Finkielsztein
  • Jay Pearson Monday, May 11, 2020
    On-Premise database for usage tracking will be supported when private end-points for egress are available. At this stage there is no ETA.
  • Bibin Kumar Monday, August 24, 2020
    we followed the exact steps, but the tables are getting created. Any idea on what mis-step we may have done?
  • Jay Tuesday, September 8, 2020
    Hi Bibin,

    Please log an SR with Oracle Support if you have followed the blog steps and it is still not working.

    If you find I have missed a step in the blog let me know and I can add it in.

  • Ajit Friday, October 9, 2020
    Hej Jay
    I see that ADW is supported for usage tracking. What if we ADW is provisioned with Private end point ? Both OAC and ADW are on same VCN of course.

    In this scenario, Usage tracking works ? Because I guess we anyway have to use RDG for OAC to connect to ADW private
  • Jay Monday, October 12, 2020
    Hi Ajit,
    When OAC supports Private Access Channel (reverse private connections) it will support private ADW for Usage Tracking. Usage Tracking is not supported with RDG.
    Thanks, Jay
  • Hilbert Friday, February 12, 2021
    With OAC 5.9 i receive an error with the following "Project" calculation:

    left( (right((REPLACE(XSA('weblogic'.'LOGICALQUERIES_UT')."Columns"."SAW_SRC_PATH",'/@Catalog/','/')),LENGTH((REPLACE(XSA('weblogic'.'LOGICALQUERIES_UT')."Columns"."SAW_SRC_PATH",'/@Catalog/','/')))-POSITION('/' IN (REPLACE(XSA('weblogic'.'LOGICALQUERIES_UT')."Columns"."SAW_SRC_PATH",'/@Catalog/','/'))) +1-17 )), POSITION('"' IN (right((REPLACE(XSA('weblogic'.'LOGICALQUERIES_UT')."Columns"."SAW_SRC_PATH",'/@Catalog/','/')),LENGTH((REPLACE(XSA('weblogic'.'LOGICALQUERIES_UT')."Columns"."SAW_SRC_PATH",'/@Catalog/','/')))-POSITION('/' IN (REPLACE(XSA('weblogic'.'LOGICALQUERIES_UT')."Columns"."SAW_SRC_PATH",'/@Catalog/','/'))) +1-17 )))-1)

    It is saying LEFT should be greater than 0. If i add +1, the calculations works, but i have a layout that i don't want.
  • Jay Monday, March 29, 2021
    Hilbert, I tested the project with OAC 5.9 and it works fine. This error is caused from NULL's in the SAW_SRC_PATH field. Thanks, Jay
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content