Data Whse, Long-running queries, no-brainer

} Ok all, } } I'm tired of hearing the terms data mining and data warehousing without } fully understanding what they mean. I also get the impression that their are } different views of what they mean being banded about. } } Please explain in not more than 4 printed volumes. } } Mark Denham } BBC } London, UK } Mark.Denham@bbc.co.uk } In article <3uk28e$ccg@lennon.cc.gatech.edu> > gukal@cc.gatech.edu "Sreenivas Gukal" writes: } } >Hi Folks } >Part of my Ph.D. thesis deals with efficiently supporting long-running } >queries (maybe Decision Support System queries) without affecting } >concurrent transactions in an OLTP environment. I dont know how important } >and/or frequent a problem this is in real applications. If you } >have encountered similar situations, can you please let me } >know the context you had the problem in and what your solution was. } [...] } } The solution that I would most like to be able to offer is one where } the database allows queries to be prioritised in the same way that an } operating system allows processes to be prioritised. This would be } particularly effective for trend or pattern analysis queries that } could be set to run at a very low priority, or even during RDBMS } idle-time. Being read-only and aimed at historic data they would cause } the minimum of locking contention with "direct access" queries which } are primarily involved with current data. } [...] } Bruce Horrocks "Audiences are sometimes needed } Hampshire, England for acoustical purposes." } bh@granby.demon.co.uk Arnold Schoenburg } } } The only really good way I have found so far to solve your problem is } to have a replicate database against which the long ad hoc queries are } run. All solutions where such queries are run against a database where } OLTP work is going on, result in performance problems and other kinds } of trouble. My experience from practical work is that as soon as ad } hoc users are allowed to access a database, there are always users who } fire extremly complicated queries (most often it's done } unintentionally) that ruin performance for the OLTP:s. } To have a separate copy of the database for ad hoc use works fine. } There are tools available to ship all updates from the original } database to the copy at the desired point in time (immediately, once a } day, once an hour or at any other interval). } } Name: Magnus Weiman } Company: Datalogikonsult AB } http://DECUS.SE/~weimanm } mailto:WEIMANM@DECUS.SE [.sig stuff deleted...] These four posts are all related. Data warehousing (DW) is a term for the practice of providing large query-oriented databases isolated from OLTP. Often the DW is a consolidation of data from many separate OLTP systems. One of the uses of a DW is for DSS. A DW replicates data from an OLTP source, re-formatting and cleaning as needed, into a form--possibly de- normalized--that is easier for end users to deal with. DW's often contain summary data, which may be retained for longer periods than the details. Prioritization of queries against an OLTP system is a competing method of accomplishing the same goal. There are other terms often used in this context. Among them: Data Mining: This is the process of searching for relationships and information in a DW. These relationships were not easily visible under the separate databases that predate the DW. This is a pretty vague definition, a sure sign of a "buzz word." Data Mart: A subset of the DW, usually the result of queries against the DW, which are stored in a separate database, often on a separate machine. The purpose of a Data Mart is to provide extremely fast answers to questions that are defined in advance. Knowledge Worker: Any user of the information generated from the DW; potentially everyone in the company, customers, vendors, and other interested parties. This is the worst kind of buzz word. These definitions are my own, but they seemed to agree with the consensus at the Informix User's Conference. I attended most of the sessions on DW, and talked with several people who had built one. We are in the middle of building a small one (~30GB) here, which is why I have an interest in DW. There are several other regular contributors to the net in the same position, there might be enough interest to maintain a thread on this topic. Yours pontifically, __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|