Monday, August 11, 2008

OBIEE Setting up usage tracking

OBIEE has a very good usage tracking system, with low resource costs. Here is how you set up the Oracle variant.

Step 1: create a oracle schema:

CREATE USER obiee_usage_tracking IDENTIFIED BY "OBIEE_USAGE_TRACKING"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE DEFAULT
QUOTA UNLIMITED ON "USERS";
GRANT "CONNECT" TO obiee_usage_tracking;
GRANT "RESOURCE" TO obiee_usage_tracking;
GRANT CREATE SESSION TO obiee_usage_tracking;
GRANT CREATE TABLE TO obiee_usage_tracking;
GRANT CREATE VIEW TO obiee_usage_tracking;
ALTER USER obiee_usage_tracking DEFAULT ROLE NONE;

Step 2: Create the tracking table.

the Tracking table Script can be found in: ... \OracleBI\server\Schema
Use SAACCT.Oracle.sql for Oracle and put it in the relevant OBIEE_USAGE_TRACKING schema.

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 ...\OBIEE\OracleBI\server\Sample\usagetracking\SQL_Server_Time

Run the following scripts;
Oracle_create_nQ_Calendar.sql
Oracle_create_nQ_Clock.sql
Oracle_nQ_Calendar.sql (Warning this only goes to 2016!)
Oracle_nQ_Clock.sql

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 5b: Create an extra view
(They forgot this one in the documentation)

CREATE OR REPLACE 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;

Step 6: Merge the usage tracking RPD into your master RPD.

Open your master RPD offline.

Save as "master_temp.rpd"
From the file menu select merge.



Select you original master.RPD
Select the modified repository from ...\OracleBI\server\Sample\usagetracking\ Password is empty
Click MERGE If you get this one don't worry about it, just click ok
Save the merged RPD as your master.rpd

Step 7: Move the Usage Tracking to a native connection pool If you ommit this and use ODBC you can get "strange" errors.
Import 1 table from the OBIEE_USAGE_TRACKING schema.

Delete only the table and copy the connection pool

Drag and drop the table part from the imported Usage tracking rpd.

Rename the databasebase an connection pools

Check the connection pool data
Be sure to set both connection pools!



Step 8: Altering the NQSConfig.ini

The file can be found in: …\OracleBI\server\Config

Locate and Alter the following:


