Oracle Analytics Cloud (OAC) - Using Usage Tracking

July 23, 2020 | 11 minute read
Jay Pearson
Consulting Solutions Architect, Business Intelligence
Text Size 100%:

 Background

* This blog was last tested on OAC 6.2 + 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).

* OAC Usage Tracking is NOT supported using Data Gateway (DG) with an on-premises database *

* On-premise databases are only supported when using Private Access Chanel (POC) *

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:

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

b) Import and open the sample "Real Time Sandbox Usage USA" Project.

c) Click on the Data tab

d) Click on the hamburger (3 dots to right) -> Select Replace Dataset

e) Highlight the new (your) Dataset -> Click Select

f) Map the date fields:

-> Note: The first one is START_DT and the following two are START_TS

-> Drill into each one and map to date part (i.e. Week, Hour of Day, Weekday)

-> Click Replace

g) Click on the Visualize tab

h) Change the User Name Filter from "Visitor" to a valid OAC User

 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.

Jay Pearson

Consulting Solutions Architect, Business Intelligence


Previous Post

Provisioning Oracle Analytics Cloud Natively in Oracle Cloud Infrastructure

Dayne Carley | 1 min read

Next Post


Deploying a Highly Available Windows File Server on OCI

Leo Yuen | 5 min read