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:

/usr/local/OracleBI/instances/instance1/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI

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

/usr/local/OracleBI/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml

  • 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à !

HierarchyResult3

[nQSError: 42039] Columns in BY clause of REPORT totalling function must be in select list. (HY000)

I got this error today with OBIEE 11.1.1.7.0. Obviously, all my columns were in the select list. I found a few blogs and articles with people asking for help for that bug but none of them provided/found a solution. Here is mine.

The Error

[nQSError: 42039] Columns in BY clause of REPORT totalling function must be in select list. (HY000)

The symptoms

The error is thrown when sorting one specific column (a measure) of a Table with 14 columns ((5 measures, 7 dimensions columns including 2 hierarchies). All the others columns could be sorted without problem except one.

 The solutions

Uncheck “Report-Based Total (when applicable)” in the aggregation rule of your measure. That’s it !

OBIEE 11G Aggregation Rule

 

It worked for me !

Bash Script to easily start or stop OBIEE 11G on Linux

On linux, starting OBIEE requires to start

  1. Weblogic Server Admin Server
  2. Weblogic Server Node Manager
  3. Weblogic Managed Server
  4. Oracle Instance

All of the servers have to be running before starting the others. Looking at the log waiting for the running state to know when to start another service is a painful and time consuming task. So I wrote a little script. Laziness is sometimes a good thing :)

All you have to do is to modify the configuration variables section with your own values.

Enjoy !

Download and setup OBIEE 11G Usage Tracking (11.1.1.7)

Activating USAGE TRACKING with OBIEE 11G (11.1.1.7) 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 11.1.1.7, the Usage Tracking Catalog and the Installation SQL Script.

  

Quick Notes on these files:

To create the repository, I downloaded one from http://total-bi.com/2011/09/obiee-11g-usage-tracking-rpd/ but I had to tweak it a bit to make it compatible with 11.1.1.7.

  • 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 http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi1116/ut/ut.html  (section Configuring Usage Tracking in Enterprise Manager)

Hope this help.

References:
http://obiee101.blogspot.ca/2008/08/obiee-setting-up-usage-tracking.html
http://paulcannon-bi.blogspot.ca/2012/11/how-to-install-usage-tracking-in-obiee.html
http://adventuresinobiee.blogspot.ca/2013/02/how-to-enable-usage-tracking-on-obiee.html
http://total-bi.com/2011/09/obiee-11g-usage-tracking-rpd/
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi1116/ut/ut.html