BI Publisher Reports with Multiple Data Sources

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.

biee_multiple_ds_1.jpg

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.

biee_multiple_ds_2.jpg

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.

biee_multiple_ds_3.jpg

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?

biee_multiple_ds_4.jpg

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.

biee_multiple_ds_5.jpg

Using this feature I can then put one report template together that uses two different data sets, which when previewed, looks like this:

biee_multiple_ds_6.jpg

Not bad. Again, thanks to Borkur for working out the mechanics.