Re: DBMS' and datawarehouse combinations. (Management and ad-hoc infor
Date: 1996/05/11
Message-ID: <68fFC$fljOB_at_rupert.shnet.org>
Hi,
We have done some work in this field recently. We had to develop an information system for a SQL-DB, namely Oracle. After some thinking we went towards producing SQL on the fly automatically. This lead to an approach where it is possible to build querys without knowing anything about the structure of the database, nothing about SQL at all. You just "describe" your result-set and run it against the database. If you want, you can add more details to it at any time, combine two sets to a new one and so on.
By generating SQL on the fly when it is needed this system makes use of Snapshots the database features. This means that you store redundant data on the server that is only a small aggregated part of the original data. Whenever it is possible to use a smaller table that is a subset of a (potentially much) bigger, this will be included in the query, the bigger one ignored. If the description becomes more detailed, the bigger one will be used again, without the user noticing it (except different answer time). This means that you can get quick information at low detailed level but at any time also go to the finer grade, then possibly having to deal with longer answer times again.
Imagine a big table (extremely simplified) containing
- TransportOrderID
- FromLocationID
- ToLocationID
If you query this frequently with something like
SELECT FromLocationID, ToLocationID, Count( TransportOrderID )
FROM ... WHERE ...
...
Think of this as a persistent view (snapshot) that the db-server updates automatically. Therefor that table-view will be magnitudes smaller because it contains now only FromLocationID, ToLocationID and NumberOfTransports. Identically with the above query you might now ask the server
SELECT FromLocationID, ToLocationID, SUM( NumberOfTransports ) ...
with exactly the same results. Now - having to think about this by yourself is one thing, our approach guarantees that you have to say only:
(I need) FromLocation, ToLocation and Number of Transports
and you'll get the optimal query for that. If you add TransportOrderID later to the list, the query will be regenerated using the original, bigger table.
The Product is a library which is easily accessible from an application outside. There are lots of possibilities to manipulate the result-set- descriptions, feeding them from one tool to another (e.g. Drag&Drop), making them persistent and so on.
The library is written in C++, the first applications using it are written in SQL-Windows. However, the later is replaceable at any time.
F.J.Verduin_at_KUB.NL wrote
[paragraph about Sybase Server accessed by PCs using ODBC deleted]
> My view on this is that it should be possible to use this data-store
> in another way, by combining the use of pre-programmed queries and a
> GUI to create an all-round management information system, reporting
> (monthly?) changes in premiums, damages and the like (off hand, but
> the items are too divers to fully describe here)
Some more words about the application side: What we have now is the aspect of tools manipulating resultset-descriptions (querys) and tools displaying information from the result set. Examples (generic tools) for the later are Cross-Table, Graphic-Tool (Charts), simple tables and Hypergraphics. There are more tools that are used to manipulate the "querys", e.g. a "search"-tool and so on. You can feed any result-set to the next tool, you can even feed tools to tools, and you can grab parts of result-sets and feed them to other tools. (several rows of a query, a cell of a cross- table etc). These parts are again complete resultset-descriptions that may be manipulated as every resultset-description before. In addition to this you can make every single bit of these persistent and reuse it next time you start (or for the statistics next month)
> One of the major drawback, besides convincing my boss and the people
> who're doing the job manually now, is processing time. Programming isn't
> the problem, but the time it takes Access 2.0 to sieve through the data
> on the Sybase is a real pain.
>
> So, my questions:
>
> 1. I really need an DBMS alternative to Access 2.0, something that's
> not *too* expensive but cuts processing time by a reasonable amount. I
> was thinking about a system with a native Sybase-driver myself, but
> anyone with a suggestion/opinion and/or experience is welcome to share
> it. My own experience with other DBM-programs is negligable.
Our approach does not improve the communication with the server, nor does it improve the performance of the server directly. However, configured for the individual needs it automatically makes heavy use of predefined queries (snapshots) on the server, cutting processing time indirectly by using smaller tables (it's also possible to put data from different table into only one new snapshot to limit several joins)
> 2. I would like to discuss this idea with others, people who've
> thought of something similar, introduced a similar idea or are
> willing to ponder a bit on it.
>
> Anyone?
Any time. Email me.
Regards,
Olaf
-- Wollen Sie nicht Ihre Festplatte löschen? (J/N)Received on Sat May 11 1996 - 00:00:00 CEST