OBIEE comes with a very useful usage tracking feature. For information about it and how to set it up see these links:
Usage Tracking captures the logical SQL of queries in a column called QUERY_TEXT in the table S_NQ_ACCT. However, out of the box this column is defined as 1k (1024 bytes) long. In some situations this will limit its usefulness because the text will be truncated if necessary when it’s inserted.
When it’s truncated you may see this message in NQServer.log:
 Usage Tracking encountered an insert statement execution error. This error has occurred 1 times and resulted in the loss of 1 insert statements since this message was last logged. [nQSError: 17001] Oracle Error code: 12899, message: ORA-12899: value too large for column "OBIEE_USAGE_TRACKING"."S_NQ_ACCT"."QUERY_TEXT" (actual: 1039, maximum: 1024
To increase the length of query captured to an Oracle DB do the following:
Windows: Services -> Stop Oracle BI Server
ALTER table to increase column length
alter table s_nq_acct modify query_text varchar2(4000);
4000 is the maximum for a varchar2. You could define it as less if you wanted. 1024 is the default out of the OBIEE box.
Amend RPD physical layer
Manually – Admin Tool
Automatically – UDML
NB this is NOT SUPPORTED by Oracle!!
Copy this into a text file:
DECLARE COLUMN "Oracle Analytics Usage"."Catalog"."dbo"."S_NQ_ACCT"."QUERY_TEXT" AS "QUERY_TEXT" TYPE "VARCHAR" PRECISION 4000 SCALE 0 NULLABLE PRIVILEGES ( READ);
Apply it to the RPD using nqUDMLExec. I’ve split the statement over multiple lines to make it more readable.
run-sa.sh start or
Windows: Services -> Start Oracle BI Server