After setting usage tracking on
OBIEE 11g, customer have noticed that the value of node_id is recorded as "instance1:corea"
instead of the Hostname. I have seen number of instances where the column width
is not sufficient to hold the usage tracking attribute information in
respective columns. This blog is an
attempt to list down the steps to resolve these types of issues related to
usage tracking data.
Analysis of Issue
These issues are caused due to
the usage tracking table S_NQ_ACCT has the fields (e.g. NODE_ID) with size set at less than expected ( it is
set at 15 for NODE_ID). The first step is to increase the field size so it can
accommodate the appropriate data.
In example of NODE_ID, however,
even after increasing the field size, still the hostname cannot be captured in
the inserted value. After raising a Bug (14083146) , the development team has outlined that the NODE_ID field is
constituted for concatenating two values: COMPONENT_NAME and
INSTANCE_NAME. The default for INSTANCE_NAME is "instance1" and the
default for COMPONENT_NAME is "coreapplication_obis1". The
INSTANCE_NAME value can be set / customized by environment variables.
Resolution
The issue can be resolved by
applying following steps
Edit the usage tracking table
S_NQ_ACCT in the database and set the field NODE_ID to a size of 50 instead of
15.
In the Physical layer at the RPD,
set the field NODE_ID in the table S_NQ_ACCT to a size of 50 instead of 15.
Backup the file opmn.xml under
Middleware_home/instances/instance1/config/OPMN/opmn
Edit opmn.xml and add the
following under:
<ias-component
id="coreapplication_obis1" inherit-environment="true"> <environment>
For example, add the following
<variable
id="INSTANCE_NAME" value="kngdc5000_instance1"/>
Save the file and restart OBIEE
services then retest the issue by executing following SQL
Select NODE_ID from S_NQ_ACCT;
Result - kngdc5000_instance1:coreapplication_obis1
I hope you find this
information useful.
Thanks a lot.
ReplyDeleteVery helpful