Usage Tracking in OBIEE 11g

Hi All,

While Implementing Usage Tracking in OBIEE 11g,I came to know that the given default RPD and Catalog is a bug in

So updating you all with correct RPD and Catalog for implementing Usage Tracking in OBIEE 11g.

you can get the correct RPD and catalog from here.

Step by Step procedure of Implementing Usage Tracking in OBIEE 11g:

Usage Tracking:

Used to monitor system and ad hoc query performance and to provide input on usage details.  Used for determining user queries that are creating performance, based on query frequency and response time.Usage tracking Presentation Catalog provides prebuilt requests and a dashboard built to provide common analysis—for example, analyzing  usage patterns by users, groups, and queries; daily and weekly peak usage patterns and load variance;time series comparisons to help compare current usage over previous weeks or months; and major contention points for troubleshooting, including, for example, top long-running queries.

Before implementing Usage Tracking we need to run the following scripts.

Step 1 :

Create the following sql scripts:

  • Oracle_create_nQ_Calendar—Create table scripts
  • Oracle_create_nQ_Clock—Create Table Scripts
  • Oracle_nQ_Calendar—Insert Scripts
  • Oracle_nQ_Clock—Insert Scripts
  • Also create S_NQ_ACCT table
  • We need to even create NQ_LOGIN_GROUP view also using the following syntax:

CREATE OR REPLACE VIEW nq_login_group AS SELECT DISTINCT user_name AS login, user_name AS resp FROM s_nq_acct;

Grant select on nq_login_group to public;

We can get the scripts from Middleware/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/sample/usagetracking/SQL_Server_Time

We can get the S_NQ_ACCT table from


Note : Don’t forget to commit the changes after creating the tables and inserting the data.

Step 2 : We can get the upgraded Usage Tracking RPD and Catalog from the below path after installing OBIEE 11g.


As mentioned it is a bug so you can get the RPD and Catalog from the above mentioned links.

Step 3 : Upload the RPD and Catalog in the Enterprise Manager (EM) to make it online.

you can get how to upload RPD and Catalog in to EM from

Step 4 : Open the RPD in online mode and change the Usage Tracking Writer Connection Pool settings to your database credentials.

Update the row count and check whether data was populating or not.

Step 5: OBIEE 11g Usage Tracking Server Configuration:

To configure the server configuration parameters, perform the following steps:

a)  Open Middleware\instances\instance1\config\OracleBIServerComponent\

coreapplication_obis1\NQSConfig.ini in Notepad and edit.

b) Next, set the usage tracking options. Scroll to the Usage Tracking section. Set Enable = Yes; to enable usage tracking.

c) In the section that lists parameters used for inserting data into a table, set the

PHYSICAL_TABLE_NAME = “OBI Usage Tracking”.”Catalog”.”dbo”.”S_NQ_ACCT” ; 

d) The table name is the fully qualified name as it appears in the Physical layer of the Server Administration Tool. The general structure of this parameter depends on the type of database being used. For Oracle, the structure is PHYSICAL_TABLE_NAME = “<Database>”.”<Schema>”.”<Table>”.]

e) Set the CONNECTION_POOL parameter to specify the connection pool to use for inserting records into the usage tracking table. The setting is CONNECTION_POOL = “OBI Usage Tracking”.“Usage Tracking Writer Connection Pool”. Leave the default settings for BUFFER_SIZE, BUFFER_TIME_LIMIT_SECONDS, NUM_INSERT_THREADS, and MAX_INSERTS_PER_TRANSACTION. Your configuration file should look like this:

The connection pool name is the fully qualified name as it appears in the Physical layer of the repository.

f)Save and close the NQSConfig.ini configuration file.

Step 6 : Login to analytics http://<hostname&gt;: <port >/analytics/  by using Administrators credentials.

Check the Usage Tracking dashboards .

By bidevatapraveenkumar Posted in OBIEE 11g

BI Publisher shows Blue Page

We recently faced an issue while opening BI Publisher it shows only blue page,the content is not displaying.


Step 1 : : Login to answers page http://<hostname&gt;: <port >/analytics/ by using the credentials.

Step 2 : Go to Administration –> Manage privileges–> Access to SOAP.

Here the permissions should be given to BI Consumer Role.

Now we are able to see the BI Publisher.

By bidevatapraveenkumar Posted in OBIEE 11g

Column Header Mouse Over In OBIEE 11g

You can set Mouse Over functionality over column header of the table.

Step1: Go to column format and check custom heading.

Step2: Add html tag like <b title=”DNAME”>DNAME</b>

Step3: Go to instanceconfig.xml.

Step4: Edit it and add tag – <HardenXSS>false </HardenXSS> in the Server Instance.

Step5: Restart the Services.

Now we can see the Mouse Hover for Column Heading.

By bidevatapraveenkumar Posted in OBIEE

Log Files in OBIEE 11g

Log Files in OBIEE 11g:


In Enterprise Manager Console you can find the Most Recent Errors and Most Recent Warnings. After warning messages, you can also see all the log files grouped by categories using Log Viewer.

Available files:

• Presentation Services Log
• Server Log
• Scheduler Log
• JavaHost Log
• Cluster Controller Log
• Action Services Log
• Security Services Log
• Administrator Services Log

The log files for OBIEE components are found under /instances/instance1/diagnostics/logs

Different type of log files:

Presentation Server log files:

Files: sawlog0.log, webcatupgrade0.log
BI Server log files:
Files: nqquery.log, nqserver.log, servername_NQSAdminTool.log, servername_nQUDMLExec.log, servername_obieerpdmigrateutil.log
Scheduler log:
Files: nqscheduler.log

Cluster Controller log:
Files: nqcluster.log
ODBC log:
OPMN log:

Files: debug.log, logquery~NUMBER.log, opmn.log, opmn.out, service.log

By bidevatapraveenkumar Posted in OBIEE

How to see log level from Presentation Services

Step 1 : Login to answers page http://<hostname&gt;: <port >/analytics/ by using the credentials.

Step2 : Click on new and select a Subject Area, pull any column from the Subject Area.

Step 3 : Go to Edit Formula (fx) of the column and go to variable–>Session Variable now give the value as log level in the server variable and click ok.

Step 4 : Now click on results then we can see the log level which we set in the UI.

By bidevatapraveenkumar Posted in OBIEE

No Log Found error in OBIEE 11g:

When you try to take the query from the Administration Tab we used to get an error like No Log Found.

In order to see our query we have to set the log level to 7 in RPD.

Setting Log Level in RPD:

We have to check the Logging Level in three places in RPD.

Step 1 : Open RPD by using the credentials.

Step 2 : Go to Tools–>Options–>Repository

Here set the Log Level to 7 in System Logging Level.

Step 3 : Go to Manage–>Identity–>weblogic and set the Logging level to 7 here.

Step 4 : Sometimes we used to define the Log Level in Initialization Blocks.

Go to Variables–>Session–>Initialization Blocks–Log Level.

Edit the Data Target and set the Log Level to 7.


By bidevatapraveenkumar Posted in OBIEE 11g