Relational database modifications based on multi-dimensional database modifications

• US 5,905,985 A
• Filed: 06/30/1997
• Issued: 05/18/1999
• Est. Priority Date: 06/30/1997
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Malloy, William Earl, Tomlyn, Craig Reginald
• Primary Examiner(s): Von Buhr, Maria N.
• Assistant Examiner(s): Coby, Frantz
• Application Number:
  US08/884,695
• Time in Patent Office: 687 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/5, 707/100, 707/101, 707/102
• US Class Current: 1/1
• 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
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method, apparatus, and article of manufacture for using a relational database management system to support on-line analytical processing (OLAP) systems. A relational schema is defined from a multi-dimensional database definition. The multi-dimensional database definition has one or more dimensions having one or more members. The relational schema includes a fact table and related dimension tables. The fact table has a dimension column for all but one of the dimensions of the multi-dimensional database and a value column for each member of the remaining dimension. The fact table has one or more rows that contain values corresponding to the intersection of the members identified by the dimension columns and the members associated with the value columns. Each dimension table has a row for each member of a dimension. Each row of each dimension table includes a multi-dimensional member identifier and a relational member identifier. A relational database is created based on the defined relational schema. Rows are accessed from the relational database using the multi-dimensional member identifiers by joining the dimension tables to the fact table through the relational member identifiers.

Claims

1. A method of executing a command in a computer to perform a database operation on a relational database stored on a data storage device, the method comprising the steps of:

(a) defining a relational schema from a multi-dimensional database definition, wherein the multi-dimensional database definition has one or more dimensions having one or more members, the relational schema includes a fact table and related dimension tables, one of the dimension tables is an anchor dimension table that does not intersect the fact table with a common column and the remaining dimension tables are non-anchor dimension tables that intersect the fact table with common columns, the fact table has a dimension column for each of the non-anchor dimension tables of the multi-dimensional database and a value column for each member of the anchor dimension table, the fact table has one or more rows that contain values corresponding to the intersection of the members identified by the dimension columns and the members associated with the value columns, each dimension table has a row for each member of a dimension, and each row in the dimension tables includes a multi-dimensional member identifier and a relational member identifier; and

(b) creating a relational database based on the defined relational schema.

2. The method of claim 1 above, further comprising the step of receiving an indication that the multi-dimensional database definition has been modified.

3. The method of claim 2 above, further comprising the step of modifying the relational database based on the received indication.

4. The method of claim 3 above, wherein the step of modifying further comprises the steps of:

retaining relational member identifiers in the dimension tables;

marking rows associated with the retained relational member identifiers as available; and

reusing the marked rows.

5. The method of claim 4 above, wherein each row of each dimension table contains a member name.

6. The method of claim 5 above, wherein the member name contains a NULL value to indicate that the row is available.

7. An apparatus for executing a command to perform a database operation on a relational database, comprising:

(a) a computer having a data storage device coupled thereto, wherein the data storage device stores the relational database;

(b) one or more computer programs, performed by the computer, for defining a relational schema from a multi-dimensional database definition, wherein the multi-dimensional database definition has one or more dimensions having one or more members, the relational schema includes a fact table and related dimension tables, one of the dimension tables is an anchor dimension table that does not intersect the fact table with a common column and the remaining dimension tables are non-anchor dimension tables that intersect the fact table with common columns, the fact table has a dimension column for each of the non-anchor dimension tables of the multi-dimensional database and a value column for each member of the anchor dimension table, the fact table has one or more rows that contain values corresponding to the intersection of the members identified by the dimension columns and the members associated with the value columns, each dimension table has a row for each member of a dimension, and each row in the dimension tables includes a multi-dimensional member identifier and a relational member identifier, and for creating a relational database based on the defined relational schema.

8. The apparatus of claim 7 above, further comprising means for receiving an indication that the multi-dimensional database definition has been modified.

9. The apparatus of claim 8 above, further comprising means for modifying the relational database based on the received indication.

10. The apparatus of claim 9 above, wherein the means for modifying further comprise:

means for retaining relational member identifiers in the dimension tables;

means for marking a row associated with the retained relational member identifiers as available; and

reusing the marked rows.

11. The apparatus of claim 10 above, wherein each row of each dimension table contains a member name.

12. The apparatus of claim 11 above, wherein the member name contains a NULL value to indicate that the row is available.

13. An article of manufacture comprising a program storage medium readable by a computer and embodying one or more instructions executable by the computer to perform method steps for executing a command to perform a database operation on a relational database stored on a data storage device, the method comprising the steps of:

(a) defining a relational schema from a multi-dimensional database definition, wherein the multi-dimensional database definition has one or more dimensions having one or more members, the relational schema includes a fact table and related dimension tables, one of the dimension tables is an anchor dimension table that does not intersect the fact table with a common column and the remaining dimension tables are non-anchor dimension tables that intersect the fact table with common columns the fact table has a dimension column for each of the non-anchor dimension tables of the multi-dimensional database and a value column for each member of the anchor dimension table, the fact table has one or more rows that contain values corresponding to the intersection of the members identified by the dimension columns and the members associated with the value columns, each dimension table has a row for each member of a dimension, and each row in the dimension tables includes a multi-dimensional member identifier and a relational member identifier; and

(b) creating a relational database based on the defined relational schema.

14. The method of claim 13 above, further comprising the step of receiving an indication that the multi-dimensional database definition has been modified.

15. The method of claim 14 above, further comprising the step of modifying the relational database based on the received indication.

16. The method of claim 15 above, wherein the step of modifying further comprises the steps of:

retaining relational member identifiers in the dimension tables;

marking a row associated with the retained relational member identifiers as available; and

reusing the marked rows.

17. The method of claim 16 above, wherein each row of each dimension table contains a member name.

18. The method of claim 17 above, wherein the member name contains a NULL value to indicate that the row is available.