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.
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.