Distributed data warehouse query and resource management system

[0002] 1. Field of the Invention

[0003] The present invention relates to data warehousing and decision support systems. More particularly, the invention relates to a system and method of operation to support data query and retrieval in a data warehousing environment including a plurality of data providing computers accessible via a suitable communication network, wherein the information appears as though it may be emanating from a single (logical) data source.

[0004] 2. Background and Objects of the Invention

[0005] The advent of mainframe computing provided the ability to manage large amounts of data in a manner that had not been previously possible. The advantages of managing such volumes of data by computer was obvious, especially to government and large corporate entities. As can be seen in FIG. 1A, the architecture of such prior art systems, such as system 10a, initially provided a user accessing the system with a terminal 12 that was coupled to the mainframe 20 by way of a cable 12a. The cable 12aestablishes what may be termed a ‘communication channel’. These systems generally provided very simple ASCII-type user interfaces, wherein data was presented in tables (or lists) on monochrome screens. The early terminal interfaces enabled a user to enter, view, and modify the stored data, which could later be printed for hardcopy records. Importantly, even with the significant expense in deploying and maintaining these systems, they provided a cost-effective means to store, maintain, and manipulate very large amounts of data in a manner never before possible.

[0006] Soon thereafter, the advent of the mini-computer resulted in the introduction of reduced cost low-end database systems. Now it was possible for relatively small and medium sized companies to enjoyed the benefits of computer based data handling and management. Again, the interface screens employed by users of such systems were rather mundane by today's high resolution graphical user interface (GUI) standards. However, the availability of mini-computers also made it possible to have a plurality of such systems employed within an organization. For example, different departments or geographical locations within a single organization could employ one or more such systems.

[0007] Eventually, the advent of server based systems resulted from a merging of low-cost microprocessor based technology, and complex network and multi-user operating systems. These system architectures were, of course, driven by the introduction and proliferation of the personal computer or PC. As the computing power of these personal computer systems rapidly increased, and as prices continued to drop, the ubiquitous PC lead to the wide scale deployment of LAN based client/server computing environments. These systems provided advantages with regard to scalability and redundancy not previously available (especially when considering cost). Another significant advantage of these systems was their ability to run a variety of business application programs, which evolved and steadily improved in power and ease-of-use. The cost of these application programs was also quite low when compared to the cost of mainframe and mini-computer software. The PC based applications where generally simple and intuitive to use, and provided a ‘standard interface’ (GUI) having excellent graphical capabilities. Once a user understood the basics of the interface, new applications, or updated versions of older applications (having additional feature sets), were easy to learn and use.

[0008] However, the significant investment in mainframe and mini-computer based database systems, termed by some as ‘legacy’ database systems, combined with their stability and relative reliability, lead to a desire by some to mix legacy systems with newer server based data storage systems, which are often referred to as data warehousing systems. As depicted in FIG. 1B, when desiring to maintain older or original data providing systems, the result was the birth of hybrid systems. Whether hybrid (consisting of a plurality of mixed physical servers) or homogeneous (consisting of a plurality of the same or equivalent physical servers) these systems may require the analysis of information stored therein, and may accordingly be termed ‘decision support systems’ or DSS environments. An illustrative DSS 10b, as shown in FIG. 1B, includes clients such as client 14a, 14b, etc., with the ability to access data stored in one or more data marts 20a, data warehouses 20b, and/or legacy data-base systems 20c. It is important to note that the DSS environment may be formed by a mixed or hybrid arrangement (as illustrated), or a homogenous arrangement, say consisting only of data warehouses 20b. In each instance, however, a number of problems resulted from the desire to establish and maintain such DSS architectures. For example, certain database systems may employ accessing formats (or query languages) that differed from later PC and client/server based data warehouse query formats. This results in a need for query translation and reformatting. Also, as data within an organization could be located on any one of a number of data providing physical servers, there was a need to determine where the specific data being requested was actually located. This need to locate data, which has been exacerbated by the introduction of Web and enterprise resource planning (ERP) applications (which are typically distributed throughout an enterprise or organization), may be termed ‘query routing’. That is, query requests are steered or routed to a particular database, data warehouse, or data mart, wherein the data required is located. Query routing generally works best when based on cost and business rules.

[0009] Yet, other problems existed in these distributed DSS architectures, such as the need to manage redundancy, replication, and load-balancing, as well as provide security to confidential or sensitive information. In short, the DSS landscape was complex and full of pitfalls! The recent explosion in the use of the Internet as a data distributing medium certainly further complicated the situation.

[0010] One solution which was initially embraced to solve the above listed problems and concerns is illustrated in FIG. 2. The arrangement therein employed a middle-tier server 22, which was interposed between the client PCs, such as 16aaand 16bb,and the data sources, such as data mart 20a,data warehouse 20b, and or legacy data bases 20c. In theory, all the above listed problems, including translation, query routing, security, etc., would simply be handled by the middle-tier server 22 employing suitable ‘middle-ware’ software. A request for data would be passed to the middle-tier server 22, translated if necessary, and passed to the appropriate system to satisfy the query. The queried data would then be passed back to the requesting application program (executing on a respective client PC 16) via the middle-tier server. As one skilled in the art will recognize, the architecture of FIG. 2 clearly has several major drawbacks. First, the middle-tier server 22 is clearly a bottleneck for the flow of information. Not only do the queries flow through the middle-tier server 22, all requested (queried) data also passed through it. If very large volumes of data are requested by a client PC 16, the utilization demands placed on such a (middle-tier) server increases quickly. If a large number of users are to be serviced by the arrangement of FIG. 2, the middle-ware server may need to be a very high throughput, high-cost system. Accordingly, the middle-tier server 22 may require more computing power than the actual data mart 20aor data warehouse 20bsystems! In addition, if the middle-tier server 22 were to fail and go down, the entire system would generally be rendered inoperable with regard to delivering queried data to the client PCs 16. Skilled individuals will also appreciate the difficulty in scaling the arrangement of FIG. 2, wherein a large number of client PCs must be added over a period of time. Eventually, regardless of the actual middle-tier server computing power available, scalability of the arrangement of FIG. 2 becomes a significant issue.

[0011] Therefore, when considering prior art DSS system architectures, there is a need for more fault tolerant, scalable, and secure arrangements to overcome the above stated problems. Objects of the present invention are, therefore, to provide new and improved decision support and data warehousing system methods and architectures to support the rapid access to large amounts of distributed data having one or more of the following capabilities, features, characteristics, and or advantages:

[0012] employs an architecture not requiring a bottleneck producing middle-tier server configuration;

[0013] significantly improved system throughput over many presently available DSS architectures;

[0014] incorporates a ‘logical server’, which may be used to implement resource management and dynamically balance the loading of a plurality of physical data providing sources including data marts, data warehouses, legacy databases, Web based information servers, etc.

[0015] supports query routing that may be dynamically altered in a manner that is transparent to the system users (i.e., clients PCs and client applications)

[0016] a scalable and dynamically alterable architecture incorporating redundancy, load balancing, and security;

[0017] enables queried data to be directly transmitted from a data providing source computer, such as a data warehouse or a data mart, to the requesting client computer by way of virtually any available means to support networking and communication;

[0018] enables easy management and reconfiguration, possibly from a single location;

[0019] supports automatic and transparent client driver software updates, as needed or determined based on client generated query requests; and

[0020] simple relatively low cost architecture employing embodiments based on many off-the shelf hardware components.

[0021] The above listed objects, advantages, and associated novel features of the present invention, as well as others, will become more apparent with a careful review of the description and figures provided herein. Attention is called to the fact, however, that the drawings and the associated description are illustrative and exemplary only, and variations are certainly possible.

[0022] In accordance with the present invention a method and architecture for servicing data queries within a distributed data warehousing system is provided. The queries are generated by at least one data accessing and or data processing application executing on at least one client computer. The distributed data warehousing system is composed of a plurality of physical servers housing data to be accessed, wherein the physical servers may require one or more specific formats to be compatible with the queries generated by the client computers. An embodiment of the method may comprise the following steps. When a query is generated for data required by at least one application executing on the client computer, the query is passed to a connection driver. The connection driver may simply be a specialized software driver, such as a TSR or a VDD, executing on said client computer. The connection driver then forwards the query to a query server. (It may be assumed that the client computers, the query server, and all physical servers housing data are coupled via a suitable communication network.) The query server would next receive and process the query to determine (possibly via a query router therein or executing thereon) a specific physical server within the distributed data warehouse system that is to be accessed in order to resolve the query for the required or requested data. The query server would then transmit back to the connection driver of the originating client computer, information indicating the physical server to access in order to satisfy the query. The client computer, generally by way of the connection driver, would transmit a request to the respective physical server to satisfy the query. The physical server would then transmit (via the communication network) the required data to the requesting client computer. Upon receiving the required data at the client computer, the connection driver would forward or pass said data to the data accessing and or data processing application executing on the client computer. It is important to note that the required data is passed directly from the physical server to the client computer without passing through the query server or any other middle-tier server.

[0023] In a most preferred embodiment of the present invention, should the particular physical server, as selected by the query server to satisfy the query, require a query format differing from the original query format received by the query server, the query may be rephrased (by the query server) to establish a query format that is compatible with said physical server. In this situation the information transmitted from the query server to the originating client computer's connection driver may include the rephrased query, which is to be sent to the physical server to access the required data. Therefore, the present invention may be employed to provide a means to translate queries, which would otherwise be incompatible with a selected physical server having the queried data. Further the translation is conducted in a manner that is transparent to the application executing on the client computer.

