Automatic determination of OLAP cube dimensions

• US 20020091707A1
• Filed: 10/03/2001
• Published: 07/11/2002
• Est. Priority Date: 10/06/2000
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Keller, Martin
• Granted Patent: US 6,829,621 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/30 of unstructured textual data document management systems G06F16/93
• CPC Class Codes: Y10S 707/956 Hierarchical
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

Means and a computerized method for determining variable dimensions for a multi-dimensional database table, whereby the database table also comprises at least one pre-defined measure dimension for storing values of one or more measure types. Input records are treated as an implicit, yet unknown functional relationship between the measure types as dependent variables and the variable types as independent variables. A regression function is then calculated and used as a prediction model for the measure types based on the variable types, using the input records. The most significant variable types contributing to this prediction model are selected as variable dimensions for the database table.

Claims

1. A computerized method for automatically determining at least one variable dimension for a multi-dimensional database table, said multi-dimensional database table also comprising at least one pre-defined measure dimension for storing values of at least one measure type, the method comprising the steps of:

determining a set of input records, each of said input records comprising a value of at least a first measure type and associated values of a plurality of variable types, said variable types representing candidates for a variable dimension; and

calculating a regression function for use as a prediction model of said value of said measure type, said regression function depending on values of a subset of up to M most significant of said plurality of variable types; and

determining said most significant variable types as variable dimensions of a multi-dimensional database table.

2. The method according to claim 1, further including the step of:

determining members of said variable dimension;

wherein said values of a subset of up to M most significant of said plurality variable types are selected as members of said variable dimension on a lowest hierarchical member level; and

wherein hierarchical aggregations of said members are aggregated as further members of said variable dimension on higher hierarchical member levels.

3. The method according to claim 1, further including the steps of:

calculating for each of said up to M most significant of said plurality variable types a significance value; and

,

associating each variable type not contributing to said regression function a significance value of zero.

4. The method according to claim 3,

wherein in a first phase said method is repeated for a set of L measure types, including the steps of:

calculating L regression functions; and

calculating significance values for each variable type of each of said L regression functions; and

wherein in a second phase for each variable type determining an effective significance value by a balancing function depending on all significance values of said each variable type.

5. The method according to claim 4,

wherein in a third phase, the up to M most significant of said plurality variable types according to said effective significance value are selected as variable dimensions of said multi-dimensional database table, wherein M is a predefined number.

6. The method according to claim 3,

wherein calculating a regression function for use as a prediction model comprises the following steps:

initializing a current prediction model; and

executing a looping sequence.

7. The method of claim 6, wherein the step of executing includes the steps of:

determining f or every variable type not yet reflected in said current prediction model a plurality of partial regression functions, each partial regression function depending only on one of said variable types;

determining for each of said partial regression functions a significance value; and

selecting a most significant partial regression function and a corresponding variable type not yet reflected, adding said most significant partial regression function to said current prediction model; and

,

associating said corresponding variable type with said significance value.

8. The method according to claim 7,

wherein said most significant partial regression function is added only if its addition improves an adjusted correlation coefficient of the prediction model and if its addition does not cause M to be exceeded.

9. The method according to claim 8, further including the steps of:

determining whether significance of a partial regression function included in said current prediction model is reduced after execution of said step of adding said most significant partial regression funtion; and

when significance is reduced, removing said partial regression function and corresponding variable type from said current prediction model.

10. The method according to claim 9,

wherein said looping sequence is terminated if all variable types are reflected in said current prediction model.

11. The method according to claim 9,

wherein said looping sequence is terminated if the significance of said most significant partial regression function is below a second significance threshold.

12. The method according to claim 7,

wherein said partial regression functions are regression polynomials.

13. The method according to claim 12, further comprising the steps of:

calculating significance of all powers of a regression polynomial; and

using a minimum significance of said powers as a significance measure of said regression polynomial.

14. The method according to claim 13, wherein the step of calculating further includes the step of:

calculating said significance of said powers based on F test values for coefficients of said powers.