Re: Reports in OLTP system

On 03.08.2007, joel garry <joel-garry_at_home.com> wrote:
> On Jul 31, 8:09 am, Wiktor Moskwa <wiktorDOTmos..._at_gmail.com> wrote:
>> Thanks for a reply. Let me change my question a little.
>> I wonder what is a usual practice of serving aggregate data in
>> a standard database (not a warehouse, used to store current data).
>> By aggregate data I mean for example monthly sales summary.
>>
>> The example environment is a web application with 2000 users
>> (each is a different <very> small business, not related to others,
>> with distinct subset of data). How to provide them with effective
>> up-to-date summaries? (triggers? MVs? queuing? calculation on
>> request and storing for later use?)
>
> Triggers not so good. In the past, when hardware was much slower, I'd
> do the calculation on request with storing for later use. I don't
> think I'd do that nowadays, but with the faster hardware the wheel
> that's already installed is like instant virtual DW's. Partioning
> would perhaps be more appropriate, but then again, we're talking
> organizations that are too cheap to have a second machine for
> reporting or DW, much less pay big licensing bucks if they don't
> _have_ to.
>

Hi Joel,

Thanks for your advice. That's exactly how you guessed - second DB or partitioning is not an option.
We don't have any performance problems at the moment but I'd like to design new parts and redesign older parts of this app "in a correct way". In the application that I've recently iherited the biggest problems are:
1. A lot of business logic in triggers
2. Undocumented, unpackaged procedures, some of them called from

   application, some used internally - big chaos; and most of them    had been written without thinking about concurrency... 3. Java object-relational mapping framework that "simplifies database

   access and isolates you from the complexity of databases" which    practically means that you can't force it do anything useful.    Curse on it!

> Some people are fans of VPD, I'm still reserving judgement on that,
> given the limitations it adds. For the situations I see, at any rate.
>

I've never used it but for me the idea behind VPD fits quite well into our environment - each user (a small business) "owns" a disjoint part of a table. But it would be useful for security reasons only I think and wouldn't solve our current problems.

>> And a sub-question: with two tables INVOICE (parent) and
>> INVOICE_ITEM (child) - do you usually keep sum of values
>> from child rows in parent row or calculate in application
>> every time?
>
> Calculate every time, except for a modification where they need a
> snapshot to be able to reprint what was printed previously (I added a
> table for that, since this is a packaged app that makes a big deal
> about the dynamism). There are a lot more than two tables involved.
>

Two tables were used for simplification. Of course an invoice has to be recorded in few "files" (I'm not sure if it's a correct English word for it). Is my guess correct that your application would invoke a single stored procedure that implements whole transaction and puts invoice's data into some tables and updates other tables?

That's at least how I'd do it - a facade of packaged procedures that do all the business logic, queries executed by functions returning ref cursor. But I wonder if it is a good idea to put virtually everything into procedures so that application never issues anything else than PL/SQL call. For example simple create/update/delete/browse involving one table at the time would need procedures for all those actions for every such table. What are "best practices" in this area?

Thanks for patience.

-- 
Wiktor Moskwa