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.

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!

 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

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

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):

If upgrading from OBI Usage tracking to OAC Usage Tracking, queries must be switched to the new table structure. OBI used a single Usage Tracking table S_NQ_ACCT. Some column names have been changed or may be omitted in OAC and may require re-mapping. 

 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 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 ( 1 )
  • VM Thursday, April 2, 2020
    It will be great to get this sample project .dva as a starting point.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha