Dynamic SQL against BI Server Data Source

Officially a public holiday for us at Oracle today but for the rest of the world and some folks here in the good ol' US of A, a great write up from Andy and his team at Beyond Systems on handling dynamic sql requirements for BIP when connecting to BI Server.
Just the intro to 1. whet your appetite and 2. for those of you wondering what the heck Im talking about:

Recently while working on a high profile BI Publisher project we encountered a rather interesting
challenge. The organisation that we were working for are very forward thinking, having adopted OBIEE
as their corporate BI solution they had fully bought into the idea of having a single point of truth. This
architecture meant that OBIEE server would be the single data source. BI publisher is a great product
however it quickly became apparent to us that we had lost the ability to write dynamic SQL through the
usual technique of creating a data template and executing a package procedure in the before report
data trigger to change the SQL. Now this in itself was not a complete show stopper but the effort
required to write a single all inclusive query, then filter the data and conditionally display it in the RTF
template was going to be considerable. With this in mind we decided to do some research and came up
with this alternative solution which we hope will help others adopt the OBIEE server architecture with BI
Publisher. The beauty of this solution is that several reports can condensed into a single report which
significantly reduces the development effort and gives the user a much simpler solution.

Document available


Thanks Andy and team, really appreciate the time you took to write it up and allow me to share it.


Hi Tim, We did this research 16 months back and implemented this solution in our environment. Glad we were right :) ~Vetri

Posted by Vetri on January 19, 2010 at 02:31 AM MST #

Great article as for me. It would be great to read something more concerning this matter. Thnx for posting this data.

Posted by Marcus on February 08, 2010 at 05:39 AM MST #

Hi, We are using Publisher to extract reports from the database. In this we are uisng the data template option wherein we are calling a stored procedure as a trigger which will populate a table in the database. In the same report we are enabling the burst option (burst to file) but sadly it gives me the following error oracle.apps.xdo.servlet.scheduler.ProcessingException: java.lang.StringIndexOutOfBoundsException: String index out of range: -1 at oracle.apps.xdo.servlet.scheduler.XDOJob.runBurstingReport(XDOJob.java:2116) at oracle.apps.xdo.servlet.scheduler.XDOJob.execute(XDOJob.java:358) at org.quartz.core.JobRunShell.run(JobRunShell.java:195) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520) Caused by: java.lang.StringIndexOutOfBoundsException: String index out of range: -1 at java.lang.String.substring(String.java:1768) at oracle.apps.xdo.batch.BurstingProcessorEngine.applyElementBreak(Unknown Source) at oracle.apps.xdo.batch.BurstingProcessorEngine.globalDataEndElement(Unknown Source) at oracle.apps.xdo.batch.BurstingProcessorEngine.endElement(Unknown Source) at oracle.xml.parser.v2.XMLContentHandler.endElement(XMLContentHandler.java:210) at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1318) at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:336) at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:303) at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:263) at oracle.apps.xdo.batch.BurstingProcessorEngine.burstingRequest(Unknown Source) at oracle.apps.xdo.batch.BurstingProcessorEngine.process(Unknown Source) at oracle.apps.xdo.servlet.scheduler.XDOJob.runBurstingReport(XDOJob.java:2008) I have been googling this error for quite sometime and still havent found any solution. I read somewhere that, I should include the bursting query as one of the datasource. I tried doing that, so I have two data sources now, one is the data template (as mentioned above) and another datasource which has the Bursting query, but i get "Data source reference "" can not be found. Please select new data source reference." just below the data source option. Any Help would be appreciated, and sorry if I have posted in the wrong post.

Posted by Harry on February 08, 2010 at 08:25 PM MST #

A very important factor in online bussiness is to outperform the competitors. If you go to http://www.bh-server.com/seo they have a $10 special on website analysis, this normally sell for about $300. I would get it while they are giving it away.

Posted by Tresa Fiumara on February 22, 2010 at 10:30 AM MST #

I realise this may sound nasty, but I honestly don't agree with the title Dynamic SQL against BI Server Data Source (Oracle BI Publisher Blog) . I believe it is a awful insult to human kind. I think you should surely be more tolerant next time. However I have to say, that your writing is brilliant. Sincerely, Audrie Lorenz

Posted by Audrie Lorenz on August 16, 2010 at 05:01 AM MDT #

I'm really upset about the recent setbacks hindering the repeal of the Don't Ask, Don't Tell policy! I'm confident that it will ultimately be gone, though.

Posted by Whip My Hair on September 25, 2010 at 08:17 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!


« August 2016