rmoff

October 6, 2009

Usage Tracking – only half the story …

Filed under: obiee, systemsmanagement, usagetracking — rmoff @ 10:28

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:

     [59048] 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:

Stop nqsserver

Unix: run-sa.sh stop
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

Load the RPD in the Administration Tool, and edit the properties of the QUERY_TEXT column in the S_NQ_ACCT table.
1
2

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.

c:\OracleBI\server\Bin\nQUDMLExec.exe
-U Administrator
-P SADMIN
-I c:\extend_query_text.udml
-B c:\OrignalRPD.rpd
-O c:\UpdatedRPD.rpd

For more information on using UDML see here and here.

Start nqsserver

Unix: run-sa.sh start or run-sa.sh start64
Windows: Services -> Start Oracle BI Server

Advertisements

Create a free website or blog at WordPress.com.

%d bloggers like this: