Accessing multi-dimensional data by mapping dense data blocks to rows in a relational database

• US 5,978,796 A
• Filed: 06/30/1997
• Issued: 11/02/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, Robinson, Gary
• Primary Examiner(s): Amsbury, Wayne
• Assistant Examiner(s): CHANNAVAJJALA, SRIRAMA T
• Application Number:
  US08/885,112
• Time in Patent Office: 855 Days
• Field of Search: 707/6, 707/7, 707/9, 707/10, 707/100, 707/101, 707/104, 707/200, 707/201, 707/503, 707/504, 707/508, 707/509, 707/1-5
• 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/99933 Query processing, i.e. searching

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 database has a fact table and at least one related dimension table. A dimension identifier is received that identifies a data block in a multi-dimensional database. The dimension identifier comprises one or more multi-dimensional member identifiers. Rows are accessed from the fact table through the related dimension tables using the received dimension identifier. Then, the accessed rows are formatted as a data block for the multi-dimensional database.

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, wherein the relational database has a fact table, one or more related dimension tables, and relational member identifiers for accessing data from the fact table through the related dimension tables, the method comprising the steps of:

(a) receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers; and

(b) accessing rows from the fact table through the related dimension tables using the received dimension identifier, wherein 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.

2. The method of claim 1 above, further comprising the step of formatting the accessed rows as a data block for the multi-dimensional database.

3. The method of claim 1 above, wherein the step of accessing rows further comprises the step of mapping the identified multi-dimensional member identifiers to relational member identifiers.

4. A method of executing a command in a computer to perform a database operation on a relational database stored on a data storage device, wherein the relational database has a fact table, one or more related dimension tables, and relational member identifiers for accessing data from the fact table through the related dimension tables, the method comprising the steps of:

receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, wherein the multi-dimensional member identifiers identify an offset into the data block;

accessing rows from the fact table through the related dimension tables using the received dimension identifier, wherein the accessed rows contain values;

determining an offset into the data block using the multi-dimensional member identifiers; and

storing the values contained in the accessed rows into the data block at the determined offset.

5. A method of executing a command in a computer to perform a database operation on a relational database stored on a data storage device, wherein the relational database has a fact table, one or more related dimension tables, and relational member identifiers for accessing data from the fact table through the related dimension tables, the method comprising the steps of:

receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, wherein the multi-dimensional member identifiers identify an offset into the data block, and wherein the data block contains values;

accessing rows from the fact table through the related dimension tables using the received dimension identifier;

determining an offset into the data block using the multi-dimensional member identifiers;

accessing the values contained in the data block at the determined offset; and

storing the accessed values in rows of the relational database.

6. A method of executing a command in a computer to perform a database operation on a relational database stored on a data storage device, wherein the relational database has a fact table and at least one related dimension table, the method comprising the steps of:

(a) receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers;

(b) accessing rows from the fact table through the related dimension tables using the received dimension identifier; and

(c) formatting the accessed rows as a data block for the multi-dimensional database.

7. The method of claim 6 above, wherein the relational database has relational member identifiers for accessing data from the fact table through the related dimension tables.

8. The method of claim 7 above, wherein the step of accessing rows further comprises the step of mapping the identified multi-dimensional member identifiers to relational member identifiers.

9. A method of executing a command in a computer to perform a database operation on a relational database stored on a data storage device, wherein the relational database has a fact table and at least one related dimension table, the method comprising the steps of:

receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, wherein the multi-dimensional member identifiers identify an offset into the data block;

accessing rows from the fact table through the related dimension tables using the received dimension identifier, wherein the accessed rows contain values;

formatting the accessed rows as a data block for the multi-dimensional database;

determining an offset into the data block using the multi-dimensional member identifiers; and

storing the values contained in the accessed rows into the data block at the determined offset.

10. A method of executing a command in a computer to perform a database operation on a relational database stored on a data storage device, wherein the relational database has a fact table and at least one related dimension table, the method comprising the steps of:

receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers wherein the multi-dimensional member identifiers identify an offset into the data block, and wherein the data block contains values;

accessing rows from the fact table through the related dimension tables using the received dimension identifier; and

formatting the accessed rows as a data block for the multi-dimensional database;

determining an offset into the data block using the multi-dimensional member identifiers;

accessing the values contained in the data block at the determined offset; and

storing the accessed values in rows of the relational database.

11. 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, and wherein the relational database has a fact table, one or more related dimension tables, and relational member identifiers for accessing data from the fact table through the related dimension tables;

(b) one or more computer programs, performed by the computer, for receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers and for accessing rows from the fact table through the related dimension tables using the received dimension identifier, wherein 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.

12. The apparatus of claim 11 above, further comprising means for formatting the accessed rows as a data block for the multi-dimensional database.

13. The apparatus of claim 11 above, wherein the means for accessing rows further comprises means for mapping the identified multi-dimensional member identifiers to relational member identifiers.

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

a computer having a data storage device coupled thereto, wherein the data storage device stores the relational database, and wherein the relational database has a fact table, one or more related dimension tables, and relational member identifiers for accessing data from the fact table through the related dimension tables;

one or more computer programs, performed by the computer, for receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, wherein the multi-dimensional member identifiers identify an offset into the data block, for accessing rows from the fact table through the related dimension tables using the received dimension identifier, wherein the accessed rows contain values, for determining an offset into the data block using the multi-dimensional member identifiers, and for storing the values contained in the accessed rows into the data block at the determined offset.

15. 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, and wherein the relational database has a fact table, one or more related dimension tables, and relational member identifiers for accessing data from the fact table through the related dimension tables;

(b) one or more computer programs, performed by the computer, for receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, wherein the multi-dimensional member identifiers identify an offset into the data block, and wherein the data block contains values, for accessing rows from the fact table through the related dimension tables using the received dimension identifier, for determining an offset into the data block using the multi-dimensional member identifiers, for accessing the values contained in the data block at the determined offset, and for storing the accessed values in rows of the relational database.

16. 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, and wherein the relational database has a fact table and at least one related dimension table;

(b) one or more computer programs, performed by the computer, for receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, accessing rows from the fact table through the related dimension tables using the received dimension identifier, and formatting the accessed rows as a data block for the multi-dimensional database.

17. The apparatus of claim 16 above, wherein the relational database has relational member identifiers for accessing data from the fact table through the related dimension tables.

18. The apparatus of claim 17 above, wherein the means for accessing rows further comprises the means for mapping the identified multi-dimensional member identifiers to relational member identifiers.

19. 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, and wherein the relational database has a fact table and at least one related dimension table;

(b) one or more computer programs, performed by the computer, for receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, wherein the multi-dimensional member identifiers identify an offset into the data block, for accessing rows from the fact table through the related dimension tables using the received dimension identifier, wherein the accessed rows contain values, for formatting the accessed rows as a data block for the multi-dimensional database, for determining an offset into the data block using the multi-dimensional member identifiers, and for storing the values contained in the accessed rows into the data block at the determined offset.

20. 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, and wherein the relational database has a fact table and at least one related dimension table;

(b) one or more computer programs, performed by the computer, for receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, wherein the multi-dimensional member identifiers identify an offset into the data block, and wherein the data block contains values, for accessing rows from the fact table through the related dimension tables using the received dimension identifier, for formatting the accessed rows as a data block for the multi-dimensional database, for determining an offset into the data block using the multi-dimensional member identifiers, for accessing the values contained in the data block at the determined offset, and for storing the accessed values in rows of the relational database.

21. 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, wherein the relational database has a fact table, one or more related dimension tables, and relational member identifiers for accessing data from the fact table through the related dimension tables, the method comprising the steps of:

(a) receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers; and

(b) accessing rows from the fact table through the related dimension tables using the received dimension identifier, wherein 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.

22. The method of claim 21 above, further comprising the step of formatting the accessed rows as a data block for the multi-dimensional database.

23. The method of claim 21 above, wherein the step of accessing rows further comprises the step of mapping the identified multi-dimensional member identifiers to relational member identifiers.

24. 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, wherein the relational database has a fact table, one or more related dimension tables, and relational member identifiers for accessing data from the fact table through the related dimension tables, the method comprising the steps of:

receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, wherein the multi-dimensional member identifiers identify an offset into the data block;

accessing rows from the fact table through the related dimension tables using the received dimension identifier, wherein the accessed rows contain values;

determining an offset into the data block using the multi-dimensional member identifiers; and

storing the values contained in the accessed rows into the data block at the determined offset.

25. 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, wherein the relational database has a fact table, one or more related dimension tables, and relational member identifiers for accessing data from the fact table through the related dimension tables, the method comprising the steps of:

receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, wherein the multi-dimensional member identifiers identify an offset into the data block, and wherein the data block contains values;

accessing rows from the fact table through the related dimension tables using the received dimension identifier;

determining an offset into the data block using the multi-dimensional member identifiers;

accessing the values contained in the data block at the determined offset; and

storing the accessed values in rows of the relational database.

26. 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, wherein the relational database has a fact table and at least one related dimension table, the method comprising the steps of:

(a) receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers;

(b) accessing rows from the fact table through the related dimension tables using the received dimension identifier; and

(c) formatting the accessed rows as a data block for the multi-dimensional database.

27. The method of claim 26 above, wherein the relational database has relational member identifiers for accessing data from the fact table through the related dimension tables.

28. The method of claim 27 above, wherein the step of accessing rows further comprises the step of mapping the identified multi-dimensional member identifiers to relational member identifiers.

29. 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, wherein the relational database has a fact table and at least one related dimension table, the method comprising the steps of:

receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers, wherein the multi-dimensional member identifiers identify an offset into the data block;

accessing rows from the fact table through the related dimension tables using the received dimension identifier, wherein the accessed rows contain values;

formatting the accessed rows as a data block for the multi-dimensional database

determining an offset into the data block using the multi-dimensional member identifiers; and

storing the values contained in the accessed rows into the data block at the determined offset.

30. 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, wherein the relational database has a fact table and at least one related dimension table, the method comprising the steps of:

receiving a dimension identifier that identifies a data block in a multi-dimensional database, wherein the dimension identifier comprises one or more multi-dimensional member identifiers wherein the multi-dimensional member identifiers identify an offset into the data block, and wherein the data block contains values;

accessing rows from the fact table through the related dimension tables using the received dimension identifier;

formatting the accessed rows as a data block for the multi-dimensional database;

determining an offset into the data block using the multi-dimensional member identifiers;

accessing the values contained in the data block at the determined offset; and

storing the accessed values in rows of the relational database.