Business Intelligence – Oracle

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.

About these ads

11 Responses to “Usage Tracking in OBI EE”

  1. [...] Usage tracking in OBIEE [...]

  2. Sid said

    Venkat, nice article. Is there a way to store physical queries instead of logical queries in those columns in S_NQ_ACCT table? Alternatively, is there a way to translate any logical query into physical query without passing it through presentation servers (i.e. without defining the logical SQL in reports/dashboard/answers)?

  3. Venkat,

    Nice job. I have performed a successful merge of the Usage Tracking with my existing custom (non-siebel) repository. I can load the subject area just fine but when I execute any queries in the application I receive the following error;
    04-08 15:38:26

    [59048] Usage Tracking encountered an insert statement execution error. This error has occurred 5 times and resulted in the loss of 5 insert statements since this message was last logged.

    [nQSError: 16001] ODBC error state: S1000 code: 1861 message: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string.

    [nQSError: 16015] SQL statement execution failed.

    It appears since we do not have Siebel Seed data to populate our S_ETL_DAY tables we are getting this error. Should these S_ETL_* tables just be removed from the physical layer? If so than the S_NQ_ACCT will basically be standalone with the aliases. Is that all I need to at least see something that will write to the tables and populate at least something in answers?

    Thanks

  4. Peter Ikuobase said

    Venkat,

    I have set up Usage Tracking in Oracle BI Enterprise Edition with the Direct Insert Option. I have created the S_NQ_ACCT table in an Oracle schema that is referenced as a connection pool in the Oracle BI Repository physical layer. When I run my requests from Oracle BI Answers and check my server logs (NQServer.log), I get the following message:

    —————————————————————————————————————————————————
    [59048] Usage Tracking encountered an insert statement execution error. This error has occurred 9 times and resulted in the loss of 9 insert statements since this message was last logged.
    [nQSError: 16001] ODBC error state: S1000 code: 1456 message: [Oracle][ODBC][Ora]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction.
    [nQSError: 16015] SQL statement execution failed.
    2008-04-25 04:34:18
    [59048] Usage Tracking encountered an insert statement execution error. This error has occurred 9 times and resulted in the loss of 9 insert statements since this message was last logged.
    [nQSError: 16001] ODBC error state: S1000 code: 1456 message: [Oracle][ODBC][Ora]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction.
    [nQSError: 16015] SQL statement execution failed.
    ————————————————————————————————————————————————————————–
    Do you know how to fix this problem?

    Regards,

    Peter

    • Trent said

      I got this too… the connection pool userid/password was different from the S_NQ_SCCT table owner “OBIEE_Usage_Tracking” I had created. I could have changed or used a different connection pool but I just granted insert on OBIEE_Usage_Tracking.S_NQ_SCCT to the connection pool userid.

  5. Nimish said

    Great post on usage tracking. I implemented it without any issues in half an hour. Thanks, folks. A quick and very pertinent question for many implementing usage tracking. Isn’t there a way to have the session id also logged onto S_NQ_ACCT file? This would be the only variable that would tell us how many distinct times a user has logged into the system.

  6. Sarat said

    Hi Venkat,

    We have customized the vaning llla OBIEE RPD in our project. Now we have a req to implement Usage Tracking into the existing vanilla.My question is, I did all the changes as specfied by you.But it throws an error saying that S_NQ_ACCT table doesnt have any join to logical table sources as this being the only table in the new schema. How do this table capture the stats without having any join to the existing RPD? Are the stats diff for RPD and for Webcat usage? Can I insert any other new column other than the existing columns in the S_NQ_ACCT table?

    Pls let me know at the earliest.This is pretty urgent req for us. Would appreciate your early response.

    Thx,
    Sarat

  7. Soren said

    Hmm. I got a database set up, and the table generated.

    But the direct insert fails with this error:

    [59048] Usage Tracking encountered an insert statement execution error. This error has occurred 1 times and resulted in the loss of 1 insert statements since this message was last logged.
    [nQSError: 16001] ODBC error state: S1000 code: 1861 message: [Oracle][ODBC][Ora]ORA-01861: litteral matcher ikke formatstreng.
    [nQSError: 16015] SQL statement execution failed.

    I am working on an Oracle 9i DB set up with a Danish locale. I suspect it has something to do with datetime conversion (by looking at the ora-01861 error)

    I’ve tried changing the locale for the bi server to Danish, and fooling around with the date and time formats in the ini file, but to no avail.

    Any bright ideas anyone?

    /Soren

  8. Soren said

    I got some good help from our local friendly oracle representative. I changed the connection pool to OCI in stead of ODBC, and the problems magically disappeared.

    /Soren

  9. Jan Kohoutek said

    You must “play” with Data Source Definition. Do not use ODBC but proper type of Oracle database.

  10. Hussain said

    Hi There: Anyone can please guide me thru the OCI or ODBC connection after first time installation. I install OBIEE on linux, Oraccle DB 11g already resides o the same server, then we started server and client sevices and tried to test the connection using nqcmd. Hours after hours, I was unable to figure what to pass as data source name, and where to setup data source name. I tried different option as were posted at different forums to edit odbc.ini, user.sh etc, but nothing worked.

    Regards,
    Hussain.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: