Method and apparatus for populating multiple data marts in a single aggregation process

• US 6,282,544 B1
• Filed: 05/24/1999
• Issued: 08/28/2001
• Est. Priority Date: 05/24/1999
• Status: Expired due to Term

• Current Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Original Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Inventors: Lore, Michael Dean, Tse, Eva Man-Yan, Attaway, James Daniel
• Primary Examiner(s): Black, Thomas
• Assistant Examiner(s): Pardo, Thuy N.
• Application Number:
  US09/317,773
• Time in Patent Office: 827 Days
• Field of Search: 707/2, 707/100, 707/101, 707/201, 707/1, 707/3, 707/6, 707/8, 707/10, 705/10
• US Class Current: 707/600
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06Q 10/06 Resources, workflows, human or project management; Enterprise or organisation planning; Enterprise or organisation modelling
• CPC Class Codes: Y10S 707/957 Multidimensional
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99936 Pattern matching access
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A method of populating multiple data marts in a single operation from a set of transactional data held in a database in a single aggregation process, in which aggregate values are calculated only once, a determination is made as to which output data marts required the aggregate value, and the aggregate values are output to the appropriate data marts. Dimension data associated with the output aggregate records is also output to the appropriate data marts.

Claims

1. A method of generating a plurality of output data marts from a first data mart, said first data mart comprising input fact data and at least one dimension table, said method comprising:

generating aggregated fact data including a plurality of records representative of said fact data summarized at one or more levels other than the level of the input fact data;

distributing said aggregated fact data to said plurality of output data marts, wherein certain aggregated fact data records are distributed to more than one of said data marts, and wherein the set of fact data records distributed to at least some of the output data marts are not identical;

providing at least one data structure specifying levels or level cross products required by each output data mart;

establishing the aggregate fact data records which need to be generated from said input fact data; and

generating each required aggregate fact data record, and for each generated aggregate fact data record;

1) establishing from said at least one data structure which of said output data marts requires said aggregated fact data record; and

2) providing each of said output data marts which requires said aggregate fact data record with said aggregated fact data record.

2. A method according to claim 1, wherein each aggregated fact data record is generated only once and then output to the output data marts requiring said aggregate fact data record.

3. A method according to claim 1 wherein said data structure comprises a list of levels or level cross products from said at least one dimension, and wherein each element in said list is associated with one or more output data marts, thereby allowing each aggregate fact data record to be associated with appropriate output data marts based on the level or level cross product with which the record is associated.

4. A method of generating a plurality of output data marts from a first data mart, said first data mart comprising input fact data and at least one dimension table, said method comprising:

generating aggregated fact data including a plurality of records representative of said fact data summarized at one or more levels other than the level of the input fact data;

distributing said aggregated fact data to said plurality of output data marts, wherein certain aggregated fact data records are distributed to more than one of said data marts, and wherein the set of fact data records distributed to at least some of the output data marts are not identical; and

generating dimension tables for each of the output data marts from an input dimension table wherein each generated target dimension table contains only dimension records associated with levels requested by the associated data mart.

5. A method according to claim 4 wherein aggregated fact data is shared by different data marts.

6. The method of claim 4 wherein at least one of the generated dimension tables is shared by two or more of the output data marts.

7. The method of claim 4 wherein the step of generating target dimension tables for a particular dimension is carried out by generating a list of levels associated with said target dimension tables and outputting dimension records into a target dimension table only if the level associated with said dimension record is in the list of levels associated with said target dimension table.

8. The method of claim 7 wherein

said list of levels for a particular target dimension table is generated by merging lists of levels in the appropriate dimension associated with each of the data marts to which the target dimension table corresponds, and wherein

each list of levels associated with a particular data mart comprises the set of levels in the corresponding dimension of all the cross products with which the data mart is associated.

9. A method according to claim 8 wherein generated dimension tables in each data mart contain only records from said input dimension tables which appear in the aggregated output fact data output to that data mart.

10. A method according to claim 9 wherein the step of generating dimension tables for each data mart in a particular dimension is carried out by

a) providing storing means for associating dimension records with each data mart, and initially associating each dimension record with no data marts;

b) performing the following steps for each fact data entry, wherein each fact data entry includes a set of dimension entries corresponding to a specific dimension record in each dimension;

1) establishing the level associated with the dimension entry in each dimension,

2) establishing the data marts associated with said fact data entry based on the cross product of said levels, and for each dimension, associating each of said data marts with the dimension record corresponding to the dimension entry in said storing means if the data mart is not already associated with said dimension entry;

c) outputting each dimension record to each output dimension table if the dimension record is associated in said storing means with a data mart with which said output dimension table is associated.

11. A method according to claim 4 wherein generated dimension tables are shared by different output data marts.

12. A method according to claim 4 wherein each aggregated fact data record is generated only once and then output to the output data marts requiring said aggregate fact data record.