Re: Reports in OLTP system

Wiktor Moskwa wrote:
> Hello,
>
> Let's consider an accounting application for small businessess. It is
> a web based application with about 2000 users issueing invoices and
> making lots of other business transactions. In the same time they
> want to view tax reports, generate tax declarations and see general
> reports of how the business is doing.
> Most users would like to see reports very frequently.
>
> My question is what are best practices in this area. How to generate
> reports for individual users based on source data that is changing
> all the time.
>
> My initial proposals are (database is Oracle 10R2):
> 1. Use triggers to maintain up-to-date reports
> 2. Use "fast refresh on commit" materialized view as reports
> 3. Manually generate a report in PL/SQL procedure when it is requested
> by a user and is not up-to-date - otherwise present the one
> calculated last time
> 4. Queue requests for reports and generate them in the background
> (advenced queing would be used)
>
> Triggers and materialized views will have impact on data modifications,
> moreover I think triggers should not contain business logic and
> materialized views are discouradged for OLTP systems.
> In cases 3 and 4 a business transactions has to mark some reports
> as old.
>
> What can you suggest?
> Thanks in advance!

I'd suggest taking a serious look at BAM.

http://www.oracle.com/technology/products/integration/bam/index.html

Why reinvent the wheel?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org