###################
# Usage Tracking Section
# Collect usage statistics on each logical query submitted to the
# server.
###################
[ 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 ;

Step 9:
Restart the BI server. Check the server log for errors.

Step 10: Import the Usage Tracking Presentation Catalog into the existing Presentation Catalog. Extract the example catalog from the ZIP file found in ...\OracleBI\server\Sample\usagetracking\



Open one instance of the Oracle Business Intelligence Catalog Manager. Open the source Presentation Catalog from where we can copy the Usage Tracking content.



Open this Presentation Catalog in offline Mode.

Once you have opened the presentation catalog, click on the shared folder. In this folder you can find the folder Usage Tracking, which holds all related reports, filters and the dashboard page.


Open another instance of the Oracle Business Intelligence Catalog Manager. Open the destination Presentation Catalog in online mode to which you want to copy the Usage Tracking content to. Open the shared folder. As you can see in the example below, this presentation catalog doesn’t have the Usage Tracking folder yet.


Go back to the source Presentation Catalog for the Usage tracking. Click on the Usage Tracking folder and click on Copy button in the upper left of the screen.


Go to the destination presentation catalog. Be sure the shared folder is opened. Right click in the folder window and click on Paste. Close both catalog managers.

Note: Some people are worried that they don't see the entry directly in the reports. This is because OBIEE saves the usage tracking inserts them in a batch. The amount is influenced by the BUFFER_SIZE parameter. This specifies the amount of memory used to temporarily store insert statements. The buffer allows the insert statements to be issued to the usage tracking table independently of the query that produced the statistics to be inserted. When the buffer fills up, then subsequent queries’ statistics are discarded until the insert threads service the buffer entries. When you stop the BI-server it will try to issue an insert all remaining entries.


Till Next Time
This article is also published on: http://knowledge.ciber.nl/weblog/

Murat did some great extra comments:

36 comments:

Anonymous said...

Thanks a lot..i followed all the steps mentioned in this blog for usage tracking.Everything is working fine..

John Minkjan said...

You are welcome,

regards
John

Anonymous said...

Very useful document. Thanks a lot !

OBIEE_learn said...

Thanks john. Excellent article...Followed step by step and it just worked. Appreciate ur contribution.

Amin said...

It was a very useful document.

I found one issue with this.
CREATE OR REPLACE VIEW nq_login_group AS SELECT DISTINCT user_name AS login, user_name AS resp FROM s_nq_acct;

Due to the way the view was created as above, I am not able to get the correct group name (resp) of the user (login) from Answers. In this case, both the user and group name (RESP) are same.

What approach should be taken to get the group name(s) of the user as given in the RPD?

Thanks
Amin

John Minkjan said...

Hi Amin,

This is a historical feature, the user groups arent logged here. If you use external table authothencation you could use that to join to the user groups.

regards

john

Anonymous said...

I can't understand what and where move, copy since step 7. It is not clear for newbie..

Anonymous said...

sorry, just step 7 isn't clear

John Minkjan said...

Step 7 is done because you need a sperate xconnection pool with "write" priviliges. ODBC and OBIEE aren't supported when used for Usage Tracking.

Regards John

Roman said...

This solution has bugs.

In table s_nq_acct I have extra records for some reports with logical sql like: "{call NQSGetLevelDrillability".
Columns QUERY_SRC_CD, SAW_SRC_PATH, PRESENTATION_NAME are empty.
This multiplies statistics of reports usage and what worst: every entry with NQSGetLevelDrillability has SUBJECT_AREA_NAME always the same - first presentation catalog in the list.

Another bug. When you execute report with measure drillable atribute from dimension - the record is like above described. It means you do not have proper statistics for such a queries.

John Minkjan said...

@Roman,

Did you raise an SR with Oracle on these bugs?

Regards

John

Roman said...

No, I think this solution is not supported by Oracle?

John Minkjan said...

@roman,

What make you say that? This solution is conform the Oracle Docs.

regards

John

Anonymous said...

We're seeing a strange bug now that we've upgraded to 10.1.3.4.1. It seems the meaning of the the USER_NAME and RUNAS_USER_NAME have been swapped when doing an "Act As". Has anybody else noticed this?

Anonymous said...

Hello All,
I was able to get Usage Tracking configured and running. However, I noticed that the 'Report Name' that invokes the requests is not in the report. With so many reports running against the repository, how do you identify the 'Named Report' for the request that is running longer than you think it should.

Thanks,
Michelle

hafizah said...

hi John,
Do you have any documentation on setting up OBI Disconnected Analytics?Do you have an idea on how to solve my problem as i mention as below:
When I open Disconnected Analytics Manager, I don’t see any
application name in the drop down and start, synchronize,
delete, get server info buttons are all disabled .. what needs
to be done? [Some body has written that an administrator should
create Disconnected Analytics applications on server and make
them available for users. I have made those in the server(i.e. I
have put rpd, webcat and .sql files in
C:\SiebelAnalytics\Disconnected\xx\app and put mobile
application configuration file (.xml)in
C:\SiebelAnalytics\Disconnected\xx but not getting how to make
them available for users.)But as synchronize, get server info
buttons are disabled in disconnected client machine I am
confused how to connect to server from the client machine.

John Minkjan said...

@hafizaf,

I've never worked with disconnected analytics, try the OTN forums.

Regards John

Thilak said...

Hi John,
Is it possible to track the failed login in Usage tracking.
Like, If a user enters a wrong password and authentication fails. I need this also to be inserted in the tacking table.

Thanks,
Thilak

John Minkjan said...

@thilak

Try quering the SAWlog files.

Regards

koen said...

Very complete post - thanks !
Koen

Anonymous said...

HELLO ALL,

i was getting the following error when i was trying to query about usage tracking-
[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: 984 message: [Oracle][ODBC][Ora]ORA-00984: column not allowed here.

[nQSError: 16015] SQL statement execution failed.

Please help me.

Thank you in advance.

John Minkjan said...

@Anonymous,

did you check the log to see if the right connection pool is used?

regards

John

Anonymous said...

hello john,

thank you for the quick response.

I am a newbie to this field. I think the connection pool i created was in accordance with the oracle tutorial and there were no errors about connection pool in log files. in the results section of bi answers it was indicating no results, it wasn't showing any errors..

Marina said...

I can understand step 7. When i copy one table from OBI Usage Tracking the Administration Tool don't create a folder like in your images.
Could you explain most well how i do step 7? (Sorry for my english)

John Minkjan said...

@Marina,

Can you mail me a screenshot of picture you get? John dot Minkjan at ciber dot nl

regards

John

Marina said...

I can solve that problem. :) Now my problem is other, when i try to show this information in a dashboard, it gives me an error like "column requested does not exist in table". If you can help, thanks.

Marina said...

In Administration Tool in physical layer when i try to view data from, for example, S_NQ_ACCT, gives me the same error.

Jerry Ursetti said...

I am very new to all of this and appreciate the info on sites like this. This is very thorough but I'd like to know where I can find more information on the *ETL* tables. I found information on S_NQ_ACCT and basic info on setting up usage tracking in the OBI Server Admin Guide. I also found the OBE doc, which goes into more detail. But I cannot find any detailed information about the S_ETL_DAY, S_ETL_TIME_DAY and NQ_LOGIN_GROUP tables. Do these tables ever need to be updated? How? My Oracle_nQ_Calendar.sql has dates to 2016. What if I'm still doing this in 6 years? My Oracle_nQ_Clock.sql seems to only have entries for April 14, 2000. Is the date irrelevant?

Thanks.

Jerry

John Minkjan said...

@Jerry,

In 2016 hopefully Oracle will have released OBIEE 11g. If not ask you ETL person to extend the script for the calendar. The date in the time table is not used, only the time part.

regards

John

Akash said...

Hi John,

First of all thank you for such a nice document.

I was just trying to implement Usage Tracking SA and found that if you double click OBI Usage Tracking and change the database to oracle 10g/R2/11g from SQL Server 7.0/2000 which comes by default then you need not do step 7, you can directly change the call interface to oci 8i/9i. Do you see any problem in this.?

Karthik said...

Thanks John,
Akash, I was struggling with the error "Oracle Error code: 984, message: ORA-00984: column not allowed here"
I was able to resolve the error by changing the Connection pool properties as suggested by you, it works!!...Thanks again!

Cecilia said...

Hello, i already got usage tracking dashboard then when i choose one of query that available there, this error occured:

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46036] Internal Assertion: Condition m_CountFields == static_cast(m_ColumnNameVector.size()), file .\Src\SQCSCacheStorageListStream.cpp, line 221. (HY000)
SQL Issued: SELECT Users."User Name" saw_0 FROM "Usage Tracking" ORDER BY saw_0

John Minkjan said...

@Cecilia,

Looks like a caching issue, did you switch it off?

obi said...

This is a very useful blog.Thanks !
My requirement is to identify Max Concurrent users through Usagetracking
I need help implementing the logic in OBIEE

SELECT MAX(ConcurrentUsers)
FROM (SELECT a.StartTime, COUNT(*) AS ConcurrentUsers
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.StartTime <= a.StartTime
AND b.EndTime >= a.EndTime WHERE a.StartTime <= a.Starttime + 1/24
OR a.Logouttime >= a.StartTime
GROUP BY a.StartTime) AS x
(This calculates the Max Conc Users from a start time to start time + 1 hour)
Here my table will be S_NQ_ACCT
How can I perform an Innerjoin on a Dashboard??

Please help !!

rtaraka said...

Thanks John.

Anonymous said...

Do you know if caf is available also for OBIEE 11g?