Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Reports in OLTP system

Reports in OLTP system

From: Wiktor Moskwa <wiktorDOTmoskwa_at_gmail.com>
Date: Sat, 28 Jul 2007 16:27:16 +0000 (UTC)
Message-ID: <f8fqp4$niu$1@atlantis.news.tpi.pl>


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!

-- 
Wiktor Moskwa
Received on Sat Jul 28 2007 - 11:27:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US