Re: Reports in OLTP system

On 28 J l., 19:27, Wiktor Moskwa <wiktorDOTmos..._at_gmail.com> 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!
>
> --
> Wiktor Moskwa

There are things you can do and things you cannot do in this world. You cannot get the speed and power of Ferrari for the Opel price. Users might want up to date reports but (depending on their complexity of course) this might need additional boxes and/or other resources. You haven't said BTW what are these reports? Are these simply some ~10 rows for the most recent transactions for a specific user that you can got from 3 tables using indexes and nested loops or are these complex aggregates needing for a full scan of ~10 tables? If the latter and you haven't another box for reporting there are 3 possibilities:
1) calculate reports from business data and you can easily calculate necessary time for it just running when there is no load on the system 2) calculate reports from some on commit refresahble Materialized views and understand that this will add quite significant overhead on your business transactions.
3) calculate reports from some periodically refreshable MVs and let users understand that they actually a) cannot get most recent data because of lack of horse powers b) most recent data on a busy system many times are just unnecessary luxury - you start to genarate precise report on 10.00 and end it say 10.02. But situation has been changed and overall totals also have been changed. So you could easily get along with data on 7.00 because the main goal usually for such reports are some trends.

We had similar situation like you (need for complex searches) and we simply understood that we cannot generate them from business data and we cannot afford on commit MVs, so we got periodically refreshed MVs and forced users to accept that. Simple - either pay for Ferrrari or if you cannot, you can get Opel ;)
OK and my experience is described here - Effective search in a normalized application at http://www.gplivna.eu/papers/mat_views_search.htm

Gints Plivna
http://www.gplivna.eu Received on Wed Aug 15 2007 - 02:26:07 CDT