Usage Tracking in OBIEE

Post date: 13-Apr-2010 06:19:13

How to enable usage tracking in OBIEE ?

How to perfrom usage audit in OBIEE ?

How to set you Direct Insertion to Collect Information for Usage Tracking ?

Introduction

Oracle BI server supports collection of usage statistics which can use in a variety of ways such as who is running what and when ? database optimization, aggregation strategies, figuring out most used reports, billing departments as per their usage etc etc.

Goal

We want to achieve a reporting for usage in OBIEE. One such sample report is attached. Lots of analysis similar to this can be achieved using the Usage Tacking.

How it works?

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

There are two ways to achieve Administration of usage tracking.

• Setting Up Direct Insertion to Collect Information for Usage Tracking

• Setting Up a Log File to Collect Information for Usage Tracking

Oracle Recommendation: Use direct insertion to write statistics to a database table.And I am going to discuss that method here.

Steps to Set Up Direct Insertion to Collect Information for Usage Tracking:

Set 1 : Create a necessary tables to collect usage tracking information.

A. Click here to download SAACCT.Oracle.sql or

Go to \\OracleBI\server\Schema and run the script  SAACCT.Oracle.sql to create table S_NQ_ACCT. This table will hold all the usage tracking information.

B. Go to \\OracleBI\server\Sample\usagetracking

This folder has necessary time series table creation script  and build in repository for usage tracking.

Create a tables using scripts:

Oracle_create_nQ_Clock.sql and Oracle_create_nQ_Calendar.sql

Load data in above tale using  Oracle_nQ_Clock.sql and Oracle_nQ_Calendar.sql script.

Step 2 Import  S_NQ_ACCT into the repository.

Please note: in order for Usage tacking to work properly , one must have to include this table in physical layer.

Step 3  Creating a business and presentation layer.

We will use a repository provided at C:\OracleBI\server\Sample\usagetracking\UsageTracking.rpd So that we want have to create a repositoy from ground zero.

Click  UsageTracking.rpd to down load the usage tracking repository.

Step 4 Change configuration file.

This is most important step in setting up usage tracking.

Go to \\OracleBI\server\Config and open NQSConfig.INI.

We have to make the change in [ USAGE_TRACKING ] portion of the config file.

Change DIRECT_INSERT                  = YES;

Along with this we have change the connection properties as well.

–PHYSICAL_TABLE_NAME

–CONNECTION_POOL

Change below parameter for better insertion performance and insertion control

–BUFFER_SIZE

–BUFFER_TIME_LIMIT_SECONDS

–NUM_INSERT_THREADS

–MAX_INSERTS_PER_TRANSACTION

Please refer below screen shot for more detail.

Note : Any typo will hold server from starting.

Step 5 Check results to see every things is working.

Copy repository UsageTracking.rpd to OracleBI\server\Repository\

make Star = UsageTracking.rpd, DEFAULT; in NQSconfig file.

Restart BI server. If server fails to start check possible typo in NQSConfig

OBIEE usage tracking setup and cloning reports with CAF

Usage tracking repository and catalog setup

Finally I did usage tracking setup in OBIEE. There is a good doc/example in Oracle by example sitehttp://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/usage_tracking/usage_tracking.htm#t1 in John Minkjan's bloghttp://obiee101.blogspot.com/2008/08/obiee-setting-up-usage-tracking.html and Rittman Meadhttp://www.rittmanmead.com/2008/10/11/comparing-obiee-usage-tracking-with-nqsquerylog/. Also there are many sources of this information on other sites/bloges.

I will use my SH user to create usage tracking tables into it.

Use SAACCT.Oracle.sql to create table S_NQ_ACCT. Script can be found in ..OracleBI\server\SchemaScripts folder. After that jump into D:\OracleBI\server\Sample\usagetracking\SQL_Server_Time. There are some scripts that we need to start.

Decription of S_NQ_ACCT columns (description of usage tracking data) is available in Oracle Business Intelligence documentation Oracle Business Intelligence Server

Administration Guide in the table 42 on the page 406.

Run scripts:

Oracle_create_nQ_Calendar.sql (create S_ETL_DAY)

Oracle_create_nQ_Clock.sql (create S_ETL_TIME_DAY)

Oracle_nQ_Calendar.sql (insert into S_ETL_DAY)

Oracle_nQ_Clock.sql (insert into S_ETL_TIME_DAY)

We make these tables public:

GRANT SELECT ON S_ETL_DAY TO PUBLIC;

GRANT SELECT ON S_ETL_TIME_DAY TO PUBLIC;

Now we need to merge usagetracking.rpd into our current rpd online.

I copy/paste my current online rpd and save it as ofline. This repository I'll open at first and also I make a copy of it, and this copy is only for OBIEE to allow me to merge (this is required although they are the same).

We get the message that repositories are identical.

Now, we add usagetracking.rpd as modified repository.

We merge information from usagetracking.rpd to our tecaj.rpd.

After merging ignore delete failed message.

A new repository has been created.

Save new merged repository and owerwrite original tecaj.rpd.

We need to put some changes to OBI Usage Tracking database folder, connection pools in the physical layer and also in NQSConfig.ini in ..\OracleBI\server\Config.

I set my SH user to connection pools Usage Tracking Writer Connection Pool and Connection pool. The first is for writing and the second just for reading.

Don't forget to create vie NQ_LOGIN_GROUP:

create 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;

Changes in NQSConfig.ini:

Before merging usage tracking catalog (reports) into our current catalog online, make sure that you copy/paste your new merged repository into ..OracleBI\server\Repository folder and owerwrite the old one.

For merging usage tracking reports I'll use Content Accelerator Framework (CAF) clone option. I'll not explain procedure for installing Content Accelerator Framework (CAF), you can find it on thehttp://www.oracle.com/technology/products/bi/pdf/oraclebiee-cafv1-usage-instructions.pdf.

In ..OracleBI\server\Sample\usagetracking there is a UsageTracking.zip. If you unzip it you'll get usage tracking catalog.

Open usage tracking catalog ofline and merge it to our current online web catalog. You'll need to open two instances of catalog manager application.

Copy/paste this path to catalog:

Copy usage tracking reports and dashboards from shared folder:

Paste it to a web catalog online shared folder:

You'll se usage tracking in online web catalog shared folder. We did a usage tracking reports and dashboards migration in online web catalog.

Cloning reports with Content Accelerator Framework (CAF) V1

Now, open Content Accelerator Framework (CAF) V1 and try to clone these usage tracking reports just for test.

We use copy of online repository:

Clone all usage tracking reports:

The same repository file we are using for source and the target repository because we just want to copy/clone reports:

We choose usage tracking subject area and make required object mappings:

You can change the name of the target report, add yome prefix, etc. Cloned reports are in the cloned folder:

Check the Answers to see various analysis available for Usage Tracking.

Once should be able to see the reporting as shown below.