Re: Summary: Controlling "ad hoc" queries

In article <laujakINNp5_at_news.bbn.com>, NBROOKS_at_BBN.COM (Nat Brooks) writes:
> In article <lapscgINN8jg_at_news.bbn.com> Nat Brooks, NBROOKS_at_BBN.COM writes:
>>Has anyone out there successfully installed and supported an end user >>query tool that was actually used by end users?

We have been using Easy*SQL, SQL*Plus, and FOCUS as end user tools for adhoc reporting. We tried SQL*QMX but the users never liked it so its just stagnating. Some users found Easy*SQL too simplistic and now have access to SQL*Plus. The Dept. went through a review process some time ago and FOCUS was selected as THE end user reporting tool but the end users haven't picked it up fully yet. I've seen demos of Oracle's new end user type tools (SQL*Browser etc) and they look nice but we haven't moved on them yet.

>> How did you prevent "monster" queries?

With Oracle6, you can't. The best we could do was isolate them to their own disk drive(s) away from transaction based production applications (same machine though).

Oracle7 is supposed to allow you to set CPU and I/O limits per session/user so this should help enormously. Oracle7 also has the cost based optimiser which should help the performance of end user querries.  

>> How did you handle users formulating queries that returned misleading >> results?

We provide crystal balls so users can see into the future and determine if the results look ok.

Actually, there is no way of guaranteeing query correctness. The users are warned about this and are effectively on their own. If they want confirmation that a particular query is correct then they can approach the application developers for review but this hasn't happened to my knowledge.

>> How did you /will you handle changes in the database schema?

We don't allow adhoc querries on live production data. Production data structures are geared (denormalised and indexed) for production activity and throughput. We full normalise the production structures and place a full copy into the reporting database. The reporting database has many many indexes which would slow down updates (which don't happen here) but are fine for reporting.

Regular copies are a real pain but Oracle7 has some nice features which should make this a lot easier.

> Are "user query tools" a dead end, due to inadequate protection against
> "incorrect" queries?

At the moment YES. The standard reply from the vendor is to run it on a bigger machine which of course requires more license spending and maintenance costs. Keep screaming at the vendor long enough and they will eventually come up with something useful.

> If so, what are the alternatives (rapid development of query applications,
improved report writing tools...)?.

When an application is built we try to identify the major reports that would be required. Those that satisfy 90-99% of all inquiries. The user is given the ability to modify the columns that are extracted and change range settings on the WHERE clauses but cannot add to the complexity of the query. The base skeleton query is optimised by the application developers.

We have found this to work quite well. If the user's job changes to start requiring additional reports regularly then they are factored into the maintenance/enhancement resources for that project.

> If not, how are they best used (only with heavy training for users, small
> databases only, on extracted "reporting" databases...)?

If you have an end user adhoc reporting requirement then you MUST TRAIN them in using the tools efficiently. You MUST MONITOR who is using the adhoc enquiry facilities and clobber anyone who is doing inefficent reporting.

> My intention here is to stimulate discussion. Speculate away!

Is the above a good start?

-- 

Bruce...        pihlab_at_hhcs.gov.au
                 ^^
*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Health, Housing & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************