Method and apparatus for simplified and flexible selection of aggregate and cross product levels for a data warehouse

• US 6,163,774 A
• Filed: 05/24/1999
• Issued: 12/19/2000
• Est. Priority Date: 05/24/1999
• Status: Expired due to Term

• Current Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Original Assignee: PLATINUM Technology IP, Inc. (Broadcom, Inc.)
• Inventors: Tse, Eva Man-Yan, Lore, Michael Dean
• Primary Examiner(s): Alam, Hosain T.
• Application Number:
  US09/317,247
• Time in Patent Office: 575 Days
• Field of Search: 707/2, 707/3, 707/4, 707/5-6, 707/100-102, 706/12, 706/47, 706/46, 345/355, 345/357, 345/440, 345/326, 705/10, 705/36
• US Class Current: 705/36.00R
• CPC Class Codes: G06Q 10/06 Resources, workflows, human or project management; Enterprise or organisation planning; Enterprise or organisation modelling
• CPC Class Codes: G06Q 40/06 Asset management; Financial planning or analysis
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A method of defining aggregate levels to be used in aggregation in a data store having one or more dimensions. Levels are defined corresponding to attributes in the dimension, so that data can be aggregated into aggregates corresponding to values of those attributes. The invention provides for the definition of sub-levels which act as levels but define which detail entries in the associated dimension will contribute to the sub-level. The invention also provides for the definition of level groups. A level group can replace a level in a level cross-product and such a cross product is then expanded before aggregation into a set of cross products, each containing one of the level group entries.

Claims

1. A method of defining an aggregate level in at least one dimension corresponding to groupings of detail data entries in a database, said dimension detail entries representing a set of entities in said dimension, wherein each dimension detail entry comprises at least one attribute field, wherein values stored in said attribute fields are values of an attribute, said attribute being common to each of said entities, said method of defining an aggregate level comprising the steps of:

specifying as a level rule at least one attribute in said dimension to be associated with said level, such that said level has at least one member, each member corresponding to an attribute value of each of said at least one attributes, and each member being associated with at least one detail entry in said dimension with the same attribute values as the level member;

wherein

at least one of said rules comprises a logical expression dependent on the attribute values of an attribute incorporated in said level definition and determines whether level members representing attribute values satisfying said logical expression are present in the level;

whereby data in said database being aggregated can be grouped based on the values of attributes incorporated in said level rule, and a determination can be made as to whether a specific data entry should contribute to a corresponding group based on whether it satisfies the logical expression associated with said level rule.

2. A method of defining an aggregate sublevel in at least one dimension corresponding to groupings of detail data entries in a database, said dimension detail entries representing a set of entities in said dimension, wherein each dimension detail entry comprises at least one attribute field, wherein values stored in said attribute fields are values of an attribute common to each of said entities, and wherein said database includes fact data entries each associated with a detail entry in said dimension, said method of defining aggregate sublevels for a dimension comprising the steps of:

defining a level by specifying at least one attribute in said dimension to be associated with said level, such that said level has at least one member, each member corresponding to an attribute value of each of said at least one attributes, and each member being associated with at least one detail entry in said dimension with the same attribute values as the level member;

wherein

specifying a logical expression which each detail entry in said dimension may or may not satisfy to define a subset of the detail entries contributing to said level thereby to form said sublevel, such that level members in the sublevel are a subset of the level members in the level, and such that each member in said sublevel is associated with a subset of the detail entries of the corresponding level member in said level;

whereby said sublevel acts as a level but furthermore provides control over which fact data entries in said database contribute to the members of the sublevel during aggregation.

3. A method in accordance with claim 2 wherein each of said levels in a dimension is defined by a set of rules, each rule specifying an attribute of said dimension on which said level is to be grouped.

4. A method in accordance with claim 2 wherein each of said sub-levels in a dimension is defined by at least one rule specifying conditions defining which detail entries in that dimension are to be associated with level members in the sub-level.

5. A method in accordance with claim 4 wherein said rule specifies conditions dependent on field values of said detail entries.

6. A method of defining a plurality of cross products of levels for use in aggregating input fact data comprising fact data entries in a database, wherein said database comprises a plurality of dimensions each representing a set of entities and containing dimension detail entries, each representing one of said entities;

wherein each dimension detail entry comprises at least one attribute field, and wherein values stored in said attribute fields are values of an attribute, said attribute being common to each of said entities; and

wherein said levels are defined in each of said dimensions by specifying at least one attribute in said dimension to be associated with said level, such that said level has at least one member, each member corresponding to an attribute value of each of said at least one attributes, and each member being associated with at least one detail entry in said dimension with the same attribute values defining the level member;

said method comprising the steps of;

representing at least two of said levels in one of said dimensions as a level group;

selecting at most one level or level group from each of said dimensions, at least one of which is a level group to define a level group cross-product;

converting said level group cross product into said plurality of level cross products by generating a level cross product for every combination of levels in the level groups of the cross product, each level cross product generated incorporating a level from each level group in said level group cross product and also incorporating any levels in said level cross product which do not comprise a level group;

whereby a user of said database can generate a first set of level cross-products involving a first level in a dimension and a second set of level cross products identical except for the replacement of said first level with a second level in said dimension, without explicitly defining both said first and second sets.