Reporting Strategies for Oracle Databases
Using Three Alternative Approaches
In a multi-tier architecture, you have the user interface in the front tier,
business rules and application server in the middle, and finally the database
engine and its associated services in the back end. To produce a report in
such an environment, you basically have three different alternatives:
- Using application reporting tools that are part of your front-end
technology;
- Using database reporting tools that come with Oracle;
- Using independent (3rd party) tools.
Let's explore these alternatives in more detail.
- Your front-end reporting tools, as the name suggests, simply come with
whatever you have running in the user interface. This could be Access,
Visual Foxpro, Visual Basic, or any other technology. Front-end tools
typically give you the most powerful and diverse reporting
alternatives. Here are some examples:
- Access reports are fully object-oriented and programmable. They
also offer extremely powerful features such as "subreports"
which basically allow you to embed one report within another.
- Visual Foxpro reports, although not programmable like Access, have the
power and speed of Visual Foxpro's database engine behind them. In
a wide area network where different sites could be thousands of miles
apart, the processing speed virtually becomes the name of the game.
Also, in situation where you're not reporting off of a perfect data
warehouse and there's on-the-fly pre-processing to be done before a
report, Visual Foxpro is a perfect choice.
- Visual Basic reports, due to their widespread popularity, offer a
diverse array of 3rd-party add-ons. As such, if you have all sorts
of unique little things in your report requirements, chances are that a
Visual Basic add-on will be most helpful to you.
The draw-back of all of these tools, however, is that you are utilizing a
rich-client methodology and as such, you are adding to the load of the
client machine with each new report. Therefore, depending on the
number of reports you're planning to develop, you may want to keep the
reports away from your data entry / OLTP (on-line transaction processing)
system and instead put all of them in their own application.
- Oracle Application Server now comes with two reporting tools of its own:
DISCOVERER (for ad-hoc reports and queries) and REPORTS SERVICES (for
Web-based reporting and distribution). With these tools, you have fewer capabilities
(although more advanced in some cases). For example, the REPORTS
SERVICES allow you to "burst" the reports that have been
subdivided by a GROUPING selection and then send each piece to a different
destination electronically. At the same time, using native Oracle
features allows you to maintain a thin
client architecture and you don't have to overload your
data entry / OLTP system.
- The most popular of the 3rd party reporting tools is Crystal Reports, so
let's talk about it as a representative of this category. Here's a
quote from a computer magazine published in April 2002: "Crystal
Reports has likely been the cause of more developers losing their hair
prematurely than any other software product in the history of
mankind." Well, I happen to disagree with this quote.
Especially with last week's release of the new Crystal Reports version 9, a
wealth of new features has been introduced:
- Report Application Server (RAS) for the Web
- Unlimited SQL control in data connectivity
- Component reuse
- Java, .NET and COM support
Nevertheless, you still
have to justify it in light of other alternatives mentioned above. You
also have to decide whether to integrate Crystal Reports into your existing
applications or use it as a stand-alone tool.
In each of these alternatives, there are other considerations that are beyond
the scope of this article. Purchasing and licensing costs, the learning
curve of your IT staff, and interrelationships of multiple heterogeneous systems
are among the primary factors that you need to consider.
|
Ben
Aminnia, president of Pointer Corporation, is a database
architect with over 20 years of experience. He's also the president of
Los Angeles SQL Server Professionals Group www.sql.la
and a board member of Los Angeles .NET Developers Group
www.ladotnet.org. |
|
 |
|
Pointer
Corporation