Sunday, December 29, 2013

How-to: Upgrade Usage Tracking OBIEE 10g to OBIEE 11.1.1.x

8:52 PM

Share it Please
Oracle has provided plenty of documentation on how to upgrade your OBIEE 10g environment to their new 11g platform. They've even outlined the process of configuring a new instance of Usage Tracking on a new 11g environment - outlined here. What I haven't been able to find is the ability to take an existing Usage Tracking environment in OBIEE 10g, and port it over to the new 11g environment. The upgrade assistance tool won't work because the data model for Usage Tracking has actually changed between the two versions.  This guide is going to cover the process of:

  • Taking an existing OBIEE 10g Usage Tracking environment 
  • Making the necessary changes to the Usage Tracking data model
  • Applying the configuration changes in weblogic to enable Usage Tracking in OBIEE 11g

Step 1: Make changes to existing OBIEE 10g Usage Tracking RPD

The data types and nullable check box have changed for nearly every column in Usage Tracking's primary table: S_NQ_ACCT. Using the Admin Tool, modify each column as outlined below:



Step 2: Modify Physical Data Model

I've noticed on some engagements that the NQ_LOGIN_GROUP table may not always be deployed. I don't think this is an 'out of the box' table but rather something manually deployed to track the applicable group of an individual user. Note that as you move to the application role based security model you'll no longer need this table. In the event that you do need this backwards compatibility, you'll need to fix the broken link - which is made evident by the red line that appears when you attempt to display the physical data model:

You'll need to make the physical join on NQ_LOGIN_GROUP.LOGIN = S_NQ_ACCT.USER_NAME

Step 3: Enable Usage Tracking in Enterprise Manager

It's at this step where Usage Tracking implementation differs dramatically between OBIEE 10g and OBIEE 11g. In the 10g platform, Usage Tracking was enabled by modifying the nqsconfig and instanceconfig files, but with the advent of Oracle's Enterprise Manager you must now go through EM's MBeans configuration to enable the tool.  To Oracle's credit, they did provide a detailed guide on how to enable Usage Tracking in EM . Rather than re-invent the wheel, i'm going to defer everyone to the section titled 'Configuring Usage Tracking in Enterprise Manager'. We're past the 'OBIEE 10g' upgrade so Oracle's guide is more than appropriate.

Step 4: Deploy Usage Tracking Tables to Your 11g Database

Most implementations use the DEV_BIPLATFORM that come pre-populated with the S_ETL_DAY, S_ETL_TIME_DAY and S_NQ_ACCT tables, but if your version did not, or you're not using the default schema, run the following scripts located at the following directory:
instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\
  1. Oracle_create_nQ_Calendar.sql
  2. Oracle_create_nQ_Clock.sql
  3. Oracle_nQ_Calendar.sql
  4. Oracle_nQ_Clock.sql

Step 5: Confirm Usage Tracking is Enabled


I've created a sample report with the Usage Tracking subject area as outlined below:

 
When viewing the log generated by the query, you'll see that a query is generated against the Usage Tracking connection pool and no error is thrown!

 

keywords: obiee 11g, usage tracking,  upgrade usage tracking , enterprise manager
 

