X

Best Practices from Oracle Development's A‑Team

Oracle Analytics Cloud (OAC) - Using Usage Tracking

Jay Pearson
Consulting Solutions Architect, Business Intelligence

 Background

Click here for recommended prerequisite reading on OAC Usage Tracking. TEST

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

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.

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:

CREATE USER USAGE_TRACKING IDENTIFIED BY Password;

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

GRANT DWROLE TO USAGE_TRACKING;

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

GRANT UNLIMITED TABLESPACE TO USAGE_TRACKING;

 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 Connection Pool: UsageTracking.UsageTrackingConnectionPool

{RPD Physical Layer DatabaseName.ConnectionPoolName}

Usage Tracking Initialization Block Table: UsageTracking.USAGE_TRACKING.INIT_BLOCK_INFO

{RPD Physical Layer Database Name.DB_SCHEMA_USER.InitBlockTableName} 

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} 

Usage Tracking Max Rows: 100,000

{Max Rows Allowed is 100,000}

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.

 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.

 Summary

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 ( 6 )
  • 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?

    Thanks,
    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.

    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