Delivering Oracle Discoverer Over The Web
Oracle Discoverer is an ad-hoc query tool used for reporting and analyzing data held in Oracle and other third-party databases. It presents data using 'business terms', allowing users to build reports by choosing data items from a business repository (known as the 'End User Layer), then dragging and dropping them onto a report using a 'point and click' interface. Oracle Discoverer allows users to create charts and graphs to accompany their reports, and then to save and publish them for other users to view.
Historically, Oracle Discoverer came in two versions - Oracle Discoverer 3.1 User Edition, used by business users and analysts to prepare and run reports, and Oracle Discoverer 3.1 Administration Edition, used by system administrators to control access to the underlying database. Both applications were client-server Microsoft Windows application, and the only way to deploy reports to a wider audience was to use the (unsupported) method of using product such as Citrix Metaframe. However, with versions 4i, and now 9iAS, of Oracle Discoverer, it is now possible to deploy Discoverer reports using a web browser over the internet.
Introducing Oracle Discoverer 4i and 9iAS
Oracle Discoverer 4i and 9iAS have a three-tier architecture that integrates the Discoverer 'engine' into the Oracle 9i Application Server. Users now have two versions of Oracle Discoverer for viewing reports, a thick-client java applet version of Discoverer User Edition called Discoverer Plus, and a thin-client, pure-HTML version of Discoverer User Edition called Discoverer Viewer.
Discoverer Administration Edition was kept as a Windows-based application, renamed as 'Discoverer Administrator', and bundled in with Oracle Warehouse Builder, Oracle Reports and Oracle JDeveloper (amongst others) as part of the Oracle 9i Developer Suite. The old client-server Discoverer User Edition was however preserved but renamed as Discoverer Desktop, and one license for this version was also included in each Oracle 9iDS license. Lastly, from Oracle 9iAS Release 2 (9.0.2), an additional feature, Discoverer Portlets, were introduced that allowed worksheets and workbook lists to be displayed as portlets in Oracle Portal 9iAS. All of the web-based Discoverer tools (Discoverer Plus, Discoverer Viewer and Discoverer Portlets) are rendered and co-ordinated by Discoverer Services, the J2EE-based Discoverer 'engine' that is incorporated into Oracle Application Server.
Ignoring for this moment Discoverer Administrator, which hasn't changed much since version 3i, each of the Discoverer query tools performs a slightly different role, and has different capabilities and benefits. A typical implementation of Discoverer generally involves just Discoverer Viewer, Plus and Portlets, with Discoverer Desktop used less and less, though there are some instances where Desktop is still useful. The key features of Discoverer Plus, Viewer, Portlets and Desktop are as detailed below.
Discoverer Plus, Viewer, Desktop, Administrator and Portlets
Discoverer Plus is a reimplementation of Discoverer Desktop, but written in Java. The aim is for it to contain all of the features that Discoverer Desktop had, and as time goes by, new features added to Discoverer will get added to Discoverer Plus rather than Discoverer Desktop.
For example, graphing in Discoverer Plus is an improved version of the graphing available in Discoverer Desktop, as it uses the 'BI Beans Graphing Bean' which is also used by Oracle Reports and Oracle BI Beans. Discoverer Plus is delivered as a java applet embedded in a HTML page, and it uses the Oracle JInitiator plug-in provided with 9iAS to establish stateful, two way communication between the query tool, the 9iAS middle-tier, and the back-end Oracle database.
The main advantage (apart from the graphing capability) of Discoverer Plus is that it gives you all of the functionality of Discoverer Desktop, without having to install software on the client's PC. One disadvantage, however, is that as Discoverer Plus is written in java, it requires a slightly higher-specified client PC that was required for Discoverer Desktop.
Discoverer Viewer is a thin-client HTML based version of Discoverer Plus, that allows users to view and interrogate workbooks built using Discoverer Plus or Desktop. It doesn't allow you to create new workbooks or worksheets, and whilst you can apply parameters and 'slice-and-dice' the data in a report, you can't save the report afterwards. Discoverer Viewer reports have the Oracle 'Browser Look And Feel', and integrate well with the rest of the range of Oracle 9iAS web pages and reports.
Discoverer Viewer is a useful complement to Discoverer Plus, and in many cases, clients roll out Discoverer Viewer to the majority of their users, with a handful of 'power users' using Discoverer Plus to create reports and graphs for the other users. Like Discoverer Plus, Discoverer Viewer reports are directly connected to the Oracle database using a stateful session, and any changes to the underlying data are dynamically reflected in the report or graph. The Discoverer Viewer interface can be customised for an organisation using preference files on the 9iAS server, and the look and feel can be completely altered to match an organisation's intranet style.
Discoverer Desktop is still around and as mentioned previously, is now bundled in with Discoverer Administrator as part of 9iDS. Each 9iDS 'seat' comes with one Discoverer Desktop and one Discoverer Administrator license, which means that if customers wish to deploy further copies of Discoverer Desktop to other users, they either have to license more copies of 9iDS, or license Discoverer Desktop separately for each user.
Compared to Discoverer Viewer and Plus, which are bundled in with 9ias on a per-user, per-processor or a site license, Discoverer Desktop has to be licensed on top of 9iAS which is the usual reason it's not used these days. However, there are one or two occasions when Discoverer Desktop is a better choice than Discoverer Plus - sometimes, print formatting is better with Discoverer Plus, and, as a native Windows application, it seems a bit faster and more responsive that working with a java applet. One further thing to bear in mind with Discoverer Desktop is that graphs built using it don't display when viewed using Discoverer Plus and Viewer, which use a different graphing mechanism (the BI Beans Graphing Bean) to that used by Discoverer Desktop. Any workbooks built using Discoverer Desktop will need their graphs redoing when viewed using Discoverer Plus and Viewer.
The new Discoverer Portlets introduced with 9iAS 9.0.2.0.0 allow a Discoverer Plus or Viewer worksheet to be displayed as a portlet using Oracle Portal. Users can apply parameters to the worksheets if required, and the data and reports are rendered in exactly the same look and feel as with Discoverer Viewer, although crosstabs can't be drilled in to or dimensions swapped around as the worksheet is essentially 'static'.
In addition to the worksheet portlet, there's also a 'list of workbooks' portlet, that displays, for a given public or private Discoverer connection, the list of available workbooks as defined in the End User Layer. Unlike Discoverer Viewer and Plus worksheets and workbooks lists however, the output from Discoverer portlets are refreshed to a defined cycle (once an hour, once every few hours or days, or whatever) by Oracle Portal, which means that changes to the underlying data or End User Layer are not immediately reflected in the portlet. Each portlet however has a 'view workbook' link which, when clicked, takes the user to the corresponding Discoverer Viewer worksheet which will have up-to-date data within it. Discoverer portlets are best thought of as a snapshot of data at a particular time, and work best as 'adverts' or 'tasters' for data in the customer database rather than a query tool in their own right.
A summary of the different features found in the various Discoverer query tools would be as follows;
| Function | Desktop | Plus | Viewer | Portlets |
| Create / Edit Query | Yes | Yes | No | No |
| View Query With Dynamic Data | Yes | Yes | Yes | No |
| View Query With Snapshot Data | No | No | No | Yes |
| BI Beans Based Charting | No | Yes | Yes | Yes |
| Export To Microsoft Excel | Yes | Yes | Yes | No |
| Deliverable As Portlet | No | No | No | Yes |
| Integrated With Oracle Single Sign-On | No | Yes | Yes | Yes |
| Create Worksheet Parameters | Yes | Yes | No | No |
| Apply Parameters To Worksheet | Yes | Yes | No | No |
| Create / Edit Conditions | Yes | Yes | No | No |
| Create / Edit Calculations | Yes | Yes | No | No |
| Save The Workbook | Yes | Yes | No | No |
| Supports Subqueries | Yes | No | No | No |
| Find In Sheet | Yes | No | No | No |
| Count All Rows | Yes | No | No | No |
| Exception Formatting | Yes | No | No | No |
Upgrading To Discoverer 4i and 9iAS
Upgrading existing Discoverer 3i installations to either Discoverer 4i or Discoverer 9iAS is straightforward, and consists of three separate steps;
- Install either Oracle 9i Application Server 1.0.2.2 or Oracle 9i
Application Server 9iAS 9.0.2.
Unless there is a specific reason to choose Oracle 9iAS 1.0.2.2, the later
version (9.0.2) should always be installed instead. In addition, at the time
of writing (March 2004) Oracle have now released Oracle Application Server
10g (9.0.4) for the Sun Solaris, HP-UX and Intel Linux platforms, and if
these are suitable platforms this newer version can be installed instead.
- Install the correct version of Oracle Discoverer Administrator (version
4.1.37 for Oracle 9iAS 1.0.2.2, version 9iAS for Oracle 9iAS 9.0.2, and
version 10g (9.0.4) for Oracle Application Server 10g (9.0.4).
- Once the correct version of Oracle Discoverer Administration Edition has been installed, connect to any End-User Layers that need upgrading, and Discoverer Administrator will then prompt you to upgrade the EUL tables from version 3 to version 4 or 5. This process is non-destructive and preserves the existing EUL versions if for any reason you want to go back.
Next Steps
Once you have installed the software and upgraded the EUL tables, the decision then becomes one of how best to deploy the new web-based versions of Oracle Discoverer.
A typical approach would be for office-based, power users who wish to create and publish Discoverer reports to use a combination of Oracle Discoverer Desktop and Oracle Discoverer Plus. Discoverer Plus is used for creating reports and graphs for distribution to other users, with Oracle Discoverer Desktop occaisionally used for tasks that Discoverer Plus cannot yet do. Reports are then distributed to users using Oracle Discoverer Viewer, which allows them to open reports, slice-and-dice the data, and print and export the data as required. Certain reports could then be subsequently published as portlets in Oracle Portal 9iAS, giving users a dashboard view of the organisations' performance, and allowing them to drill-through to full Discoverer Viewer reports if they wish to explore the report in more detail.
Further Reading
More details on the various Discoverer versions can be found over at the 'Getting Started With Discoverer' page on OTN, or by looking at the online 9iAS or Discoverer documentation. More detailed instructions on upgrading Oracle Discoverer 3i to versions 4i and 9iAS can be found in the Plus Consultancy white paper, "Upgrading Business Intelligence Applications To 9iAS". Lastly, keep an eye on the Plus Consultancy website at http://www.plusconsultancy.co.uk where you'll find tips and tricks for working with Oracle Discoverer and the rest of Oracle's range of business intelligence and data warehousing tools.