Usage Tracking – OBIEE 12c

Usage tracking in OBIEE 12c is very similar to that in 11g, which enables mainly administrative users to track all reports being run and queries fired to the database. It is also very helpful in identifying and analyzing objects that require better query performance. The configuration steps will be mentioned in this blog as well as the tables that need to be set up for triggering Usage Tracking.

System Specifications/Pre-requisites

• OBIEE 12c (In this example 12.2.1.2.0) has been already installed
• Data Source (RCU and Warehouse): (In this case Oracle 11.2.0.4.0) have been already set
• Need file permissions to 12c environments.

Setting up Usage Tracking

Step 1: – Creating Usage Tracking Tables

1. Create the following tables first in the database schema where usage tracking needs to be set up.

• S_ETL_DAY
• S_ETL_TIME_DAY
• S_NQ_ACCT
• S_NQ_DB_ACCT

2. The Table creation scripts are attached below (Here OBIW: Schema where these tables are being created).

• CREATE TABLE OBIW.S_ETL_DAY
  ( DAY_DT DATE,
  CAL_MONTH NUMBER(*,0),
  DAY_OF_MONTH NUMBER(*,0),
  DAY_NAME CHAR(12 BYTE),
  DAY_OF_YEAR NUMBER(*,0),
  DAY_OF_WEEK NUMBER(*,0),
  CAL_WEEK NUMBER(*,0),
  MONTH_NAME CHAR(15 BYTE),
  CAL_YEAR NUMBER(*,0),
  PER_NAME_MONTH VARCHAR2(50 BYTE),
  PER_NAME_QTR VARCHAR2(20 BYTE),
  FSCL_YEAR NUMBER(*,0),
  FSCL_QTR CHAR(5 BYTE),
  DAY_AGO_DT DATE,
  WEEK_AGO_DT DATE,
  MONTH_AGO_DT DATE,
  QUARTER_AGO_DT DATE,
  YEAR_AGO_DT DATE,
  ROW_WID NUMBER(*,0)
  )
• CREATE TABLE OBIW.S_ETL_TIME_DAY
  ( TIME_SLICE DATE,
  HOURS NUMBER(*,0),
  MINUTES NUMBER(*,0),
  HAGO DATE,
  MIN_SEQ NUMBER(*,0),
  HOUR_MIN VARCHAR2(5 BYTE)
  );