[0024] In the drawings, like elements are assigned like reference numerals. It is important to note that each of the embodiments of the invention depicted are but one of a number of possible arrangements utilizing the fundamental concepts of the present invention. The drawings are briefly described as follows:

[0025] FIG. 1A provides a high-level block diagram of a simple mainframe based prior art computing arrangement employing terminals for user access to the system.

[0026] FIG. 1B provides a block diagram of a complicated and difficult to manage prior art distributed decision support system (DSS).

[0027] FIG. 2 depicts a prior art DSS system employing a middle-tier server coupling client computers to available data providing sources (e.g., data marts, data warehouses, and legacy database systems).

[0028] FIG. 3 illustrates a simplified high-level block diagram of a logical representation of an embodiment of the present invention.

[0029] FIG. 4 shows an embodiment of a DSS system architecture consistent with the present invention.

[0030] FIG. 5 presents a flow-chart of an embodiment of the present invention that inherently supports query translation and rephrasing.

[0031] 10a—A prior art mainframe database system

[0032] 10b—A prior art DSS arrangement

[0033] 12—(user) terminal

[0034] 12a—communication channel (or link)

[0035] 14a—client (sales)

[0036] 14b—client (purchasing)

[0037] 16—client PC

[0038] 16a—client application

[0039] 16b—client connection driver

[0040] 16c—network interface circuit (NIC)

[0041] 20—mainframe computer

[0042] 20a—data mart

[0043] 20b—data warehouse

[0044] 20c—legacy database or RDBMS

[0045] 22—middle-tier server

[0046] 26—query server

[0047] 26a—logical server (with virtual tables)

[0048] 26b—query router

[0049] 26c—metadata repository

[0050] 28—communication network

[0051] 50 to 68—flowchart blocks

[0052] It is important to establish the definition of a number of terms and expressions that are used throughout this disclosure. The term ‘query’ as generated or produced by an application (program) may be assumed to be any request for any volume of data needed for commencement, continuation, and or completion of a data accessing and or data processing activity. As such, a query may require a single piece of data (e.g., a byte, word, or single array entry) or an entire array of data to be accessed and delivered to or from an application. It should be understood that the query may be made in a common query language employing, for example, an SQL instruction or an OLE request, or a custom query format/language. The term ‘client computer’ may be any system ranging from the ubiquitous personal computer (PC), which may also be termed a workstation, network computer, thin client, etc., to a more advanced computing device, such as a mini-computer or a super computer. As such, the term client computer is to be broadly defined. The expressions ‘data accessing application executing on a client computer’, or the like, is to be indicative of one or more software programs that are executing, or possibly in a suspended state—but ready to execute, that are capable of accessing and processing data. Such ‘applications’ may, therefore, vary from a simple spreadsheet type of program, or a data processing software program, or even a mathematical program performing complicated mathematical data reduction algorithms. An important characteristic of any such ‘application’ is the need to access data from, or provide data to, a distributed data warehouse system that may be composed of data marts (also referred to as departmental or summary data warehouses), legacy databases, very large databases (VLDBs), and or data warehouses. Finally, the term ‘query server’, which may also be referred to as a query hub or simply a hub, is intended to include any computing system that may be accessed by a plurality of client computers to determine from where queried or requested data may be directly accessed. As such, a query server is not intended to operate in a fashion wherein the actual data required to satisfy a query or query request flows through the query server, as is the case with a ‘middle-tier server’. As skilled persons would appreciate, middle-tier servers are almost always interposed between a client computer and the data warehouse (or the like), as illustrated in FIG. 2. An integral function and or component of a query server, is to provide for ‘query routing’, as will be fully addressed below. As such the terms query server and query router may, in most cases, be employed interchangeably. It should further be noted that the original query generated by a client computer may be termed a ‘logical query’, while the term ‘physical query’ may be employed to indicate the query that is actually delivered to a particular physical server. Additional terms and their associated meanings will be provided as needed.

[0053] Referring now to FIG. 3, there is provided a high-level block diagram of a logical representation of a portion of an embodiment of a distributed data warehouse system in accordance with the present invention. It should be noted that a plurality of physical servers housing data to be accessed are not included in FIG. 3, but will be shown and addressed when referring to FIG. 4. As can be seen in FIG. 3, a client PC 16, which may be referred to as a client computer, is executing an application 16a.The application 16agenerates a (logical) query which is passed through a client connection driver 16band forwarded to the query server 26. The ‘forwarding’ of the query may be via any suitable communication channel or network including, for example, Ethernet LANs, ATM networks, and or the Internet. Further, other drivers or software components may be accessed by the connection driver 16bto actually forward the query to the query server 26 via the communication channel. The query server 26, which as shown may include a query router 26b,will examine the query passed from the connection driver 16b,and employ a logical server 26ato resolve the query and determine a particular or preferred physical server to be accessed to satisfy the respective query generated by the client application 16a.Next, information indicating the physical server to access (in order to satisfy the query) is transmitted back to the connection driver 16bof the client PC 16 (i.e., the client computer). The connection driver 16bthen transmits a request (query) to the physical server (not shown) to access the required data to satisfy the application generated query. In response to the request (for data) to the physical server, the required data is transmitted by the physical server and received at the client PC 16 and passed to the application 16a.As previously discussed, the present method eliminates the need for a middle-tier server (and the bottleneck thereat), and enables the required data to be passed directly from the physical server to the client computer without passing through such a server.

[0054] As can be seen in FIG. 3, the logical server 26amay be composed of one or more virtual tables organized according to a plurality of business rules of an organization, and the distributed data warehouse system thereof. A virtual table appears to a client application as a standard database table having rows and columns. Importantly, each virtual table does not actually store data information. Instead, virtual tables map to one or more data supplying sources (e.g., data marts, data warehouses, etc.), which actually supply the virtual table “information” to the client. Information that defines each virtual table, the underlying (physical) servers, and the associated mappings (between the virtual tables and the physical servers) may reside on the query server 26 within a metadata repository 26c.Therefore, the determination of the actual preferred physical server to be accessed to satisfy a query may be facilitated by the use of at least one virtual table of the query server 26, utilized in conjunction with the metadata repository 26c.It should be noted that the term ‘physical server’, as used in the context of this disclosure, may indicate any computer system such as a data mart (DM) 20a, a data warehouse (DW) 20b,a legacy data-base 20c,or the like, which are available to respond to queries in what may preferably be arranged in a client-server (c/s) configuration.

[0055] An exemplary embodiment of an architecture according to the present invention can be seen in FIG. 4. As shown, a communication network 28 enables queries to be passed via a network interface circuit (NIC) 16cto a query server 26. Also coupled to the communication network 28 are one or more available data housing systems including data mart 20a,data warehouse 20b,and or legacy data-base 20c.The method of the present invention certainly may be modified to employ several independent communication channels (not shown) to increase throughput and enable simultaneous transfers. For example, in one possible preferred embodiment, the client PC 16 may communicate over a first communication channel to exchange information (e.g., queries) with the query server 26, while a second channel may be employed for the transfer of the actual queried or required data. Skilled persons may provide other alterations and modifications to the structure of FIG. 4 that will remain within the scope of the present invention.

[0056] As can be seen in FIG. 4, a plurality of query servers may be provided to support redundancy, when down-time must be absolutely minimized. In a preferred embodiment of such a redundant arrangement of replicated query servers 26, if a designated primary (or 1st) query server 26 does not respond to a query within a predetermined period of time, a designated secondary (or 2nd) query server 26 (shown in dotted lines) may be configured to respond to the query. In addition, should such a primary query server 26 be determined to be non-responsive, or be determined to be responding after an unacceptably long period of time, an alternate primary query server may be automatically designated to replace the original primary query server. This type of redundancy is easily supported by the architecture and methods of the present invention. Another feature that may be provided by such a configuration would be to issue an alert, which is forwarded to a monitoring station when a primary query server does not respond within the predetermined period of time. This alert may be provided by the querying client PC 16, or the query server 26 that assumes the role of the (new) primary query server.

[0057] Turning to FIG. 5, there is provided therein a flow chart of yet another embodiment of the method of the present invention. At 50, a query is generated by a client PC application and passed at 52 through a client connection driver and forwarded to a query server. As discussed, the passing of the query may be realized by employing any suitable communication network. At 56, the query is received and processed by an available query server. At 58 a determination is made as to whether a translation of the received query is necessary. The necessity for translation may be determined before or after a physical server has been selected to satisfy the query. If query translation (or reformatting) is required, it is provided at 60. At 64, information that may include a translated or re-phrased query, as well as the respective physical server to access, is sent back to the connection driver of the respective client computer. The connection driver at 66 then transmits the query request to the physical server to access the required data to satisfy the application generated query. At 68, data is sent from the physical server, received at the client computer, and passed (preferably via the connection driver) to the application.

[0058] It is important to understand that the description of the embodiments of the present invention are illustrative only, and other equivalent arrangements are certainly possible. Therefore, while there have been described herein the currently preferred embodiments of a distributed data warehouse query and management system, those skilled in the art will recognize that other and further modifications may be made without departing from the present invention, and it is intended to claim all modifications and variations as fall within the scope of the appended claims.