Saturday, November 3, 2012

USAGE Tracking in OBIEE 11.1.1.6.4 (or) Above

Hi Friends,

Recently I was trying to setup usage tracking in OBIEE 11.1.1.6.4 as in OBIEE 10G.

If you wan to set up simple Usage Tracking follow this oracle learning library Article.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi1116/ut/ut.html

If you want to setup usage tracking as in 10G. Follow as below.

 
Steps 1: 

Paul has already took the 10G RPD and upgraded it to 11.1.1.5 RPD and made the tweaks necessary so that  the S_NQ_ACCT meets the new table structure.

http://total-bi.com/2011/09/obiee-11g-usage-tracking-rpd/.

I just took the RPD  and used obieerpdmigrateutil.exe utility to upgrade it to 11.1.1.6.4.

How to upgrade the RPD using obieerpdmigrateutil.exe :

1) I copied the paul's RPD in to  a folder in the c:\ called "up"

1) open your  OBIEE clients installation folder  and click on " BI Command Prompt"

using client tools you don't have to set environment variables  to use obieerpdupgradeutil.exe. When you click BI command Prompt , it will initiate the environment variables.





Syntax format for the obieerpdmigrateutil.exe  is as follows


After the bin > enter as follows


obieerpdmigrateutil - I C:\up\Usage Tracking.rpd  -O c:\up\11g.rpd -L c:\up\text.LDIF -U Administrator

Now we got the upgraded RPD. 

Important note : There is one new column "ID" added to usage tracking in obiee 11.1.1.6We need to import the new column and add it to the  S_NQ_ACCT table. 

Otherwise you will get the following error : 

 [2012-11-03T13:02:58.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: fea5681cb2e6e3a0:-586c3f5d:13ab9179653:-8000-0000000000015bc2] [tid: 11599700]  [nQSError: 17011] SQL statement execution failed. [[
[nQSError: 17001] Oracle Error code: 1400, message: ORA-01400: cannot insert NULL into ("DEV1_BIPLATFORM"."S_NQ_ACCT"."ID")
 at OCI call OCIStmtExecute: INSERT INTO S_NQ_ACCT (USER_NAME, REPOSITORY_NAME, SUBJECT_AREA_NAME, NODE_ID, START_TS, START_DT, START_HOUR_MIN, END_TS, END_DT, END_HOUR_MIN, QUERY_TEXT, QUERY_BLOB, QUERY_KEY, SUCCESS_FLG, ROW_COUNT, TOTAL_TIME_SEC, COMPILE_TIME_SEC, NUM_DB_QUERY, CUM_DB_TIME_SEC, CUM_NUM_DB_ROW, CACHE_IND_FLG, QUERY_SRC_CD, SAW_SRC_PATH, SAW_DASHBOARD, SAW_DASHBOARD_PG, PRESENTATION_NAME, IMPERSONATOR_USER_NAME, NUM_CACHE_INSERTED, NUM_CACHE_HITS)  VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29).

  
step2:   I just took the 10G Catalog and used the upgrade assistant to upload the 10G usage tracking to 11g usage tracking catalog.


To save others time I just have the RPD and Catalog available for you to download from box which is compatiable with the OBIEE 11.1.1.6.0 and above 

PASSWORD FOR RPD "Administrator"

 


Step 2:  You have to enable the usage tracking in the Enterprise manager.

1) Login to Enterprise Manager  

a) select coreapplication from Business Intelligence and click on " Lock and Edit"

b) Click on web logic > bi foundation> right click on  bifoundation and select  " System Mbean Browser>  Application Defined Mbean > oracle.biee.admin > Domain : bifoundation_domain >
BIDOMAIN.BIINSTANCE.ServerConfiguration

as shown below.

  
In the Right side you will see the following fields.






 at 18 > Give your connection pool details
  at     20>  change  it to true
at     21 > give your S_NQ_ACCT  table  details.

Restart the services in the Enterprise Manager. That's it. usage Tracking is all set.


Step 3: After you merge the  Usage  tacking catalog with your 11G catalog  you will come across the following error  OBIEE 11g: Unresolved table: "**NONE**". (HY000) on each report.

Issue  : You have a saved filter called  "Subject Area not eqaul to /is not in usage tracking ". that filter is a faulty one

Just follow the thing as said in the blog below to fix the issue.

http://www.oraclenerd.com/2011/09/obiee-11g-usage-tracking.html


Thanks
Venkat

2 comments:

  1. Done!!!... Updated the password... I missed it somehow......

    Thanks
    Venkat

    ReplyDelete