• CREATE TABLE OBIW.S_NQ_ACCT
  ( USER_NAME VARCHAR2(128 BYTE),
  REPOSITORY_NAME VARCHAR2(128 BYTE),
  SUBJECT_AREA_NAME VARCHAR2(128 BYTE),
  NODE_ID VARCHAR2(100 BYTE),
  START_TS DATE,
  START_DT DATE,
  START_HOUR_MIN CHAR(5 BYTE),
  END_TS DATE,
  END_DT DATE,
  END_HOUR_MIN CHAR(5 BYTE),
  QUERY_TEXT VARCHAR2(1024 BYTE),
  QUERY_BLOB CLOB,
  QUERY_KEY VARCHAR2(128 BYTE),
  SUCCESS_FLG NUMBER(10,0),
  ROW_COUNT NUMBER(20,0),
  TOTAL_TIME_SEC NUMBER(10,0),
  COMPILE_TIME_SEC NUMBER(10,0),
  NUM_DB_QUERY NUMBER(10,0),
  CUM_DB_TIME_SEC NUMBER(10,0),
  CUM_NUM_DB_ROW NUMBER(20,0),
  CACHE_IND_FLG CHAR(1 BYTE) DEFAULT ‘N’ NOT NULL ENABLE,
  QUERY_SRC_CD VARCHAR2(30 BYTE) DEFAULT ”,
  SAW_SRC_PATH VARCHAR2(250 BYTE) DEFAULT ”,
  SAW_DASHBOARD VARCHAR2(150 BYTE) DEFAULT ”,
  SAW_DASHBOARD_PG VARCHAR2(150 BYTE) DEFAULT ”,
  PRESENTATION_NAME VARCHAR2(128 BYTE) DEFAULT ”,
  ERROR_TEXT VARCHAR2(250 BYTE) DEFAULT ”,
  IMPERSONATOR_USER_NAME VARCHAR2(128 BYTE) DEFAULT ”,
  NUM_CACHE_INSERTED NUMBER(10,0) DEFAULT NULL,
  NUM_CACHE_HITS NUMBER(10,0) DEFAULT NULL,
  ID VARCHAR2(50 BYTE),
  ECID VARCHAR2(1024 BYTE),
  TENANT_ID VARCHAR2(128 BYTE),
  SERVICE_NAME VARCHAR2(128 BYTE),
  SESSION_ID NUMBER(10,0),
  HASH_ID VARCHAR2(128 BYTE);
  CREATE INDEX OBIW.S_NQ_ACCT_M1 ON OBIW.S_NQ_ACCT (START_DT, START_HOUR_MIN, USER_NAME);
  CREATE INDEX OBIW.S_NQ_ACCT_M2 ON OBIW.S_NQ_ACCT (START_HOUR_MIN, USER_NAME);
  CREATE INDEX OBIW.S_NQ_ACCT_M3 ON OBIW.S_NQ_ACCT (USER_NAME) ;
  ALTER TABLE OBIW.S_NQ_ACCT ADD CONSTRAINT S_NQ_ACCT_PK PRIMARY KEY (ID);
• CREATE TABLE OBIW.S_NQ_DB_ACCT
  ( ID NUMBER(10,0),
  LOGICAL_QUERY_ID VARCHAR2(50 BYTE),
  QUERY_TEXT VARCHAR2(1024 BYTE),
  QUERY_BLOB CLOB,
  TIME_SEC NUMBER(10,0),
  ROW_COUNT NUMBER(20,0),
  START_TS DATE,
  START_DT DATE,
  START_HOUR_MIN CHAR(5 BYTE),
  END_TS DATE,
  END_DT DATE,
  END_HOUR_MIN CHAR(5 BYTE),
  HASH_ID VARCHAR2(128 BYTE),
  PHYSICAL_HASH_ID VARCHAR2(128 BYTE);
  CREATE INDEX OBIW.S_NQ_DB_ACCT_I1 ON OBIW.S_NQ_DB_ACCT (LOGICAL_QUERY_ID);
  ALTER TABLE OBIW.S_NQ_DB_ACCT ADD CONSTRAINT FK_S_NQ_DB_ACCT FOREIGN KEY (LOGICAL_QUERY_ID);

3. Once the tables are created load the S_ETL_DAY and S_ETL_TIME_DAY time dimension tables. One can get the data dump from Oracle Support or Oracle Website.
Step 2: – Configuring Usage Tracking

1. To set up direct insertion for new (non-upgraded) installations, use a text editor. To set up direct insertion usage tracking on the Oracle BI Server computer, open the NQSConfig.INI file in a text editor. The file is located at: $MW_HOME/user_projects/domains/bi/config/fmwconfig/biconfig/OBIS
2. Edit the file.

3. Make the following changes in the file.

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

[USAGE_TRACKING] section:

ENABLE = YES;

DIRECT_INSERT = YES;

PHYSICAL_TABLE_NAME = “Oracle Analytics Usage”.”Catalog”.”dbo”.”S_NQ_ACCT”;

CONNECTION_POOL = “Oracle Analytics Usage”.”Usage Tracking Writer Connection Pool”;

4. Save the file. Once done, restart BI Services from Enterprise Manager using weblogic credentials.

5. Once done, login to OBIEE Analytics to run a couple of reports and check from either of the following to make sure OBIEE Usage Tracking is working:

6. Run a report from Usage Tracking subject area to check these logs have been updated.

7. Run a simple query in OBIW schema on table S_NQ_ACCT to make sure these logs are being updated.