Method of servicing query statements from a client machine using a database management system (DBMS) employing a relational datastore and a multi-dimensional database (MDDB)

• US 20100100558A1
• Filed: 12/11/2009
• Published: 04/22/2010
• Est. Priority Date: 02/28/2000
• Status: Active Grant

• Current Assignee: MEC Management, LLC (Three Affiliated Tribes of the Fort Berthold Reservation, North Dakota)
• Original Assignee: HyperRoll, Inc. (Oracle Corporation)
• Inventors: Bakalash, Reuven, Shaked, Guy, Caspi, Joseph
• Granted Patent: US 8,321,373 B2
• Time in Patent Office: Days
• US Class Current: 707/756
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 16/30 of unstructured textual data document management systems G06F16/93
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/957 Multidimensional
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method of serving query statements from one or more client machines using a database management system (DBMS) employing a relational data store and a multi-dimensional database (MDDB). A query interface is adapted to receive query statements from the client machines, and generate one or more query requests. A query handling mechanism (i) receives each request from the query interface, (ii) extracts a set of dimensions associated with the request, (iii) uses the dimensions to retrieve aggregated fact data from the MDDB, and (iv) forwards retrieved aggregated fact data to the query processing mechanism for subsequent processing. When the query processing mechanism determines that servicing of one or more query requests require data stored in the relational tables, then the query handling mechanism automatically routes the requests to the relational data tables, so that data can be accessed from the relational tables and forwarded to the query processing mechanism for use in servicing the requests, in a manner transparent to the client machine.

Claims

1. A method of servicing query statements from one or more client machines using a database management system (DBMS), said method comprising the steps of:

(a) installing said DBMS within an enterprise, wherein said DBMS includes

a query interface adapted to receive query statements from said one or more client machines, and generating one or more query requests by disintegrating the query statement so that each said query request specifies a set of dimensions;

a query processing mechanism for processing each query request received from said query interface;

a query handling mechanism operably coupled to said query interface;

a relational data store having relational tables for storing fact data, and a meta-data store for storing a dictionary containing dimension data;

a multi-dimensional database (MDDB) for storing aggregated fact data in a multi-dimensional data structure;

a data loading mechanism for loading said meta-data and said fact data into said MDDB; and

an aggregation engine for calculating aggregated fact data from the fact data according to a multi-dimensional data aggregation process;

(b) loading said meta-data and said fact data into said MDDB;

(c) calculating aggregated fact data from the fact data according to said multi-dimensional data aggregation process, and storing aggregated fact data in said MDDB;

(d) said one or more client machines transmitting said query statement to said query interface, and generating one or more query requests by disintegrating the query statement so that each said query request specifies a set of dimensions;

(e) said query handling mechanism

(i) receiving each query request,

(ii) extracting the set of dimensions associated with said query request,

(iii) using said dimensions to retrieve aggregated fact data from said MDDB, and

(iv) forwarding retrieved aggregated fact data to said query processing mechanism for subsequent processing, if needed; and

(f) said query processing mechanism processing said one or more query requests, and

(i) when said query processing mechanism determines that servicing said one or more query requests require data stored in said relational tables of said relational data store, then said query handling mechanism automatically routes said one or more query requests to said relational data tables, so that data is accessed from said relational tables and forwarded to said query processing mechanism for use in servicing said one or more query requests, in a manner transparent to said client machine, and

(ii) when said query processing mechanism determines that servicing said one or more query requests require aggregated data stored in said MDDB, then said query handling mechanism automatically routes said one or more query requests to said MDDB, so that aggregated data accessed from said MDDB and forwarded to said query processing mechanism for use in servicing said one or more query requests, in a manner transparent to said client machine.

2. The method of claim 1, wherein step (e) comprises said query handling mechanism comprising a first query handler operably coupled to said query interface, and a second query handler operably coupled to a first data communication interface;

and

wherein said second query handler (i) receiving each query request from said first query handler, (ii) extracting a set of dimensions associated with said query request, (iii) providing said dimensions to said data handling mechanism so that said data handling mechanism can retrieve aggregated fact data from said MDDB, and (iv) forwarding retrieved aggregated fact data to said query processing mechanism for subsequent processing, if needed.

3. The method of claim 1, wherein step (b) comprises said data loading mechanism loading said meta-data and said fact data from said relational data store into said MDDB by way of a second data communication interface.

4. The method of claim 1, wherein after data loading operations conducted during step (b), step (c) comprises initially aggregating fact data along at least one of the dimensions extracted from said dictionary, and storing aggregated fact data results in said MDDB.

5. The method of claim 4, wherein during query servicing operations, step (f) comprises aggregating the fact data on demand along additional dimensions extracted from said query request, and storing aggregated fact data results in said MDDB.

6. The method of claim 1, wherein said multi-dimensional data structure comprises a plurality of data storage cells, and wherein step (b) further comprises indexing each said data storage cell with multiple dimensions, and storing either base data values or aggregated fact data values in said data storage cells.

7. The method of claim 1, wherein during data loading operations, step (b) comprises said data loading mechanism extracting dimension data from the dictionary in said meta-data store, and forwarding the dimension data to said aggregation engine and configuring said MDDB using said dimension data.

8. The method of claim 1, wherein during aggregation operations, step (c) comprises said data loading mechanism extracting fact data from said relational tables, and forwarding the fact data to said aggregation engine;

and wherein step (c) comprises said aggregation engine calculating aggregated fact data from the fact data according to said multi-dimensional data aggregation process.

9. The method of claim 1, wherein during step (d) said query statement is an SQL-type query statement and each query request is an SQL-type query request.

10. The method of claim 2, wherein said first data communication interface is a standard interface selected from the group selected from OLDB, OLE-DB, ODBC, SQL, API, and JDBC.

11. The method of claim 3, wherein said second data communication interface is a standard interface selected from the group selected from OLDB, OLE-DB, ODBC, SQL, API, and JDBC.

12. The method of claim 1, wherein said query interface is implemented in a module within said DBMS.

13. The method of claim 1, wherein during step (d) the user interacts with said client machine using a web-enabled browser to generate a natural language type query statement, and said natural language type query statement is communicated to said query interface.

14. The method of claim 2, wherein during step (e) further comprises said second query handler transforming each query request so as to optimize the query request for efficient query handling.

15. The method of claim 2, wherein during step (e) further comprises said second query handler mapping the data types of the query statement issued by said first query handler, and for mapping a standard data type used to represent the query request issued by said first query handler into the data types used in said MDDB.

16. The method of claim 1, wherein during step (f) said multi-dimensional aggregation process further supports the following operations:

(a) if the aggregated fact data required to service a given query statement is already pre-calculated and stored within said MDDB, then the pre-aggregated fact data is retrieved by said data handling mechanism and returned to said client machine via said query handler; and

(b) if the required fact data is not already pre-aggregated and stored within said MDDB, then the required aggregated fact data is calculated on demand by said aggregation engine, and the aggregated fact data result is automatically forwarded to said client machine.