Method for incremental aggregation of dynamically increasing database data sets

• US 5,794,246 A
• Filed: 04/30/1997
• Issued: 08/11/1998
• Est. Priority Date: 04/30/1997
• Status: Expired due to Term

• Current Assignee: Informatica LLC (Informatica, Inc. (California))
• Original Assignee: Informatica Corporation
• Inventors: Wong, Mon, Nesamoney, Diaz, Sankaran, Mohan, Suresh, Sankaran
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): HOMERE, JEAN RAYMOND
• Application Number:
  US08/846,934
• Time in Patent Office: 468 Days
• Field of Search: 707/3, 707/4, 707/7, 707/101
• US Class Current: 1/1
• CPC Class Codes: G06F 16/244 Grouping and aggregation
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99937 Sorting
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A method of performing incremental aggregation of dynamically increasing database data sets. An embodiment of the present invention operates within a data mart or data warehouse to aggregate data stored within an operational database corresponding to newly received data to provide current information. Initially, a computer server creates an intermediate file which is initialized by the server with an aggregate data set. The aggregate data set consists of data values and count values that each correspond to specific group identifiers. The computer determines if any group identifiers within a new set of inputs data are identical to any group identifiers stored within the intermediate file. If an inputted group identifier matches a stored group identifier, the inputted data value is aggregated with the stored data value and the count value corresponding to the specific stored group identifier is incremented by one. If an inputted group identifier does not match any of the stored group identifiers, the inputted group identifier and corresponding data value are stored within the intermediate file and a count value of one is appended to that specific group identifier. Once all the group identifiers within the new set of input data have been determined, the computer stores all the changes that were made to the intermediate file into the aggregate data set.

Claims

1. In a computer system database, a method for incrementally aggregating dynamically changing an operational database data sets, said method comprising the steps of:

a) creating an intermediate file;

b) initializing intermediate file with an aggregate data set stored within said operational database which includes a set of stored group identifiers and a set of stored data values corresponding to said stored group identifiers;

c) receiving an input group identifier and an input data value that corresponds to said input group identifier that are both contained within a set of new input data;

d) determining if said input group identifier is identical to any of said stored group identifiers stored within said intermediate file;

e) aggregating said input data value to a corresponding said stored data value if said input group identifier is identical to any said stored group identifier found within said intermediate file;

f) storing said input group identifier within said intermediate file if said input group identifier is not identical to any of said stored group identifiers stored within said intermediate file;

g) storing any changes made to said intermediate file into said aggregate data set stored within said operational database.

2. A method as described in claim 1 wherein steps c) through f) are repeated for subsequent input group identifier of said set of new input data.

3. A method as described in claim 1 wherein said aggregate data set further comprises a count value corresponding to said stored group identifier, said count value representing the amount of times said group identifier has been received by said computer system database.

4. A method as described in claim 2 wherein said aggregating step further comprises the step of incrementing a count value that corresponds to said stored group identifier located within said intermediate file.

5. A method as described in claim 3 wherein said step of storing said input group identifier comprises the step of storing a count of one that corresponds to a newly stored input group identifier within said intermediate file.

6. A method as described in claim 1 further comprising the step of determining the average data value by dividing said data value stored within said intermediate file by said count value of a specific group identifier.

7. A method as described in claim 1 further comprising the step of determining the maximum data value stored within said intermediate file.

8. A method as described in claim 1 further comprising the step of determining the minimum data value stored within said intermediate file.

9. A method as described in claim 1 further comprising the step of determining the median value of a user defined value or variable stored within said intermediate file.

10. A method as described in claim 1 further comprising the step of determining the rank of a user defined value or variable stored within said intermediate file.

11. A method as described in claim 1 further comprising the step of determining the variance of a user defined value or variable x located within said intermediate file according to:

##EQU2## wherein x_i is one of said elements of a user defined value x and n is the number of elements in a set of x, and if the value of n is equal to 1, said variance is equal to zero.

12. A method as described in claim 8 further comprising the step of determining the standard deviation based a relationship of executing the square root of the variance of x.

13. A method as described in claim 1 wherein said determining step comprises the step of using a b-tree as a search mechanism to search through said intermediate file.

14. A method as described in claim 1 wherein said initializing step comprises the step of storing said intermediate file within random access memory of said computer system.

15. A method as described in claim 1 wherein said initialing step comprises the step of creating a copy of the elements that are stored within said intermediate file after said aggregate data set is stored within said intermediate file.

16. A method as described in claim 1 wherein said storing step comprises the step of creating a copy of the elements that are stored within said intermediate file before storing the changes that were made to said intermediate file into said aggregate data set stored within said operational database.