Usage Tracking Issues 10g -- still!!

OK, this may all be fixed in 11g, but I'm still a 10g guy, 4 years after that hatched. Having said that, if it isn't fixed in 11g this would have to be considered a serious sign of being asleep at the switch. If it is fixed, then maybe 11g starts to look better to me, somewhat anyway.

So here's the set of problems I see in usage tracking in 10g. You have two choices when it comes to usage tracking: either direct inserts of usage information into a table (s_nq_acct) or usage tracking information written to files (a new file every X minutes, as determined by the parameters in NQSConfig.ini).

The biggest issue with direct insert is that the QUERY_TEXT column is only varchar2(1024), which means that you're only going to record a fraction (maybe only a 10th!) of the entire logical query. If you expand it, it pretty much shuts down direct insert. So this column doesn't give you the information you need to really understand whatever issues might have arisen when the query was executed because you can't see all of it and thus you can't duplicate it and examine the physical SQL that would have been generated.

Another issue is that it doesn't differentiate whether a query is being run on behalf of a user via an iBot or whether the user is running it interactively as a dashboard/Answers user.

I should also mention another problem that tripped me up for a while until, thanks to a blog post by someone else, the light bulb finally went on. The s_nq_acct table must be defined in the repository's physical layer. The Oracle database DDL (in the OracleBI/server/schema folder) for the table includes several columns that are of type DATE. If the data types for these columns are DATE in the physical layer of the repository -- i.e. if the datatypes apparently exactly match the DDL -- direct inserts will fail. The DATE columns must be defined as DATETIME in the metadata (which they will be if you import the table, but be careful about setting it up manually).

So what happens if you take the second alternative and have the bi server write usage information to files (i.e. DIRECT_INSERT = NO in NQSConfig.ini)? Now you DO get all the logical SQL. However, the file doesn't contain all the other fields that would have been written in direct insert mode! Most importantly, QUERY_SRC_CD is null, always, instead of telling you whether a query was either a

GlobalFilter
Report
ValuePrompt
drill
Null (??)

Normally, if I'm looking at usage, I'm interested in counting only the "Report" queries. Without direct insert you can't differentiate -- you have to count everything. Not that the direct insert categorization is necessarily what you'd like if you had a choice (how about iBot being one of the query types?), but still it contains pretty essential information. Why isn't it written to the file? Why isn't what's written to the file (or directly to a table) configurable, for that matter?

For several months I used the file method rather than the direct insert method because a) direct insert stopped working [since fixed by changing DATE to DATETIME in the rpd -- see above];  and b) I really wanted the entire logical query text.

The consequence of doing this was that I periodically took all the usage files that had been generated and loaded them into a new table (S_NQ_ACCT_ADD) using SQLLDR. Doing this was a bit of a pain, but it enabled analysis of the usage data which otherwise, just sitting in a text file, would be pretty useless.

A word of advice here: change the datatype of QUERY_TEXT in the new table to CLOB. Also increase the size of the NODE_ID column from varchar2(15) to varchar2(128), or otherwise loads will fail.

The pertinent section of the sqlldr control file then says:

APPEND INTO TABLE S_NQ_ACCT_ADD
      FIELDS TERMINATED BY ";"
  TRAILING NULLCOLS

  (
    USER_NAME CHAR(128) ,
REPOSITORY_NAME CHAR(128) ,
SUBJECT_AREA_NAME CHAR(128) ,
NODE_ID CHAR(128) ,
START_TS DATE "YYYY-MM-DD HH24:MI:SS",
START_DT DATE "YYYY-MM-DD",
START_HOUR_MIN ,
END_TS DATE "YYYY-MM-DD HH24:MI:SS",
END_DT DATE "YYYY-MM-DD",
END_HOUR_MIN ,
QUERY_TEXT char(40000),
SUCCESS_FLG ,
ROW_COUNT ,
TOTAL_TIME_SEC ,
COMPILE_TIME_SEC ,
NUM_DB_QUERY ,
CUM_DB_TIME_SEC ,
CUM_NUM_DB_ROW ,
CACHE_IND_FLG CHAR(1)
)

As a CLOB, the query text can be inserted. However, the data can't be queried via OBIEE very successfully since OBIEE issues a SELECT DISTINCT, which fails with a CLOB. So you also need to load the data from this table into another table, where the query_text column is a varchar.

Yet another issue is that when the user has set a session variable, that session variable setting shows up in the logical sql, delimited from the rest of the logical SQL by a semi-colon. In other words, it looks like a separate column, since all the columns are delimited by semi-colons. SQLLDR will create a .BAD file for these which won't be inserted because of the column mis-matches.

You can load these bad records into another table (called, for example, "s_nq_acct_bad") which contains the extra column, and then in a separate SQL statement the relevant columns can be inserted later into s_nq_acct.

All in all, it's a multi-step workaround process. It really gives the impression that usage tracking wasn't given much attention by the development team.

[Further note a year later: The problems associated with writing usage to files became too much work to deal with. We went back to direct inserts despite the shortcomings.]