METHOD AND APPARATUS FOR AGGREGATION OF DATA IN A DATABASE MANAGEMENT SYSTEM

• US 20020099691A1
• Filed: 06/24/1998
• Published: 07/25/2002
• Est. Priority Date: 06/24/1998
• Status: Abandoned Application

• Current Assignee: PLATINUM Technology IP, Inc. (Broadcom, Inc.)
• Original Assignee: PLATINUM Technology IP, Inc. (Broadcom, Inc.)
• Inventors: TSE, EVA MAN-YAN, LORE, MICHAEL DEAN, DYSART, JOHN ANDREW
• Application Number:
  US09/109,969
• Publication Number:
  US 20020099691A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2237 Vectors, bitmaps or matrices

Abstract

An aggregation engine for a data warehouse which provides an indexing technique which allows the measures in a fact table data entry to be added to the appropriate aggregate bucket by mapping the each of the dimension keys to an aggregate index within a level in that dimension and then calculating an overall index using the aggregate index from each dimension which is then mapped onto the aggregate bucket in question. A rolling cache is also provided allowing frequently or recently used aggregate buckets to be represented in memory rather than in a file, and merged with the equivalent bucket in an aggregate file when necessary, so that the slower access to the address file and the aggregate files can be avoided.

Claims

1. A method of aggregating fact data from a set of fact data records into appropriate aggregate buckets, wherein each of said fact data records is associated with an entry in each of a plurality of dimensions identifying said fact data record, and wherein said aggregate buckets relate to specific values in levels in said plurality of dimensions;

said method comprising the steps of;

identifying the required level combinations to which each fact data record will contribute;

identifying the appropriate aggregate bucket associated with each of said required level combinations with which said fact data record is associated;

incorporating data in said fact data record into each of said aggregate buckets; and

repeating said steps for each fact data record.

2. A method for aggregating fact data from a set of fact data records into appropriate aggregate buckets;

wherein said fact data records are associated with an entry in each of a plurality of different dimensions identifying said fact data record, and wherein each of said fact data records is associated with one or more aggregates, each of said aggregates corresponding to a level cross product;

said method comprising the steps of comprising;

storing a set of level cross products for which aggregates are required;

calculating the number of aggregates associated with each of said level cross products whereby to establish a set of index values required by each level cross product;

storing a level cross product reference index for referencing the associated set of index values;

identifying an aggregate index for a specified aggregate combination from the set of aggregate index values for the associated level cross product;

establishing an overall aggregate index for an aggregate combination using said level cross product index and said aggregate index; and

mapping said overall index onto one or more aggregate buckets associated with said aggregate combination.

3. A method according to claim 2, wherein said aggregate index values associated with a level cross product are equi-spaced values over a range, and wherein the ranges for the aggregate indexes of each of the level cross products do not overlap, and wherein the reference index value associated with each of each of the level products identifies the starting point of the range of aggregate indexes associated with the level cross product.

4. A method of storing data in a memory cache arranged to hold a plurality of data entries identifiable by an index value and a file holding further data entries of the same type as the memory cache;

comprising the steps of;

identifying whether or not a certain index has an associated data entry in said cache;

generating a new data entry in said cache for a new item of input data corresponding to said index if said cache does not have an entry for said index, regardless of whether or not said file contains an entry corresponding to said index;

combining data in a data entry in said cache with a new data entry if said data entry has the same index as said new data entry;

transferring data from said data entries in said cache to said file and removing the corresponding entries from said cache.

5. A method according to claim 4 wherein the transferring step comprises transferring the least recently referenced data entries in said cache.

6. A method according to claim 4 wherein the transferring step comprises transferring the least frequently referenced data entries in said cache.

7. A method according to claim 4 wherein the transferring step comprises transferring a plurality of data entries when the number of data entries in said cache exceeds a predetermined value.

8. A method according to claim 4 wherein the transferring step comprises transferring the least frequently and the least recently referenced data entries in said cache.

9. A method according to claim 4 wherein the data entries comprise aggregate buckets corresponding to predefined aggregates or combinations of aggregates.

10. An aggregation engine for placing fact data from a set of fact data records into appropriate aggregate buckets;

wherein said fact data records are associated with an entry in each of a plurality of different dimensions identifying said fact data record, and wherein each of said fact data records is associated with one or more aggregates, each of said aggregates corresponding to a level cross product;

said aggregation engine comprising;

means for storing a set of level cross products for which aggregates are required;

means for calculating the number of aggregates associated with each of said level cross products whereby to establish a set of aggregate index values required by each level cross product;

means for storing a level cross product reference index for referencing the associated set of aggregate index values;

means for identifying an aggregate index for a specified aggregate combination from the set of index values for the associated level cross product;

means for establishing an overall index for said aggregate combination using said level cross product index, and said aggregate index; and

means for mapping said overall index onto one or more aggregate buckets associated with said aggregate combination.

11. An aggregation engine according to claim 10, wherein said aggregate index values associated with a level cross product are equi-spaced values over a range, and wherein the ranges for the aggregate indexes of each of the level cross products do not overlap, and wherein the reference index value associated with each of each of the level products identifies the starting point of the range of aggregate indexes associated with the level cross product.

13. Apparatus according to claim 12 wherein the means for transferring transfers the least recently referenced data entries in said cache.

14. Apparatus according to claim 12 wherein the means for transferring transfers the least frequently referenced data entries in said cache.

15. Apparatus according to claim 12 wherein the means for transferring transfers a plurality of data entries when the number of data entries in said cache exceeds a predetermined value.

16. Apparatus according to claim 12 wherein the means for transferring transfers data the least frequently and the least recently referenced data entries in said cache.

17. Apparatus according to claim 12 wherein the data entries comprise aggregate buckets corresponding to predefined aggregates or combinations of aggregates.

12. A system for storing data comprising

a memory cache arranged to hold a plurality of data entries identifiable by an index value,

a file arranged to hold further data entries of the same type as the memory cache;

means for identifying whether or not a certain index has an associated data entry in said cache;

means for generating a new data entry in said cache for a new item of input data corresponding to said index if said cache does not have an entry for said index, regardless of whether or not said file contains an entry corresponding to said index;

means for combining data in a data entry in said cache with a new data entry if said data entry has the same index as said new data entry;

means for transferring data from said data entries in said cache to said file and removing the corresponding entries from said cache.