Populating OBIEE Event Polling Table via Trigger

Posted on April 30, 2013 by Paul McGarrick
Filed Under BI Apps, ETL

DatabaseWhen using Oracle BI Applications there is often the need to invalidate OBIEE’s query cache once new data has been loaded into the Data Warehouse to prevent users from seeing stale data in the reports.

Perhaps the simplest and most elegant method to do this is to make use of the standard Event Polling Table mechanism.

With this decision made and the table set up the final part of the jigsaw is how to insert data into the polling table once the ETL has completed. There are a number of ways to do this including:

1. Editing each Informatica Workflow to include post-ETL SQL insert statements
2. Configuration of new tasks within the DAC to handle inserts
3. Triggers in the database (Oracle only) to automate the inserts

Method 1 involves too much work as it will potentially involve editing hundreds of workflows and the associated overhead to maintain this code. Method 2 would be a good solution but again involves a lot of configuration effort. I decided on Method 3 as it will be largely ‘plug in and forget’ and should be easy to set up.

First I created a new table to hold the names of those Warehouse tables which I want to be able to purge from the OBIEE cache. This is a simple 1 column table, nothing more.


The next step was to populate this new table with the names of the required tables. This you can do manually via your own scripting.

The final step is to add a trigger to the DAC metadata table W_ETL_REFRESH_DT. It is into this table that a refresh date gets stamped once the ETL completes. The trigger will fire every time the table is changed and if the refresh date is not null then it will insert a row into the polling table. Here’s the code for the trigger (note that the event polling table column names and values should match your environment):

VALUES (1, SYSTIMESTAMP, 'Oracle Data Warehouse', 'Catalog', 'dbo', T.TABLE_NAME, NULL);

I hope this helps someone out there.


Performance Testing/Tuning OBIEE

Posted on February 27, 2013 by Paul McGarrick
Filed Under OBIEE 11g, Performance

100Recently I’ve been heavily involved with Performance Testing and Tuning a new OBIA system for a client. I thought I’d share some of the interesting reading I’ve come across. I hope to write a more detailed post on the topic in the near future but in the meantime…

Lots of good material available from Jeff McQuigg here including recent posts on keeping things simple and time series tuning.

A good presentation by Robin Moffatt from a past Rittman Mead BI Forum.

More from Robin, this time on the myth surrounding query logging.

A useful presentation from Peak Indicators.

Some interesting reading on tuning your JVM garbage colletion. There really is a lot of bad advice on the web concerning this topic and it seems a few forum posters are keen to post their ‘must use’ JVM settings. I will try and find time to explore this topic in detail in a future posting.

Finally, a really useful Oracle White Paper “OBIEE 11g Infrastructure Performance Tuning Guide” is available on My Oracle Support – see document ID 1333049.1 once you’ve logged in.

1 Comment

BI Publisher Best Practices

Posted on November 26, 2012 by Paul McGarrick
Filed Under BI Publisher

Here’s a useful presentation I found the other day on the Oracle website.

BI Publisher Best Practices

Leave a Comment

Images with Relative Path not Exported to PDF

Posted on November 26, 2012 by Paul McGarrick
Filed Under Export, Skin

Just a quickie…

We ran into an interesting situation today when exporting some dashboard contents to PDF. It seems the PDF conversion mechanism can’t reproduce images when they are referenced with a relative path.

The fix was simple: refer to the images using the fmap syntax.

Leave a Comment

5 BI Publisher Blogs

Posted on November 26, 2012 by Paul McGarrick
Filed Under BI Publisher

It occured to me the other day that I’ve never posted anything concerning BI Publisher. To address that I thought I’d share a few of the better blogs on the subject that I’ve come across over the years.

Happy BI Publishing…

1 Comment

Informatica: Impacted Sessions Error

Posted on October 4, 2012 by Paul McGarrick
Filed Under BI Apps, Informatica

I came across an interesting error when trying to run a customised Informatica Workflow. It failed and this message appeared in the log file:

“The Repository Service marked the session or session instance as impacted, and the Integration Service is not configured to run impacted sessions”

It seems that the Session called by the Workflow had not been refreshed since its parameters were last updated. The fix was simple: Check out the Session within Workflow Manager, right-click it within the Task Developer and choose Refresh Mapping. Finally validate the session and check it back in.


Leave a Comment

Action Link text is Exported to Excel

Posted on September 5, 2012 by Paul McGarrick
Filed Under Action Framework, Export, OBIEE 11g

On a recent customer project I came across an interesting scenario when trying to export a report to Excel. The report in question contained Action Links (invoking a Browser Script) and when exported, the Action Link text and associated hyperlink appeared as an extra row beneath each data cell.

After checking with Oracle Support it seems this is expected behaviour (!) and not a bug (although why anyone would want this is a mystery to me). See 1450819.1 for more detail.

I devised a workaround by creating a duplicate version of the report without Action Links. This duplicate is then exposed via a custom ‘Export’ hyperlink on the dashboard using a bit of JavaScript to invoke the standard OBIEE export function. The syntax was like this:

<a onclick=”NQWClearActiveMenu();Download(‘saw.dll?Go&amp;Action=Download&amp;ItemName=Example%20Report&amp;path=%2fshared%2fPath%2fto%2freport &amp;Format=mht&amp;Extension=.xls’);” href=”javascript:void(null);”>Export</a>

If you plan on using this then be sure to URL encode the path and filename correctly.

Hopefully this will help someone arriving here via a search engine.



Briefing Book Table of Contents Page

Posted on September 5, 2012 by Paul McGarrick
Filed Under Briefing Books, OBIEE 11g

Quick tip…

You can define the front page of PDF Briefing Books by editing toc-template.rtf found in this directory:


This is useful if you need to apply a company logo or set a corporate font. Sadly there’s no way to apply a header/footer to every subsequent page.

Leave a Comment

New Oracle BI EE Tutorial

Posted on August 30, 2012 by Paul McGarrick
Filed Under OBIEE 11g, Tutorials

Oracle have added another BI EE tutorial to their Learning Library. This one is based on the latest version ( with BP1) and covers how to create and customise Analyses use them to build Dashboards. This should be useful for anyone new to OBIEE, particularly as an end-user rather than a system administrator.

See the tutorial in the Learning Library.

See also: related Total-BI posts

1 Comment

Communication error with the OPMN server local port

Posted on July 10, 2012 by Paul McGarrick
Filed Under OPMN, Unix/Linux

I ran into an interesting problem the other day when trying to start OBIEE components via OPMN. I kept getting the following message:

“Communication error with the OPMN server local port”

After a lot of head scratching I figured out that somehow OPMN had been started as root user (this was a Unix system) rather than the application installer/owner. I was able to confirm this by running the following command which also gave the PIDs in order to kill the processes (there will be two of them):

ps -ef | grep 'opmn -d' | grep -v 'grep'

I hope this helps someone else in the same situation.


keep looking »