Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« Mitigating Low User Adoption in Sales Automation | Main | OAC Essbase Application Migrations from On-Premise »

Automating the Purge Job for Usage Tracking Data


Why Usage Tracking?

Usage Tracking will be helpful in measuring and monitoring user interactions with the OBIEE. This will provide the deep understanding into usage statistics and performance congestions in the reporting application.

Usage tracking functionality creates entry into S_NQ_ACCT table as when a report is executed in the application by a user.

This table will capture metrics like report performance time, report start/end time, user ID etc.,

When Usage tracking is enabled, it helpful in determining which user queries are creating performance bottlenecks, based on query response time.

It also provides information on frequently accessed reports. It involves in Enterprise Manager set up changes and RPD changes.

Why Automate the Data Purge?

For a reporting application which receives user requests every minute, Usage Tracking will generate huge volume of data. This data gets written in S_NQ_ACCT database table. Purging data in this table periodically is essential. Otherwise reports created on top of usage tracking data would perform slowly. Manually purging this data requires intervention from database team and add overhead in application maintenance.

We can automate data-purging in S_NQ_ACCT table using BI Publisher technology. This automation will work for any data-purging. Also the entire automation can be done with technology stack that exists with BI application. There is no need to involve any additional tools.

Steps to Automate:

  1. Create a BI Publisher data source with READ WRITE user for OBIEE meta-data schema.

  2. Create a database package which deletes records from S_NQ_ACCT table.

  3. Create a BI Publisher data-model to invoke the DB package via Event Trigger.

  4. Create a scheduled job which will invoke the data-model periodically.


  1. Create a BI Publisher Data Source


Go to BI Publisher administration, Click on JDBC connection as shown below.

Click on "Add Data Source"





Enter the following details for a New Connection

     Data Source Name: Give a Data source name.

    Username: Enter the user name who have Read and write access to OBIEE meta schema to access the data source.

     Password: Enter the password associated with the user name.

Click on Test Connection, a confirmation message will be displayed.

  1. Create a Data Model to know how many records got purged.

    Go to New TabàData Model





Enter the following details

Default Data Source: Select the Data source which is created in above step from dropdown.

Oracle DB Default package: Enter package name which is created in the database in the OBIEE Meta schema.


Attached the package code for Reference.




Click on Data Sets and select SQL Query as shown below.


Enter the following Details

Name: Enter a Data set name

Data Source: Select the newly created Data source from the dropdown

Write the Query and click on OK.

Query: SELECT COUNT(1) num_deleted_records FROM APLINST02BI_BIPLATFORM.S_NQ_ACCT WHERE start_dt < SYSDATE - (:m)


Create an Event Trigger to initialize the Data model after Report gets triggered by the Scheduler.

Enter the Event trigger details as below

Name: Enter the name of the Event Trigger

Type: After Data

Language: PL/SQL

Oracle Default package will be populated automatically. Select the appropriate function which will trigger the Report from the Available function section and move to the Event Trigger section by Click on ">" icon


Now click on Parameters and provide the parameter details to pass it in Event trigger. We are passing the number of days with this parameter to purge the data from the S_NQ_ACCT table with below logic.

DELETE FROM Schema_Name.s_nq_acct WHERE start_dt < sysdate - m;




  1. Create a RTF template for Scheduling a Job to automate

Go to NewàReport















Click on Upload

Rtf Document for reference.





Save the report in the required folder after providing the above details.

Now click on MoreàSchedule

Enter the parameter value as 'm'





Enter the details for scheduler as below

Name: Give a name of the file name

Layout: This will be populated automatically

Format: Select a required format of the Report form the dropdown

In the Destination section, select the Destination as Email or FTP and provide the details accordingly.

In the Schedule tab, give the frequency of the Job when to run.


Now click on Submit Job from top right corner. A job will be scheduled as per given details below

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles