Relational database implementation of a multi-dimensional database

• US 5,926,818 A
• Filed: 06/30/1997
• Issued: 07/20/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
• Primary Examiner(s): Amsbury, Wayne
• Assistant Examiner(s): LEWIS, CHERYL RENEA
• Application Number:
  US08/884,696
• Time in Patent Office: 750 Days
• Field of Search: 707/3, 707/205, 707/506, 707/100, 707/101
• 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

Abstract

A method, apparatus, and article of manufacture for using a relational database management system to support on-line analytical processing (OLAP) systems. A multi-dimensional database is represented as a relational schema in the relational database. The multi-dimensional database has one or more dimensions. Each dimension has one or more members. The relational schema includes a fact table and one or more related dimension tables. Additionally, 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 also has at least one row that contains values corresponding to the intersection of the members identified by the dimension columns and the members associated with the value columns.

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 step of:

(a) representing a multi-dimensional database as a relational schema in the relational database, wherein the multi-dimensional database has one or more dimensions having one or more members, the relational schema includes a fact table and one or more related dimension tables, with one of the dimension tables being an anchor dimension table that does not intersect the fact table with a common column and the remaining dimension tables being non-anchor dimension tables that intersect the fact table with common columns, the fact table has a dimension column for each of the dimensions of the multi-dimensional database which correspond to the non-anchor dimension tables and a value column for each member of the remaining dimension which corresponds to the anchor dimension table, and the fact table has at least one row that contains values corresponding to the intersection of the members identified by the dimension columns and the members associated with the value columns.

2. The method of claim 1 above, wherein each dimension table has a row for each member of a dimension.

3. The method of claim 2 above, wherein each row of the fact table has a column for every member of a selected dimension.

4. The method of claim 1 above, wherein the multi-dimensional database accesses data using one or more multi-dimensional member identifiers, wherein the relational database accesses data using one or more relational member identifiers, and further comprising the steps of:

receiving a dimension identifier identifying one or more multi-dimensional member identifiers; and

mapping the identified multi-dimensional member identifiers to relational member identifiers in the relational database.

5. The method of claim 4 above, further comprising the step of accessing data from the relational database by joining selected dimension tables with the fact table using the relational member identifiers.

6. 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 representing a multi-dimensional database as a relational schema in the relational database, wherein the multi-dimensional database has one or more dimensions having one or more members, the relational schema includes a fact table and one or more related dimension tables, with one of the dimension tables being an anchor dimension table that does not intersect the fact table with a common column and the remaining dimension tables being non-anchor dimension tables that intersect the fact table with common columns, the fact table has a dimension column for each of the dimensions of the multi-dimensional database which correspond to the non-anchor dimension tables and a value column for each member of the remaining dimension which corresponds to the anchor dimension table, and the fact table has at least one row that contains values corresponding to the intersection of the members identified by the dimension columns and the members associated with the value columns.

7. The apparatus of claim 6 above, wherein each dimension table has a row for each member of a dimension.

8. The apparatus of claim 7 above, wherein each row of the fact table has a column for every member of a selected dimension.

9. The apparatus of claim 6 above, wherein the multi-dimensional database accesses data using one or more multi-dimensional member identifiers, wherein the relational database accesses data using one or more relational member identifiers, and further comprising:

means for receiving a dimension identifier identifying one or more multi-dimensional member identifiers; and

means for mapping the identified multi-dimensional member identifiers to relational member identifiers in the relational database.

10. The apparatus of claim 9 above, further comprising means for accessing data from the relational database by joining selected dimension tables with the fact table using the relational member identifiers.

11. 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 step of:

(a) representing a multi-dimensional database as a relational schema in the relational database, wherein the multi-dimensional database has one or more dimensions having one or more members, the relational schema includes a fact table and one or more related dimension tables, with one of the dimension tables being an anchor dimension table that does not intersect the fact table with a common column and the remaining dimension tables being non-anchor dimension tables that intersect the fact table with common columns, the fact table has a dimension column for each of the dimensions of the multi-dimensional database which correspond to the non-anchor dimension tables and a value column for each member of the remaining dimension which corresponds to the anchor dimension table, and the fact table has at least one row that contains values corresponding to the intersection of the members identified by the dimension columns and the members associated with the value columns.

12. The method of claim 11 above, wherein each dimension table has a row for each member of a dimension.

13. The method of claim 12 above, wherein each row of the fact table has a column for every member of a selected dimension.

14. The method of claim 11 above, wherein the multi-dimensional database accesses data using one or more multi-dimensional member identifiers, wherein the relational database accesses data using one or more relational member identifiers, and further comprising the steps of:

receiving a dimension identifier identifying one or more multi-dimensional member identifiers; and

mapping the identified multi-dimensional member identifiers to relational member identifiers in the relational database.

15. The method of claim 14 above, further comprising the step of accessing data from the relational database by joining selected dimension tables with the fact table using the relational member identifiers.