Method of managing slowly changing dimensions

• US 20020038306A1
• Filed: 06/18/2001
• Published: 03/28/2002
• Est. Priority Date: 06/16/2000
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: Cognos Incorporated (International Business Machines Corporation)
• Inventors: Griffiths, Peter James Lawrence, Griffin, David Antony John, Roberts, Michael David, Campbell, Nigel Anthony, Judges, Stephen Hugh
• Granted Patent: US 6,847,973 B2
• Time in Patent Office: Days
• US Class Current: 707/101
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A method of managing slowly changing dimensions in a dimensional database, such as a dimensional data mart, is disclosed. The method consists of providing a dimension template for each dimension undergoing slow changes, which template assigns a behavior to each column of the dimension table. When a row of dimensional data is to be delivered to the dimension table, the table is searched for a record having the key value of the row of data to be delivered. If such a record exists and if any attributes of the row to be delivered have their values changed compared with the existing record, the table is updated according to the behavior type assigned to these attributes in the dimension template, either by overwriting the existing record with the record to be delivered or by creating a new record or records.

Claims

1. A method of managing a slowly changing dimension of a dimensional database, said dimensional database comprising at least one dimension table, said dimension having at least one attribute, said dimension table containing dimensional records, each record comprising values of a key and of the attributes of the dimension, said method comprising the steps of:

a. providing a dimension template, said dimension template assigning a behavior to each attribute of the dimension;

b. providing a dimension update record for the dimension table;

c. adding the update record to the dimension table if no record matching the value of the key of the update record is found in the dimension table;

or

d. rejecting the update record if a record matching the value of the key and all attributes of the update record is found in the dimension table;

or

e. updating the dimension table using the values of attributes of the update record if a record matching the value of the key but not all attributes of the update record is found in the dimension table,

wherein the dimension table is updated in accordance with the behavior assigned to the attributes of the dimension in the dimension template.

2. A method according to claim 1, wherein the behavior assigned to an attribute of the dimension is a Type 1 behavior.

3. A method according to claim 2, wherein the dimension table is updated by overwriting the attribute value of the record found in the dimension table with the attribute value of the update record.

4. A method of claim 3, wherein the key is a business key.

5. A method according to claim 3, wherein the key is a surrogate key.

6. A method according to claim 5, wherein the surrogate key is an internally assigned numerical key.

7. A method according to claim 3, wherein the records of the dimension table further comprise attributes characterizing their update status.

8. A method according to claim 7, wherein the values of the attributes characterizing the update status of the record are updated correspondingly.

9. A method according to claim 1, wherein the behavior assigned to an attribute of the dimension is a Type 2 behavior.

10. A method according to claim 9, wherein the dimension table is updated by adding to the table a new record having the attribute value of the update record.

11. A method according to claim 10, wherein the key is a surrogate key.

12. A method according to claim 11, wherein the surrogate key is an internally assigned numerical key.

13. A method according to claim 10, wherein the records of the dimension table further comprise attribute values characterizing their update status.

14. A method according to claim 13, wherein the values of the attributes characterizing the update status of the records affected by the update are updated correspondingly.

15. An apparatus for managing a slowly changing dimension of a dimensional database, said dimensional database comprising at least one dimension table, said dimension having at least one attribute, said dimension table containing dimensional records, each record comprising values of a key and of the attributes of the dimension, said apparatus comprising:

a. means for providing a dimension template, said dimension template assigning a behavior to each attribute of the dimension;

b. means for providing a dimension update record for the dimension table;

c. means for adding the update record to the dimension table if no record matching the value of the key of the update record is found in the dimension table;

or

d. means for rejecting the update record if a record matching the value of the key and all attributes of the update record is found in the dimension table;

or

e. means for updating the dimension table using the values of attributes of the update record if a record matching the value of the key but not all attributes of the update record is found in the dimension table,

wherein the dimension table is updated in accordance with the behavior assigned to the attributes of the dimension in the dimension template.

16. Computer executable software code stored on a computer readable medium, the code for managing a slowly changing dimension of a dimensional database, said dimensional database comprising at least one dimension table, said dimension having at least one attribute, said dimension table containing dimensional records, each record comprising values of a key and of the attributes of the dimension, said code comprising:

a. code for providing a dimension template, said dimension template assigning a behavior to each attribute of the dimension;

b. code for providing a dimension update record for the dimension table;

c. code for adding the update record to the dimension table if no record matching the value of the key of the update record is found in the dimension table;

or

d. code for rejecting the update record if a record matching the value of the key and all attributes of the update record is found in the dimension table;

or

e. code for updating the dimension table using the values of attributes of the update record if a record matching the value of the key but not all attributes of the update record is found in the dimension table,

wherein the dimension table is updated in accordance with the behavior assigned to the attributes of the dimension in the dimension template.