Dynamic SQL against BI Server Data Source
By Tim Dexter on Jan 18, 2010
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.
Thanks Andy and team, really appreciate the time you took to write it up and allow me to share it.