Multi-dimensional restructure performance by selecting a technique to modify a relational database based on a type of restructure

• US 6,546,395 B1
• Filed: 08/30/1999
• Issued: 04/08/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: Tomlyn, Craig Reginald, DeKimpe, Daniel Martin, Pham, Khiem Phu, Malloy, William Earl
• Primary Examiner(s): Breene, John
• Assistant Examiner(s): LEWIS, CHERYL RENEA
• Application Number:
  US09/385,317
• Time in Patent Office: 1,317 Days
• Field of Search: 707/1, 707/2, 707/3, 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 improving multi-dimensional restructure performance by selecting a technique to modify a relational database based on a type of restructure. 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. Modifications to one or more original tables in a relational database corresponding to the multi-dimensional database are identified. A technique for modifying the relational database to correspond to the multidimensional database is selected based on the identified modifications.

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 corresponding to an associated multi-dimensional database, comprising:

determining that said multi-dimensional database has been modified;

identifying modifications to one or more original tables in the relational database corresponding to the multi-dimensional database; and

selecting a technique for modifying the relational database to correspond to the multi-dimensional database based on the identified modifications.

2. The method of claim 1, wherein one or more non-anchor dimensions or non-anchor dimension members have been deleted from the multi-dimensional database and wherein identifying modifications further comprises identifying that one or more records are to be deleted from the relational database.

3. The method of claim 2, further comprising estimating a number of records to be retained in the relational database.

4. The method of claim 3, further comprising estimating a dimension estimate that indicates an inverse of the product of a number of members in each dimension.

5. The method of claim 4, further comprising estimating a dimension member estimate that indicates a percent of members to be retained for each dimension with deleted dimension members.

6. The method of claim 5, wherein estimating further comprises estimating a total number of records to be retained in a table by multiplying the dimension estimate with the dimension member estimate.

7. The method of claim 6, wherein selecting further comprises selecting a technique based on the estimated total number of records to be retained in a table.

8. The method of claim 1, further comprising determining whether the estimated number of records is below a predetermined threshold.

9. The method of claim 8, wherein it is determined that the estimated number of records is below the predetermined threshold and wherein selecting a technique further comprises selecting a copy technique.

10. The method of claim 1, wherein one or more non-anchor dimensions or anchor dimension members have been added to the multi-dimensional database and wherein identifying modifications further comprises identifying that one or more columns are to be added to the relational database.

11. The method of claim 10, wherein when a sum of all non-anchor dimensions and anchor dimension members in the multi-dimensional database is less than or equal to a relational database column limit, using the copy technique.

12. The method of claim 10, wherein when a sum of all non-anchor dimensions and anchor dimension members in the multi-dimensional database is greater than a relational database column limit, returning an indication that modification of the multi-dimensional database has failed.

13. The method of claim 1, further comprising determining an unused columns fraction.

14. The method of claim 13, wherein when the unused columns fraction is large, using the copy technique.

15. The method of claim 13, wherein when the unused columns fraction is small, using the delete technique.

16. The method of claim 1 wherein:

said determining step includes determining that said multi-dimensional database has been restructured; and

,

said selecting step includes selecting a technique for modifying the relational database to correspond to the multi-dimensional database based on a type of restructure to said multi-dimensional database.

17. 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, identifying modifications to one or more original tables in a relational database corresponding to the multi-dimensional database, and selecting a technique for modifying the relational database to correspond to the multi-dimensional database based on the identified modifications.

18. The apparatus of claim 17, wherein one or more non-anchor dimensions or non-anchor dimension members have been deleted from the multi-dimensional database and wherein identifying modifications further comprises identifying that one or more records are to be deleted from the relational database.

19. The apparatus of claim 18, further comprising estimating a number of records to be retained in the relational database.

20. The apparatus of claim 19, further comprising estimating a dimension estimate that indicates an inverse of the product of a number of members in each dimension.

21. The apparatus of claim 20, further comprising estimating a dimension member estimate that indicates a percent of members to be retained for each dimension with deleted dimension members.

22. The apparatus of claim 21, wherein estimating further comprises estimating a total number of records to be retained in a table by multiplying the dimension estimate with the dimension member estimate.

