Usage tracking on OBIEE

Usage tracking on OBIEE can be enabled to write on to files or to to make direct inserts into the DB.

To enable usage tracking on DB

1. Run the script {ORacleBI}\server\Schema\SAACCT.Oracle.sql
or
Run the script below :
create table S_NQ_ACCT
(
USER_NAME VARCHAR2(128),
REPOSITORY_NAME VARCHAR2(128),
SUBJECT_AREA_NAME VARCHAR2(128),
NODE_ID VARCHAR2(15),
START_TS DATE,
START_DT DATE,
START_HOUR_MIN CHAR(5),
END_TS DATE,
END_DT DATE,
END_HOUR_MIN CHAR(5),
QUERY_TEXT VARCHAR2(1024),
SUCCESS_FLG NUMBER(10,0),
ROW_COUNT NUMBER(10,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(10,0),
CACHE_IND_FLG CHAR(1) default 'N' not null,
QUERY_SRC_CD VARCHAR2(30) default '',
SAW_SRC_PATH VARCHAR2(250) default '',
SAW_DASHBOARD VARCHAR2(150) default '',
SAW_DASHBOARD_PG VARCHAR2(150) default '',
PRESENTATION_NAME VARCHAR2(128) default '',
ERROR_TEXT VARCHAR2(250) default '',
RUNAS_USER_NAME VARCHAR2(128) default '',
NUM_CACHE_INSERTED NUMBER(10,0) default null,
NUM_CACHE_HITS NUMBER(10,0) default null
);

create index S_NQ_ACCT_M1 on S_NQ_ACCT
(START_DT, START_HOUR_MIN, USER_NAME);

create index S_NQ_ACCT_M2 on S_NQ_ACCT
(START_HOUR_MIN, USER_NAME);

create index S_NQ_ACCT_M3 on S_NQ_ACCT
(USER_NAME);

2. Ensure there is write permission on this table.

3. Import the Table S_NQ_ACCT into the repository as below:

4. Change the NQSConfig.ini.
Set Enable = YES

[ USAGE_TRACKING ]

ENABLE = YES;
DIRECT_INSERT = YES;
PHYSICAL_TABLE_NAME = "Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT";
CONNECTION_POOL = "Usage Tracking"."Usage_tracking";
BUFFER_SIZE = 10 MB ;
BUFFER_TIME_LIMIT_SECONDS = 5 ;
NUM_INSERT_THREADS = 5 ;
MAX_INSERTS_PER_TRANSACTION = 1 ;

PHYSICAL_TABLE_NAME must be "Database"."Catalog"."Schema"."Table" or "Database"."Schema"."Table"
CONNECTION_POOL must be "Database"."Connection Pool"

5. Restart the servers.