OBIEE 11G: Refresh user GUIDs

You most probably have to refresh the user GUIDs

  • After a migration from Dev to Prod (or whatever environment)
  • After a server crash
  • After the OBIEE partition got full
  • After having unexplained problems to signing in

This is an expected operation after a migration of the entire catalogue. I don’t really understand why the GUIDs has to be refreshed after a crash (AFAIK, they don’t change), but I had to do it so many times in this situation to be able to log in again.

The task cannot be done using the Weblogic Console neither Weblogic Enterprise Manager. It requires to manually edit 2 config files on the server, bounce the services, remove the edited lines from the config and bounce the services again.

Edit NQSConfig.INI

The file is located in the Oracle BI Server config folder:


  • Locate the parameter FMW_UPDATE_ROLE_AND_USER_REF_GUIDS and change its value to YES
  • Save and Close the file

Edit instanceconfig.xml

The file is located in the Oracle Presentation Server config  folder:


  • Locate the XML tag <Catalog>
  • Add the following tag within the <Catalog> tag: <UpdateAccountGUIDs>UpdateAndExit</UpdateAccountGUIDs>
  • Save and Close the file

It should looks like:

Now restart all the services. BI Presentation should fail to start but should not throw errors.

You can now undo all the modifications you made to both config files and restart all the services. Et voilà ! You did refresh the user GUIDs.


OBIEE 11G: Display hierarchy and columns from the hierachy at the same time

One of the newest feature of 11G is the possibility to expand/collapse hierarchy level (yep.. even in 2014 it seems to be a killer feature…heh!). The problem is you can’t display the hierarchy and the column used to build the hierarchy at the same time with a table. The hierarchy will be OK but the columns will display null value.

Suppose the Hierarchy Year -> Month -> Week -> Day

Add the hierarchy AND the columns used to build it like this:

Hierarchy OBIEE 11g

As you can see, Month and Week are empty. This is caused by the Hierarchy.


OBIEE Hierarchy Result

If you need to display the value, there is a hack. You only need to use the _whatever_ function with the columns ! The less intrusive is to append an empty string at the end.
OBIEE Hierarchy Hack


et voilà !


Download and setup OBIEE 11G Usage Tracking (

Activating USAGE TRACKING with OBIEE 11G ( is not an easy thing to do. The tutorial provided by Oracle is incomplete and contains a lot of errors. So to make your life easier, you can download a fully compatible Usage Tracking repository for OBIEE, the Usage Tracking Catalog and the Installation SQL Script.


Quick Notes on these files:

To create the repository, I downloaded one from but I had to tweak it a bit to make it compatible with

  • A new Column (ID VARCHAR(50) NOT NULLABLE) in table S_NQ_ACCT
  • All columns in S_NQ_ACCT are now NULLABLE (except for the colums ID ans CACHE_IND_FLG)
  • Many foreign keys were broken so i recreated it
  • The join in the “Measure” logical table have been rebuilt

The SQL Script is the 4 scripts you have to run on your [prefixed]_BIPLATFORM schema (the one created with the rcu setup) t. I have appended them in the good order and I

  • removed some statements that cause errors (2 drop table on tables that don’t exist)
  • added 1 missing CREATE VIEW.

I found the catalog on my server (OBIEE_HOME/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/sample/usagetracking). This is an archive you can import with the Unarchive feature in Catalog Manager.

  • I had to rewrite the filters of every analysis
  • The first page name “Dashboard” is the only dashboard page provided; I created the others..
  • I wrote a few analysis and added it to the pages I made

I created the new dashboard pages because I don’t like to have just a bunch of links.. I recommend to create your own analysis and add it to a new page like I did.

The last thing to do is to activate Usage Tracking within Oracle Enterprise Manager. You can follow the step by step procedure provided by Oracle at  (section Configuring Usage Tracking in Enterprise Manager)

Hope this help.