BI Publisher Reports with Multiple Data Sources
April 24th, 2008 by Mark Rittman
Another new feature within BI Publisher that I noticed when looking through Borkur’s new BI Publisher course is support for more than one data source in a report. This was sort of possible with earlier (XML Publisher) releases of the product, but recent versions of BI Publisher have had full support for using more than one SQL query, Oracle BI Answers request or other data source in your report. I’ll just walk through how it works now.
Suppose you’ve got two data sources set up for BI Publisher; one is the demo data source that points through to the OE Sample Schema, another to Oracle BI Suite Enterprise Edition, so that we can bring in data from the BIEE repository or from Oracle BI Answers requests.
So now you go in to BI Publisher Enterprise and create your report and the initial dataset. This first dataset takes data from the OE schema (the “demo” datasource shown above) and runs a simple SQL statement against it.
I then repeat the process, creating a second data set this time against an Oracle BI Answers request. Now I have two data sets for my BI Publisher report.
Now comes the clever bit. I then go back to the Data Model node in the BI Publisher report tree, and change the report’s main data set to “Concatenated SQL Data Source” - ignore the SQL bit, any data source will work, but what this tells BI Publisher to do is to bring together all of the data source XML from each data set and combine it into one single XML source, which we can then make use of in our report template. One thing to note here is that in this particular example, I could only get the Template Builder in the next step to select from both data sources if I left the “Make row names unique” tickbox un-ticked - other times it has worked with this box ticked (which I guess is the recommended setting), I assume in the first instance though that my two data sources had non-unique row names - anyone know what the significance of this is?
Going in to BI Publisher Desktop, I connect to the BI Publisher server and create my first table. After selecting the report format, I can then select between the two data sets to provide the data for my table.
Using this feature I can then put one report template together that uses two different data sets, which when previewed, looks like this:
Not bad. Again, thanks to Borkur for working out the mechanics.
April 24th, 2008 at 5:36 am
Mark,
Great example and this is the most common way I see multiple data sources being used. I often get asked how to link the two queries. The easiest way is to link them is with a common parameter. However, sometimes the link needs to be more complicated. (Oracle Reports developers are very used to doing this.) This is also possible, but requires the use of data templates (http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/b40017/T421739T434255.htm#3893356). Data Templates provide the ability to do multiple queries and define links between them. They also allow you to define your XML output structure and call before and after report triggers.
April 25th, 2008 at 11:17 am
Looks useful Mark, particularly for daily “state of the nation” style reports.
When you say “what this tells BI Publisher to do is to bring together all of the data source XML from each data set and combine it into one single XML source” does it mean the data from each source can being merged too? If so, where are the intermediate results stored? (Business Objects allows you to do this sort of thing but it can play havoc with performance!).