38 comments:

  1. Hi John,

    2 things:

    1.) NODE_ID is - in the newest incarnations (11.1.1.7.x) - a VARCHAR2(50) rather than 15 in order to prevent node IDs like "devobiee:coreap" being stored in the S_NQ_ACCT.


    2.) Even if it's just an extremely small step, a lot of attempts to migrate UT run into problems at the ID column (unique index S_NQ_ACCT_PK constraint...). A 5sec way around this:

    create or replace function OBIEE_BIPLATFORM.OBI_MD5 (input_str in varchar2)
    return varchar2
    as
    begin
    return(DBMS_OBFUSCATION_TOOLKIT.md5 (input_string => input_str));
    end;

    drop table OBIEE_BIPLATFORM.TMP_NQ_ACCT purge;
    create table OBIEE_BIPLATFORM.TMP_NQ_ACCT as
    SELECT
    S.CACHE_IND_FLG, S.COMPILE_TIME_SEC, S.CUM_DB_TIME_SEC,
    S.CUM_NUM_DB_ROW, S.END_DT, S.END_HOUR_MIN,
    S.END_TS, S.ERROR_TEXT, S.IMPERSONATOR_USER_NAME,
    S.NODE_ID, S.NUM_CACHE_HITS, S.NUM_CACHE_INSERTED,
    S.NUM_DB_QUERY, S.PRESENTATION_NAME, S.QUERY_BLOB,
    S.QUERY_KEY, S.QUERY_SRC_CD, S.QUERY_TEXT,
    S.REPOSITORY_NAME, S.ROW_COUNT, S.SAW_DASHBOARD,
    S.SAW_DASHBOARD_PG, S.SAW_SRC_PATH, S.START_DT,
    S.START_HOUR_MIN, S.START_TS, S.SUBJECT_AREA_NAME,
    S.SUCCESS_FLG, S.TOTAL_TIME_SEC, S.USER_NAME,
    rawtohex(OBIEE_BIPLATFORM.OBI_MD5(rownum)) as ID
    FROM 10gINSTANCE.S_NQ_ACCT S;

    Cheers,
    Christian

    ReplyDelete
  2. this article nice ,this information is very useful to me and our company obieeonlinetraining

    ReplyDelete
  3. Everything is fine, am happy about your blog. Thanks admin for sharing the unique content, you have done a great job I appreciate your effort and I hope you will get more positive comments from the web users.

    Regards,
    Aamala

    SEO Training in Chennai

    ReplyDelete
  4. Thank you for sharing such a nice story with us. I really enjoyed very much And please keep updating like this with this site.

    SAP MM Training in Chennai

    ReplyDelete
    Replies
    1. I have read your blog its very attractive and impressive. I like it your blog.

      Digital Marketing Company in Chennai Digital Marketing Agency

      Delete
  5. Such as very good information are provided and easily understand all given information,thanks for your valuable support.
    sap basis training in chennai

    ReplyDelete
  6. Great explanation to given on this post and i read our full content was really amazing,then the this more important in my part of life.
    The given information very impressed for me really so nice content.
    digital marketing training in chennai

    ReplyDelete
  7. Another interesting articles and i find more new information,i like that kind of information,not only i like that post all peoples like that post,because of all given information was very excellent.
    ios training in chennai

    ReplyDelete
  8. I read your articles very excellent and the i agree our all points because all is very good information provided this through in the post.
    It is very helpful for me.
    Oracle SQL Training in Chennai

    ReplyDelete
  9. There are lots of information about hadoop have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get to the next level in big data. Thanks for sharing this. Hadoop Training in Chennai | Big Data Training in Chennai

    ReplyDelete
  10. Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care and we take your comments to heart.As always, we appreciate your confidence and trust in us.
    digital marketing company in chennai

    ReplyDelete
  11. The site was informative and inspiring. Thanks a lot Superb post, very good article, well written and very informative.SAP Simple Finance Training in Pune

    ReplyDelete
  12. Great information shared in this blog. Helps in gaining concepts about new information and concepts.Awsome information provided.Very useful for the beginners.
    Dot net Training in Chennai

    ReplyDelete
  13. Very interesting content which helps me to get the in depth knowledge about the technology. To know more details about the course visit this website.
    hadoop training in chennai | Big Data Training in Chennai

    ReplyDelete
  14. Thank you for sharing such a nice and interesting blog with us. Hope it might be much useful for us. keep on updating...!!
    seo company in india
    digital marketing company in india

    ReplyDelete
  15. This blog explains the details about changing the ways of doing that business. That is understand well and doing some different process. Provides he best output of others. Thanks for this blog.
    Digital Marketing Company in Chennai
    SEO Company in Chennai

    ReplyDelete
  16. Hi, Really your post was very informative. Today's internet era learn Hadoop Online Training will helps you to reach your goal.Selenium Training

    ReplyDelete
  17. Nice sharing. R is a language and environment for statistical computing and graphics. Want to make a career in R Programming. Learn R Programming Training course @ GangBoard. We are the best provider of online training on evergreen technologies.

    ReplyDelete
  18. You have done really great job. Your blog is very unique and informative. Thanks. Devops Online Training | Data Science Online Training

    ReplyDelete
  19. We share this information is very unique.I like that's our extraordinary content.I would be like that's we share this blog post.
    Selenium Training in Chennai | Selenium Training Institute in Chennai

    ReplyDelete
  20. Thanks for posting this blog i in reality loved it and put up some particular blogs approximately oracle........visit our internet site associated with oracle fusion procurement training
    for more information
    oracle fusion procurement training

    ReplyDelete
  21. Thanks for sharing useful information for us.I really enjoyed reading your blog, you have lots of great content,we provide low price of fee for on-line coaching.
    Oracle fusion financials training

    ReplyDelete
  22. enjoy happywheelsdemo.in unblocked
    happy wheels happywheelsgame.inPlay Free sudoku hearts minesweeper

    freecell spades yahtzee msn free games

    ReplyDelete
  23. oracle fusion financials online training at erptree.com is worlds best online training center. we have excelled in knowledge sharing through online. we have almost all country students as our subscribers for online course. 10+ years of experience we have in handling various ascent people.
    we have user friendly website where you will be provided with all the required details and Self-paced DEMO videos


    Oracle Fusion HCM Coaching Center

    ReplyDelete
  24. Hi,
    Great information shared in this blog. Helps in gaining knowledge about new information and concepts. Awesome information provided.Very useful for the beginners.
    thank you,
    Oracle EBS Online training

    ReplyDelete
  25. Thanks for sharing this useful information and that's great one.
    Film editing course in chennai

    ReplyDelete
  26. Oracle Fusion financials online training institutes is available at calfre.com, you just need to provide your interested coaching center location and course you prefer to learn with this information we can provide you the institutes list which are top rated in that particular area. thank you


    Oracle Fusion Financials online Training

    Oracle fusion Financials Trainin

    ReplyDelete
  27. Very Useful information that i have found. donot stopand Please keep updating us..... Thanks

    ReplyDelete
  28. Good post..Keep Sharing.! I'm working in brave technologies private limited. We are the leading low cost ERP software development company in chennai.

    ReplyDelete

  29. I really appreciate your post. Thanks for sharing such an useful information.
    Dissertation Writing Services

    ReplyDelete