Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 13, 2008

Business Intelligence Publisher - The "Other" Oracle Reports

By Steve Callan

Reporting in and from Oracle, or any database for that matter, has always been somewhat difficult. It seems that the more complex a report, the less flexible it is when something changes. Changes can originate from within the tool or from a change in the requirements. It’s also not uncommon that once a report has reached that “just right” state of being that the output format has to be changed or a new format is added.

From a reporting tool application developer standpoint (think of Oracle Corporation), what are the ramifications of supporting not only your own legacy reporting tool, but also having to support the reporting tools that come along with acquisitions? Let’s face it, if you are good at Oracle Reports, you can carry those skills over to what else? Pretty much just Oracle Reports. Maybe it would be worthwhile to make a new reporting tool which can not only do the presentation/content things of your product, but also provide that same reporting capability for other tools as well. For the developer (anyone who uses the tool), learning how to use one tool that can be applied against multiple products or databases has obvious benefits. Of course, the tool provider sees benefits as well.

This scenario precisely describes what Oracle Business Intelligence Publisher is and does. BI Publisher is a tremendously powerful reporting tool for several reasons. One is that it is based on widely accepted standardized open source language (XML) that is not tied to a particular tool. Another is that BI Publisher is feature rich in terms of what it can produce. Charts, tables, and graphs can be sent to printers, faxes, FTP’d, emailed, to name a few destinations. A third important feature is that BI Publisher empowers users to query, view, and format their own reports.

Users, or for all practical purposes, anyone with a computer, have access to BI Publisher and its report generation ability. How is that? One way to describe this empowerment is by asking what Oracle and Microsoft Word have in common. Assuming you’ve used Oracle for a while, you already have a pretty good idea of Oracle internals and how transactions work. Assuming you’ve used Word for quite a few years (since around Word 2000 and beyond), you probably have very little knowledge of how this application works.

Data can be saved as an XML document. Word, in turn, can read an XML document, and in fact, support an add-in from Oracle directly related to BI Publisher. Beneath the covers, Word documents are rich text format (RTF) files. BI Publisher’s formatting engine (based on XSL FO) takes the RTF document (a template) and combines it data (SQL query, XML file, Web/HTTP feed, to name a few sources). The output is then directed to one or more destinations and types of documents. Anything, and I mean anything, that you can do in Word to format a document can be applied to what’s produced in BI Publisher.

If BI Publisher is such a great reporting tool, where has it been hiding all these years? Formerly known as XML Publisher, this tool was largely in the purview of Oracle Applications. In older versions of E-Business Suite, XML Publisher was installed as an add-in via a patch. BI Publisher has also been obscured a bit by being buried within Oracle’s Business Intelligence suite. With so many moving pieces in the BI application stack, it’s easy to see how this tool has been overlooked. Recent versions of BI Publisher (now versioned in the 10.1.3.x series, jumped from 5.6.3) have introduced a significant degree of sophistication.

It’s time for BI Publisher to go mainstream. It’s not that Oracle Corp. hasn’t been putting effort into doing just that, but the attention or focus has been on the Oracle Applications and Business Intelligence communities. On Oracle Technology Network’s Oracle Reports page, there is no mention of BI Publisher whatsoever. On the other hand, within Oracle Applications, there is a significant push to replace all of the canned E-Business Suite Oracle Reports with reports generated by BI Publisher. It’s not just E-Business Suite and BI users who can benefit from this tool. Anyone with a need for reporting, and anyone currently using Oracle Reports can adopt BI Publisher in very short order (with some exceptions).

If your environment does not include EBS and BI, you are not precluded from using this tool. BI Publisher comes in a standalone version, and further, it can be deployed into a J2EE compliant Web server such as Tomcat. A future article will detail the installation into Tomcat.

“Great, I need to learn how to use another tool.” Well, yes, but it’s not that bad. The truth of the matter is that it is very easy to do easy things, and more complicated reports require some knowledge of XML (there are lots of X-whatevers involved with BI Publisher). BI Publisher comes with numerous examples already packaged into the application. Simple tables, linked/drill-down reports, Web feeds, charts, vivid graphics, and sample files/data sources are installed out of the box. OTN shows examples of the types of reports that can be created here.

Like other products, BI Publisher comes with tutorials in the form of Oracle by Example. Most of the OBE examples can be accomplished without the tie-in to a Business Intelligence Enterprise Edition (for Discoverer, BI Answers, and BI Requests) or without access to an E-Business Suite environment. The estimated times are wildly off the mark – your first time through will take a couple of hours, at least.

The use of BI Publisher, in terms of development, is analogous to current trends in Web design: separation of the content and presentation. This is driven home with example after example where the same data source is displayed in several different layouts.

Alas, BI Publisher is not free, where free connotes cost and usage. Within E-Business Suite (see the release notes information at eDelivery for the 12.0.4 media pack, as an example), the use of BI Publisher is free in that there is no license cost. However, it is not free in terms of usage. You are free to convert existing reports, but customized reports cost extra. Outside of EBS, you must pay for licensing (processor level is quoted around $46,000 plus the 22% maintenance fee), but are then free to use BI Publisher against whatever and wherever. Of course, when it comes to licensing, your mileage may vary.

In Closing

BI Publisher is probably going to drive a stake through the heart of Oracle Reports. Oracle’s Statement of Direction for Forms & Reports says Reports will continue to be supported for a very long time, but if you can’t feel the gentle nudging telling you to move to JDeveloper, ADF, and J2EE compliance, you’re going to be left behind at some point in the future.

BI Publisher is not perfect (what tool is?). It is subject to rapid evolution and many of the tips and techniques shown at a blog site are not included in the documentation. If you don’t “get” XML, you will be limited in what you can do with this tool. If you want to go full scale into converting Reports to a Publisher format, don’t count on the migration utility working very well (in fact, as of this writing, it doesn’t work at all). When it did work (in a prior release), more than 50% of what can be considered to be to complex reports required extra work to complete the conversion process. However, going forward, you’ll be pretty happy with the results, especially when you see how easy it is to create eye-catching reports by using Word.

» See All Articles by Columnist Steve Callan

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM