Analytical database system that models data to speed up and simplify data analysis

• US 6,879,984 B2
• Filed: 04/12/2001
• Issued: 04/12/2005
• Est. Priority Date: 10/05/2000
• Status: Expired due to Fees

• Current Assignee: Clareos, Inc.
• Original Assignee: Clareos, Inc.
• Inventors: Duddleson, William, Steinhoff, David
• Primary Examiner(s): Corrielus, Jean M.
• Assistant Examiner(s): HWANG, JOON H
• Application Number:
  US09/834,125
• Publication Number:
  US 20020040639A1
• Time in Patent Office: 1,461 Days
• Field of Search: 707 1- 10, 707100-103, 707200-206, 711170-173, 709200-203, 709/247
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

An analytical database system provides access to all of the data collected by an entity in interactive time. The analytical database system transforms relational database data. The relational database is denormalized and inverted such that the data fields of tables in the relational database are stored in separate files that contain a row number field and a single data field. At least one of the files, that contains repeating data values that are stored in successive rows, is compressed. The files include files with partition values and files with analytical data. Processing of the files is distributed by creating sub-rowsets of the files and by assigning the sub-rowsets to servers. The partial result sets are merged into a complete result set.

Claims

1. A method for providing an analytical business report that is based on data including data from tables, each table including a plurality of rows and a plurality of columns, each of the plurality of columns corresponding to a data field, the method comprising:

inverting said data such that said data fields are stored in separate files that include a row number field and data values corresponding to a single data field, wherein said files include at least a first file with a partition field and a second file with an analytical field;

compressing at least one of said files, including files containing repeating data stored in successive rows;

traversing said at least one of said compressed files while said compressed file is stored in memory to directly retrieve data stored in said compressed file without decompressing said compressed file;

using said retrieved data to generate said analytical business report;

processing said partition field by creating sub-rowsets of said partition field and by assigning said sub-rowsets of said partition field to a first set of servers, wherein said first set of servers identify unique partition values contained in said sub-rowsets;

merging said unique partition values identified by said first set of servers;

transmitting said merged partition values to a second set of servers; and

processing said analytical field for said merged partition values by creating sub-rowsets of said analytical field and by assigning said sub-rowsets of said analytical field to said second set of servers.

2. The method of claim 1 further comprising the steps of:

performing an analytical function using said second set of servers to create partial result sets; and

merging said partial result sets into a complete result set.

3. The method of claim 2 further comprising the step of:

annotating said complete result set.

4. The method of claim 3 further comprising the step of:

transmitting said complete result set to a client computer.

5. The method of claim 1 further comprising the step of:

receiving business user requests for said business reports via a browser of a computer that is connected to a distributed communication system.

6. The method of claim 5 further comprising the step of:

distributing said business user requests from multiple business users for said business reports over a plurality of web application servers using dynamic binding.

7. A system for providing an analytical business report that is based on data including data from tables, each table including a plurality of rows and a plurality of columns, each of the plurality of columns corresponding to a data field, comprising:

a data storage device that stores denormalized and inverted data in separate files that contain a row number field and data values corresponding to a single data field, wherein said files include at least a first file with a partition field and a second file with an analytical field;

a client computer that requests a business report that requires a calculation involving said first file and said second file;

an application controller that is responsive to said request from said client computer;

a plurality of servers connected to said data storage device and said application controller,

wherein said application controller distributes portions of said calculation to said servers, merges partial result sets that are generated by said servers and transmits said merged result sets to said client computer, wherein said first file is compressed, wherein said first file contains repeating data stored in successive rows, and wherein said servers traverse said first compressed file while said first compressed file is stored in memory to directly retrieve data stored in said first compressed file without decompressing said first compressed file, wherein said retrieved data is used to generate said analytical business report;

wherein said application controller distributes processing of said first file by creating sub-rowsets of said first file and by assigning said sub-rowsets of said first file to a first set of servers, wherein said first set of servers identify unique partition values in said sub-rowsets of said first file and merges said unique partition values identified by said first set of servers and transmits said merged partition values to a second set of servers; and

wherein said second set of servers process sub-rowsets of said second file using said merged partition values to create partial result sets and wherein said second set of servers transmit said partial result sets to said application controller.

8. The system of claim 7 wherein said first file is compressed using run length encoding.

9. The system of claim 7 wherein said application controller divides at least one of said first and second files into sub-rowsets and assigns said sub-rowsets to said servers for processing.

10. The system of claim 7 wherein said application controller distributes processing of said first file by creating sub-rowsets of said first file and by assigning said sub-rowsets of said first file to a first set of servers.

11. The system of claim 7 wherein said application controller merges said partial result sets into a complete result set.

12. The system of claim 11 wherein said application controller annotates said complete result set and transmits said complete result set to said client computer.

13. The system of claim 7 wherein said application controller receives said requests from said client computer via a browser run by said client computer.

14. The system of claim 7 further comprising a web application server connected to a plurality of application controllers, wherein said web application server distributes said requests to said plurality of application controllers using dynamic binding.

15. The system of claim 7 wherein said application controller includes a distribution analysis module that determines a number of servers required to process said request within interactive time.

16. A method for providing an analytical business report that is based on data that includes tables each with a plurality of data fields, comprising the steps of:

inverting said data such that said data fields of said tables in said data are stored in files that contain a row number and a data field;

compressing at least one of said files;

distributing processing of said business report using a first set of severs; and

traversing said compressed file while said compressed file is stored in memory to directly retrieve data stored in said compressed file without decompressing said compressed file, wherein said retrieved data is used to generate said analytical business report;

wherein a first file includes a partition field and a second file includes an analytical field;

further comprising the steps of dividing rows of said first file into sub-rowsets;

assigning said sub-rowsets to said first set of servers to determine unique partition values;

merging said unique partition values generated by said first set of servers;

distributing a control structure related to said unique partition values to a second set said servers;

dividing rows of said second file into sub-rowsets;

assigning said sub-rowsets to said second set of servers; and

calculating partial result sets using said control structure, said analytical values and said unique partition values in said second set of servers.

17. The method of claim 16 further comprising the step of:

determining a number of servers in said first set that are required to process said request in interactive time.

18. The method of claim 16 further comprising the step of:

merging said partial result sets from said second set of servers into a complete result set;

annotating said complete result set; and

transmitting said complete result set to a client computer.