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.