[OBIEE 11g] Usage Tracking

OBIEE11G USAGE TRACKING

Setting Up Usage Tracking in OBIEE

This Document will used to set usage tracking and create usage reports to monitor queries

Step1: Can use available User  in  SQL schema otherwise create new one with all permissions.

CREATE USER OBIEE_USAGE_TRACKING IDENTIFIED BY OBIEE_USAGE_TRACKING

Step 2: Create the tracking table.The Tracking table Script can be found in

D:\bi11g\instances\instance3\bifoundation\OracleBIServerComponent\coreapplication_obis1\schema\SAACCT.Oracle.sql

Run the script S_NQ_ACCT which is in SAACCT.Oracle.sql from the  user

Step 3: Make it available for the public

GRANT SELECT ON S_NQ_ACCT TO PUBLIC;

Step 4: Setting up the additional tables

You can find the scripts in D:\Obiee\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\SQL_Server_Time

Run the following scripts;

Oracle_create_nQ_Calendar.sql (To create table)

Oracle_create_nQ_Clock.sql (To create table)

Oracle_nQ_Calendar.sql (To Insert data into table)

Oracle_nQ_Clock.sql (To Insert data into table)

Step 5: 

Make it available for the public

GRANT SELECT ON S_ETL_DAY TO PUBLIC;

GRANT SELECT ON S_ETL_TIME_DAY TO PUBLIC;

Step 6: 

Create an extra view

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

Step 7: 

Make it available for the public

GRANT SELECT ON  nq_login_group TO PUBLIC;

Step 8:

Merge the usage tracking RPD into your master_Rep RPD.

Open your master RPD offline.

then Save as "master_temp.rpd"

Click yes for Global Consistency

From the file menu select merge

Then  select

Original Repository: master_Rep.rpd

Modified Repository: usagetracking_11115_BI0002 

along with the Repository Passwords

Note:  Can get the correct usagetracking RPD and catalog from the following

https://docs.google.com/open?id=0B5hhXh5H6a8CM09WZXY5czdSckNJTnVscnJLVkk3dw

https://docs.google.com/open?id=0B5hhXh5H6a8COV9OdDIwbWhRQUNrdTlmSnRHNVZxUQ

then click on Next

Then click Next

Choose Modified option from Decision 

Then Click on Finish option

Click No option for Global Consistency then Merged Rpd will be 

Step 9: Upload this new merged RPD and Catalog in the Enterprise Manager (EM)

Step 10: OBIEE 11g Usage Tracking Server Configuration

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

[ USAGE_TRACKING ]

ENABLE = YES;

DIRECT_INSERT = YES;

PHYSICAL_TABLE_NAME = "OBI Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT";

CONNECTION_POOL = "OBI Usage Tracking"."Usage Tracking Writer Connection Pool" ;

BUFFER_SIZE = 10 MB ;

BUFFER_TIME_LIMIT_SECONDS = 5 ;

NUM_INSERT_THREADS = 5 ;

MAX_INSERTS_PER_TRANSACTION = 1 ;

Note: PHYSICAL_TABLE_NAME = “<Database>”.”<Schema>”.”<Table>”                 

          CONNECTION_POOL =“<Database>”.”<Connection Pool>”

f) Save and close the NQSConfig.ini configuration file (Run the services if required using Opmnctl)

g)  Copy the Usage Tracking Presentation Catalog into the existing Presentation Catalog and Extract it

Merge  Master Catalog and Usagetracking Catalog using Catalog Manager 

1.  Go to Oracle Business Intelligence > Catalog Manager to lunch Catalog

2.  Open Catalog  - File >> Open Catalog , either in Online or Offline mode , provide the path, login credentials and click on OK.

3.  Once the catalog manager is expand the catalog and then select the “shared folder” as shown below  and then go to File >> select Archive 

     4.  A popup window appears. Click on “Browse” and then provide name you want to save this archive and then click on “open”. Once it is done name appears as shown and then click on “Ok”.

  

    5.  Once it is successful click on “Ok” and a file will be created in the folder shown.

         

OBIEE HOME >> instances >> instance4 >> bifoundation >> OracleBIPresentationServicesComponent >> coreapplication_obips1 >> catalogmanager >> mycatalog_archive 

Archived file is shown below

     Un-Archive – Catalog

    1.  Open Catalog  - File >> Open Catalog , either in Online or Offline mode , provide the path, login credentials and click on OK.

2.  Once the catalog manager is expand the catalog and then select the “shared folder” as shown below  and then go to File >> select unarchive

3.  Click on Browse and select the file which you want to un-archive and then finally click on OK. Once done successfully click OK.

4.  Refresh the Analytics browser

Login into User Interface(UI) and the check that the Usage Tracking Dashboard is added