Rittman Mead - Delivered Intelligence

The other day I wrote about the first of the integration features between Oracle BI Suite Standard Edition and Enterprise Edition in my article “Integrating Oracle Answers and Oracle Portal”. Today, I’m going to take a look at integrating Oracle Discoverer and Oracle BI Publisher, where BI Publisher can use a Discoverer worksheet as a data source.

To get this feature up and running, you need the following releases of Oracle Application Server and Oracle BI Suite Enterprise Edition

• Oracle Application Server 10.1.2.2.0
• Oracle BI Suite Enterprise Edition 10.1.3.3

In addition, you’ll need a one-off patch, No. 5648158, which gets applied to your Discoverer mid-tier to install the Discoverer Web Service, which BI Publisher uses to get data from Discoverer worksheets.

Getting this all set up was quite tricky, as you’ve got to patch Discoverer to install the Web Service, but before that you need to make sure Application Server is patched up to 10.1.2.2.0, which if you’ve not done so already involves also upgrading the metadata repository database to 10.1.0.5. As well as that, you also need to integrate Discoverer with OID, which involves protecting the Discoverer Viewer and Plus URLs using SSO, and you also need to configure BI Publisher so that it uses the OID (LDAP) security model, something that’s not mentioned in the docs. Starting off with the original requirement to link BI Publisher and Discoverer, this all causes a domino effect of having to upgrade various bits of your BI infrastructure to bring them up the right levels, although of course if you’ve already patched up Application Server and Discoverer to the latest levels, turned on SSO and so on, you’re probably mostly there anyway. Where it did get particularly tricky for me was trying to get all of this running in a VM with 1.5GB of memory - I need to have two Application Server tiers up and running (infrastructure and mid-tier), 2 Oracle databases (the metadata database, and my regular Oracle database with the Discoverer EUL in it), an instance of Application Server 10.1.3.3 and Oracle BI Suite Enterprise Edition, and BI Publisher - I had to be very careful about what’s running at any one time, and all of it worked most of the time unless I tried to run the Application Server Enterprise Manager website, whereapon things mostly seized up.

Anyway, once it’s up and running, there are two ways in which you can use data from a Discoverer worksheet in a BI Publisher report. Firstly, you can use the BI Publisher Enterprise Web site to create a new report, then select “Oracle BI Discoverer” as the data source type, then pick a connection - these have to be public connections as BI Publisher logs in to the Web Service as a trusted OC4J user, but not an authenticated SSO user.

Once you select the connection, you then pick from the list of workbooks available from that connection

When the workbook and then worksheet is selected, you can then save this part of the report definition and go over to the BI Publisher Desktop interface.

Laying out the report template involves firstly connecting to the BI Publisher server using the BI Publisher menu within Microsoft Word, and then bring up the Open Template dialog to locate the report that you’ve just created using BI Publisher Enterprise. Using the dialog, you locate the report, and then double-click on the entry below the report to create a new template.

This then creates a new blank template, with a sample of the data from the Discoverer worksheet available to you to help lay out the tables, crosstabs, graphs and so on. Once the template is complete, you upload it back to BI Publisher Enterprise and you can run it along with all your other BI Publisher reports.

The other way you can create reports using Discoverer data, is to go straight in to the BI Publisher Desktop application, log in to the BI Publisher server and bring up the Open Template dialog. This time though, if you use the drop-down list to select DIscoverer from the available workspaces, like this:

you can then select a connection:

and then a workbook and worksheet:

Then, you are prompted to copy the data source definition into the BI Publisher workspace (catalog), which creates a .XDO report definition and allows you to proceed as if you’d created the report yourself using the BI Publisher web interface.

Now you can create the template as you normally would, using the data supplied by the Discoverer worksheet (which can be an OLAP worksheet, not just a relational Discoverer worksheet) in charts, tables, crosstabs and form letters.

That’s it for now, I’m off on a flight up to Glasgow to run some BI Suite Enterprise Edition training through a partner, then it back again and bags packed for my seminar tour around Melbourne, Sydney and Auckland. Exciting stuff indeed.

Posted in Oracle BI Suite EE, Oracle Discoverer, Oracle Reports & XML Publisher |