23. The apparatus of claim 22, wherein selecting further comprises selecting a technique based on the estimated total number of record to be retained in a table.

24. The apparatus of claim 17, further comprising determining whether the estimated number of records is below a predetermined threshold.

25. The apparatus of claim 24, wherein it is determined that the estimated number of records is below the predetermined threshold and wherein selecting a technique further comprises selecting a copy technique.

26. The apparatus of claim 17, wherein one or more non-anchor dimensions or anchor dimension members have been added to the multi-dimensional database and wherein identifying modifications further comprises identifying that one or more columns are to be added to the relational database.

27. The apparatus of claim 26, wherein when a sum of all non-anchor dimensions and anchor dimension members in the multi-dimensional database is less than or equal to a relational database column limit, using the copy technique.

28. The apparatus of claim 26, wherein when a sum of all non-anchor dimensions and anchor dimension members in the multi-dimensional database is greater than a relational database column limit, returning an indication that modification of the multi-dimensional database has failed.

29. The apparatus of claim 17, further comprising determining an unused columns fraction.

30. The apparatus of claim 29, wherein when the unused columns fraction is large, using the copy technique.

31. The apparatus of claim 29, wherein when the unused columns fractions is small, using the delete technique.

32. The apparatus of claim 17 wherein said one or more computer programs, performed by said computer, are adapted to:

determine that said multi-dimensional database has been restructured; and

,

select a technique for modifying the relational database to correspond to the multi-dimensional database based on a type of restructure to said multi-dimensional database.

34. The article of manufacture of claim 32, wherein one or more non-anchor dimensions or non-anchor dimension members have been deleted from the multi-dimensional database and wherein identifying modifications further comprises identifying that one or more records are to be deleted from the relational database.

35. The article of manufacture of claim 34, further comprising estimating a number of records to be retained in the relational database.

36. The article of manufacture of claim 35, further comprising estimating a dimension estimate that indicates an inverse of the product of a number of members in each dimension.

37. The article of manufacture of claim 36, further comprising estimating a dimension member estimate that indicates a percent of members to be retained for each dimension with deleted dimension members.

38. The article of manufacture of claim 37, wherein estimating further comprises estimating a total number of records to be retained in a table by multiplying the dimension estimate with the dimension member estimate.

39. The article of manufacture of claim 38, wherein selecting further comprises selecting a technique based on the estimated total number of records to be retained in a table.

40. The article of manufacture of claim 32, further comprising determining whether the estimated number of records is below a predetermined threshold.

41. The article of manufacture of claim 40, wherein it is determined that the estimated number of records is below the predetermined threshold and wherein selecting a technique further comprises selecting a copy technique.

42. The article of manufacture of claim 32, wherein one or more non-anchor dimensions or anchor dimension members have been added to the multi-dimensional database and wherein identifying modifications further comprises identifying that one or more columns are to be added to the relational database.

43. The article of manufacture of claim 42, wherein when a sum of all non-anchor dimensions and anchor dimension members in the multi-dimensional database is less than or equal to a relational database column limit, using the copy technique.

44. The article of manufacture of claim 42, wherein when a sum of all non-anchor dimensions and anchor dimension members in the multi-dimensional database is greater than a relational database column limit, returning an indication that modification of the multi-dimensional database has failed.

45. The article of manufacture of claim 32, further comprising determining an unused columns fraction.

46. The article of manufacture of claim 45, wherein when the unused columns fraction is large, using the copy technique.

47. The article of manufacture of claim 45, wherein when the unused columns fraction is small, using the delete technique.

48. The article of manufacture of claim 32 further comprising:

determining that said multi-dimensional database has been restructured; and

,

selecting a technique for modifying the relational database to correspond to the multi-dimensional database based on a type of restructure to said multi-dimensional database.

33. 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 store connected to the computer, comprising:

determining that a multi-dimensional database has been modified;

identifying modifications to one or more original tables in a relational database corresponding to the multi-dimensional database; and

selecting a technique for modifying the relational database to correspond to the multi-dimensional database based on the identified modifications.