Multi-dimensional restructure performance when adding or removing dimensions and dimensions members

• US 6,542,895 B1
• Filed: 08/30/1999
• Issued: 04/01/2003
• Est. Priority Date: 08/30/1999
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Malloy, William Earl, Tomlyn, Craig Reginald, DeKimpe, Daniel Martin, Pham, Khiem Phu
• Primary Examiner(s): Breene, John
• Assistant Examiner(s): LEWIS, CHERYL RENEA
• Application Number:
  US09/386,072
• Time in Patent Office: 1,310 Days
• Field of Search: 707/1, 707/2, 707/3, 707/100, 707/101
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A method, apparatus, and article of manufacture for improving multi-dimensional restructure performance when adding or removing dimensions and dimension members. A command is executed in a computer to perform a database operation on a relational database stored on a data store connected to the computer. It is determined that a multi-dimensional database has been modified. Then, it is determined that the modified multi-dimensional database requires modifications to one or more original tables in a relational database corresponding to the multi-dimensional database. The modifications are incorporated into one or more new tables by copying data from the original tables into the new tables.

Claims

1. A method of executing a command in a computer to perform a database operation on a relational database stored on a data store connected to the computer and associated with a multi-dimensional database, comprising:

determining that said multi-dimensional database has been modified;

determining that the modified multi-dimensional database requires modifications to one or more original tables in the relational database corresponding to the multi-dimensional database; and

incorporating the modifications into one or more new tables by selectively copying data from the one or more original tables into the one or more new tables.

2. The method of claim 1, wherein determining that a multi-dimensional database has been modified comprises determining that one or more non-anchor dimensions or non-anchor dimension members have been deleted.

3. The method of claim 1, wherein determining that a multi-dimensional database has been modified comprises determining that one or more non-anchor dimensions or anchor dimension members have been added.

4. The method of claim 1, wherein copying further comprises creating the one or more new tables using all of the columns to be retained from the original table and any new columns that are to be added for new dimension and anchor dimension members.

5. The method of claim 1, wherein copying further comprises eliminating logging on an initial transaction.

6. The method of claim 1, wherein copying further comprises inserting data into the new table from the original table using an insert with subselect statement.

7. The method of claim 6, wherein the subselect statement comprises a where clause.

8. The method of claim 1, wherein copying further comprises initializing a column newly added or being reused to the original table with a NULL value in the new table.

9. The method of claim 1, further comprising deleting unnecessary data from the new table.

10. The method of claim 1, further comprising dropping the original table.

11. The method of claim 1, further comprising renaming the new table to the original table name.

12. The method of claim 1, wherein there are multiple original tables and multiple new tables, further comprising concurrently copying date from each of the original tables into each of the new tables with separate threads.

13. The method of claim 12, wherein each thread maintains a separate database connection.

14. The method of claim 12, further comprising identifying which data goes into which table using a hashing function based on a set of dimension identifiers that identify dimensions in the multi-dimensional database.

15. The method of claim 14, wherein copying further comprises moving data from the original tables into the new tables using an insert statement with a subselect clause that specifies the hashing function.

16. An apparatus for executing a command in a computer, comprising:

a computer having a data store coupled thereto, wherein the data store stores a relational database;

one or more computer programs, performed by the computer, for determining that a multi-dimensional database has been modified, determining that the modified multi-dimensional database requires modifications to one or more original tables in a relational database corresponding to the multi-dimensional database, and incorporating the modifications into one or more new tables by copying data from the one or more original tables into the one or more new tables.

17. The apparatus of claim 16, wherein determining that a multi-dimensional database has been modified comprises determining that one or more non-anchor dimensions or non-anchor dimension members have been deleted.

18. The apparatus of claim 16, wherein determining that a multi-dimensional database has been modified comprises determining that one or more non-anchor dimensions or anchor dimension members have been added.

19. The apparatus of claim 16, wherein copying further comprises creating the one or more new tables using all of the columns to be retained from the original table and any new columns that are to be added for new dimensions and anchor dimension members.

20. The apparatus of claim 16, wherein copying further comprises eliminating logging on an initial transaction.

21. The apparatus of claim 16, wherein copying further comprises inserting data into the new table from the original table using an insert with subselect statement.

22. The apparatus of claim 21, wherein the subselect statement comprises a where clause.

23. The apparatus of claim 16, wherein copying further comprises initializing a column newly added or being reused to the original table with an appropriate identifier in the new table.

24. The apparatus of claim 16, further comprising deleting unnecessary data from the new table.

25. The apparatus of claim 16, further comprising dropping the original table.

26. The apparatus of claim 16, further comprising renaming the new table to the original table name.

27. The apparatus of claim 16, wherein there are multiple original tables and multiple new cables, further comprising concurrently copying data from each of the original tables into each of the new tables with separate threads.

28. The apparatus of claim 27, wherein each thread maintains a separate database connection.

29. The apparatus of claim 27, further comprising identifying which data goes into which table using a hashing function based on a set of dimension identifiers that identify dimensions in the multi-dimensional database.

30. The apparatus of claim 29, wherein copying further comprises moving data from the original tables into the new tables using an insert statement with a subselect clause that specifies the hashing function.

31. An article of manufacture including 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 store connected to the computer, comprising:

determining that a multi-dimensional database has been modified;

determining that the modified multi-dimensional database requires modifications to one or more original tables in a relational database corresponding to the multi-dimensional database; and

incorporating the modifications into one or more new tables by copying data from the one or more original tables into the one or more new tables.

32. The article of manufacture of claim 31, wherein determining that a multi-dimensional database has been modified comprises determining that one or more non-anchor dimensions or non-anchor dimension members have been deleted.

33. The article of manufacture of claim 31, wherein determining that a multi-dimensional databases has been modified comprises determining that one or more non-anchor dimensions or anchor dimension members have been added.

34. The article of manufacture of claim 31, wherein copying further comprises creating the one or more new tables using all of the columns to be retained from the original table and any new columns that are to be added for new dimensions and anchor dimension members.

35. The article of manufacture of claim 31, wherein copying further comprises eliminating logging on an initial transaction.

36. The article of manufacture of claim 31, wherein copying further comprises inserting data into the new table from the original table using an insert with subselect statement.

37. The article of manufacture of claim 36, wherein subselect statement comprises a where clause.

38. The article of manufacture of claim 31, wherein copying further comprises initializing a column newly added or being reused to the original table with an appropriate identifier in the new table.

39. The article of manufacture of claim 31, further comprising deleting unnecessary data from the new table.

40. The article of manufacture of claim 31, further comprising dropping the original table.

41. The article of manufacture of claim 31, further comprising renaming the new table to the original table name.

42. The article of manufacture of claim 31, wherein there are multiple original tables and multiple new tables, further comprising concurrently copying data from each of the original tables into each of the new tables with separate threads.

43. The article of manufacture of claim 42, wherein each thread maintains a separate database connection.

44. The article of manufacture of claim 42, further comprising identifying which data goes into which table using a hashing function based on a set of dimension identifiers that identify dimensions in the multi-dimensional database.

45. The article of manufacture of claim 44, wherein copying further comprises moving data from the original tables into the new tables using an insert statement with a subselect clause that specifies the hashing function.