Business Intelligence – Oracle

Archive for August 14th, 2007

Usage Tracking in OBI EE

Posted by Venkatakrishnan J on August 14, 2007

I have had quite a few customers asking for ways to determine who is accessing what in OBI EE. As a matter of fact, OBI EE provides an easy way to do usage tracking. There are 2 methods to achieve this.

1.                  Having a usage tracking table and OBI EE would do direct inserts into this table whenever someone is accessing any of the components within OBI EE.

2.                  Enable tracking via writing to log files.

The 1st one is the most recommended option since all that one would need is a schema to hold this table. And this is very lightweight and does not consume too much of resources.

In order to set this up, one would have to go through a small set of configuration steps. They are

           

1.                  Run the create table scripts under the {BIInstallDir}/server/schema directory eg. SAACCT.Oracle.sql

2.                  Check whether the schema from which you ran the above script indeed has the table of name S_NQ_ACCT.

3.                  Open the NQSConfig.ini and go to the usage tracking section and enable it. For example, use the following as a sample

[ USAGE_TRACKING ]

ENABLE                         = YES;

DIRECT_INSERT                  = YES;

PHYSICAL_TABLE_NAME            = “BIDB”.”BISE1_SALESWH”.”S_NQ_ACCT” ;

CONNECTION_POOL                = “BIDB”.”BIDB” ;

BUFFER_SIZE                    = 10 MB ;

BUFFER_TIME_LIMIT_SECONDS      = 5 ;

NUM_INSERT_THREADS             = 5 ;

MAX_INSERTS_PER_TRANSACTION    = 1 ;

There are 2 parameters that need explanation.

PHYSICAL_TABLE_NAME parameter requires 3 values to be specified.

BIDB (eg)                        Points to the name of the Database module within the Physical layer of   the current repository.

BISE1_SALESWH                        Points to the schema of the database in which we ran the script in step 1.

S_NQ_ACCT                                    Table that collects the statistics.

One would have to import the S_NQ_ACCT table within one of the database modules for the usage tracking to work. The above is the specification for an Oracle database. For other databases, do refer the documentation.

The CONNECTION_POOL parameter requires 2 values to be specified.

“BIDB” (eg)                        Points to the name of the Database module within the physical layer of   the current repository.

“BIDB”                        Points to the name of the connection pool within BIDB database module.

 

ut1.jpg

 

 

Of course all of this is documented and one can access those from here. The S_NQ_ACCT table has the following structure.

 ut2.jpg

Quite a lot of stats are available out of the box for end users to report on. There is also a usage tracking repository under BIHome}/Server/Samples/Usage Tracking. One can use this repository and merge it to the work repository so that one can do usage-tracking analysis on the dashboards.

Advertisements

Posted in All Posts, OBI EE Plus | 11 Comments »