Usage Tracking

Oracle BI Server provides a functionality of Usage Tracking to trace the execution of a Logical SQL in a log database table, as this logs query statistics to a database table and allows you to run reports against them, with low response cost. Oracle BI server supports collection of usage statistics which can use in a variety of ways such as who is running what and when?, Lots of analysis can be achieved using the Usage Tacking.

When we enable usage tracking, statistics for every query are inserted into a database table or are written to a usage tracking log file. If you use direct insertion, the Oracle BI Server directly inserts the usage tracking data into a relational database usage tracking table.

In order to set up usage tracking, we need a usage tracking table, Oracle BIEE provides a sample of Usage Tracking table scrip and a sample usage tracking .rpd file.

1. Create a database into data source; Give a name OBI_Usage_Tracking for better understanding, and give public access.

2. Create the tracking table,

The tracking table script comes up by default with OBIEE 10(10.1.3.4 ) installation and can be found in this location

OrcaleBI\Server\Schema use SAACT script to create a table with name S_NQ_ACCT into your database OBI_Usage_Tracking. (Create table script is available for db2, Oracle DB, Teradata and MSSQLDB, choose one script for your database to create table S_NQ_ACCT)

(This guide covers Enabling Usage tracking using MSSQL server 2005 examples)

3. Set up additional tables required for usage tracking.

These additional table scripts also come up by default with OBIEE 10 installation and can be found in this location :\OracleBI\server\Sample\usagetracking\SQL_Server_Time

Run the script form below files

1.SQLServer_create_nQ_Calendar   to create table S_ETL_DAY

2.SQLServer_create_nQ_Clock      to create table S_ETL_TIME_DAY

3.SQLServer_create_nQ_UserGroup to create view NQ_LOGIN_GROUP for table S_NQ_ACCT

4. SQLServer_nQ_Calendar         to insert data into table S_ETL_DAY

5. SQLServer_nQ_Clock            to insert data into table S_ETL_TIME_DAY

See below screen shots for view details of path and scripts location

4. Check out the database, tables and data. (Note: - The table S_NQ_ACCT should be empty after creation)

4. Now locate the Usage Tracking sample RPD file and place it into repository catalog.

5.  Open Usage Tracking rpd using Admin Tool and have a review.

6. There are two ways to implement and process usage tracking in OBI.

6. A. Using sample Usage Tracking RPD provided by OBI Server,(Merge with prebuilt  reporting or master RPD)

6. B. Importing Usage Tracking Metadata (Schema and Tables) into prebuilt reporting or master rpd and create physical model, business model and Presentation Catalog on our own

The well recommended approach is enabling and implement usage tracking with UsageTracking sample rpd, so that you don’t need to import and create model for usage tracking metadata.

6.1) Create an ODBC connection for Usage Tracking database to connect Usage Tracking RPD metadata, and this connection also can be used for import metadata from this database for method 6.B. 

6.2) Now open the prebuilt reporting master rpd,which is consistent and you want to track usage.

6.3) Now click on File Menu Option to make a copy of this rpd with different name to recognize

6.4) A new rpd get create with the same Metadata as master rpd.

6.5) Now go to file menu option and select Merge to merge this repository with original master repository  and usagetracking repository.

\

6.6) Select original master repository from Select Original repository window and click open.

Enter password if it’s given or click ok in open offline window.

6.7) Click on the Select button for Modified Repository Section.

6.8) Select Usage Tracking sample Repository in modified repository window and click open.

Enter password if it’s given or click ok in open offline window.

6.9) Check put required rpds are associated to merge click on Merge button.

If you will get any message like below, that will not cause your queries and rpd metadata. You can ignore i.t    

6.10)  A new verion of  repository  is created which is built form Master repository and Usage Tracking  sample repository 

Check global consistency and verify the usage tracking metadata into all layers.Now in this current repository you will get two Physical, Business models and two presentation catalogs, that is different from your reporting master repository.

6.11) Check the connection pool settings for OBI_USAGE_TRACKING database and change both the connection pool setting as shown in below screen shot.

Here “Connection Pool” is used for getting data from database and “Usage Tracking Writer Connection Pool” is used for write back into database after query processing for both metadata.

Change the connection pool data source name, username and password as shown in above screen shot. For both connection pools of OBI Usage Tracking database.

Do not change connection pool setting for master repository metadata.

6.12) Update All row counts to check the connection is proper or not.

6.13) Now go to NQSConfig file at this location :\OracleBI\server\Config to ebable usage tracking

Open the file and locate Usage Tracking section, see the highlighted values.

6.14) Now change the highlighted parameters as below screenshot.

(***This connection pools parameters for MSSQL Server 2005 data source)

6.15) Configure the rpd name into NQS configue file to access into answers, Start the services and go to answers.

Login with different users to track usage and make request with different users,

6.16) Select subject area which belongs to master repository and create a request.

6.17) Check the result

6.18) Now go to OBI_USAGETRACKING database to check the new entries into S_NQ_ACCT table after query made by  OBI Server.

6.19) Go back to answer again and make a request from Usage Tracking subject area.

6.20)Check reslut For all users will show the result.

6.21) Check the query log to verify your result, is it coming from database.

6.22) You can request more query about usage tracking for more detail, using Usage Tracking subject area.

6. B. Importing Usage Tracking Metadata (Schema and Tables) into prebuilt reporting or master rpd and create physical model, business model and Presentation Catalog on our own. 

Following Usage Tracking sample rpd, create same model and process your queries as following above steps.

You need to create two connection pool manually by copy and paste, one is for query and one is to write back into database else is same as sample usage tracking rpd.