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:
  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


  1. Hi John,

    2 things:

    1.) NODE_ID is - in the newest incarnations ( - 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
    return(DBMS_OBFUSCATION_TOOLKIT.md5 (input_string => input_str));

    drop table OBIEE_BIPLATFORM.TMP_NQ_ACCT purge;
    create table OBIEE_BIPLATFORM.TMP_NQ_ACCT as
    rawtohex(OBIEE_BIPLATFORM.OBI_MD5(rownum)) as ID


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

  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.


    SEO Training in Chennai

  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

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

      Digital Marketing Company in Chennai Digital Marketing Agency

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

  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

  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

  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

  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

  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

  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

  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

  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

  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

  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

  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

  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.

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

  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

  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

  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

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

    freecell spades yahtzee msn free games

  23. oracle fusion financials online training at 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

  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

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

  26. Oracle Fusion financials online training institutes is available at, 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

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

  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.


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

  30. I have read this content it is very nice with unique content and keep updating us.CCNA Training Institute in Chennai | CCNA Training Institute in Velachery.

  31. Hi, thanks for pretty post, keep more update magento 2 training

  32. Your website content nice nice and interesting to observe.
    jobbörse Neunkirchen

  33. Thank you for sharing the post, its a nice post and informative.
    Spoken english Training in Bangalore

  34. power washing services Virginia Thanks for sharing the information. That is very helpful for increasing my knowledge. Thanks for sharing the information.

  35. Hi, thanks for pretty post, keep more update.its a nice blog.
    Best IT Training in Bangalore

  36. Sap Training Institute in Noida-Webtrackker is the best SAP training institute in noida. SAP is drastically a completely state-of-the-art element ever to analyze and to excel in it. However with the ever-growing price, and being the most accessible organization solution key, agencies are greater involved to have their employees specialized in it. The education courses effectively deliver you with the fundamental recognition of SAP, in a grade by grade way.
    Sas Training Institute in Noida
    PHP Training Institute in Noida
    Hadoop Training Institute in Noida
    Oracle Training Institute in Noida
    Linux Training Institute in Noida
    Dot net Training Institute in Noida

  37. I really enjoy simply reading all of your weblogs. Simply wanted to inform you that you have people like me who appreciate your work. Web Designing Training Institute in Chennai | Java Script Training in Chennai| HTML & CSS Training in Velachery