View selection for a multidimensional database

• US 7,467,127 B1
• Filed: 06/07/2004
• Issued: 12/16/2008
• Est. Priority Date: 02/27/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Hyperion Solutions Corporation (Oracle Corporation)
• Inventors: Baccash, Jonathan M., Shatdal, Ambuj, Nazarenko, Igor, Rodny, Uri
• Primary Examiner(s): Lee; Wilson
• Assistant Examiner(s): Gofman; Alex
• Application Number:
  US10/863,870
• Time in Patent Office: 1,653 Days
• Field of Search: 707/2
• 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/99932 Access augmentation or optimizing

Abstract

Selection of certain views of a multidimensional database to materialize may be accomplished using an efficient and effective solution. A set of all potential views may be stored in a structure. A path in the structure may then be traversed in an indicated direction through the structure, the path including two or more potential views and beginning at an indicated view. Then two or more immaterialized views in the path may be compared to each other to determine which immaterialized view is the optimum choice for maximizing benefit if selected to be materialized. Then the traversing and comparing may be continually iterated through, each iteration utilizing an indicated direction different than the last, each iteration also utilizing an indicated view set at the optimum choice determined by the last iteration, the iterating continuing until it converges on a single view. That single view may then be selected for materialization.

Claims

1. A method for selecting a view to be materialized for a multidimensional database, the method comprising:

(a) selecting a first node of a plurality of nodes of a multidimensional structure, each node of the plurality of nodes representing a potential view of the multidimensional database;

(b) performing, in multiple iterations, the steps of;

(1) based on a particular node, determining a particular path of nodes in a particular direction through the multidimensional structure, the particular path of nodes comprising the particular node;

(2) determining a materialization benefit for each of two or more non-materialized views represented by nodes in the particular path of nodes;

(3) selecting a new node in the particular path of nodes, the new node representing a non-materialized view of the two or more non-materialized views having a higher materialization benefit than any other of the two or more non-materialized views;

wherein, for a first iteration of steps (1)-(3), the particular node is a first node, the particular direction is a first direction, and the particular path of nodes is a first particular path of nodes;

wherein, for each iteration of one or more iterations of steps (1)-(3), each iteration being subsequent to the first iteration, the new node of an immediately preceding iteration becomes the particular node, a new direction becomes the particular direction, and a new path of nodes becomes the particular path of nodes;

wherein the new path of nodes is different from the particular path of nodes of the immediately preceding iteration, and the new direction is different from the particular direction of the immediately preceding iteration;

(c) determining that, over multiple iterations of steps (1)-(3), selection of the new node in step (3) has converged upon a single node; and

,

(d) in response to step (c), materializing a particular view represented by the single node.

2. The method of claim 1, wherein said particular direction is horizontal or vertical.

3. The method of claim 2, wherein said particular direction alternates between horizontal and vertical on each of the multiple iteration of steps (1) through (3).

4. The method of claim 1, wherein said particular direction is along an axis of the multidimensional structure.

5. The method of claim 4, wherein said particular direction changes to a different axis of the multidimensional structure on each of the multiple iteration of steps (1) through (3).

6. The method of claim 1, further comprising the step of executing steps (a) through (d) two or more times to generate a set of materialized views.

7. The method of claim 6, wherein the first node changes on each execution of steps (a) through (d).

8. The method of claim 6, wherein the step of executing steps (a) through (d) two or more times comprises executing steps (a) through (d) until the set of materialized views is a predetermined size.

9. The method of claim 1, wherein step (a) comprises selecting the first node randomly.

10. The method of claim 1, wherein the selection of the new node in step (3) is said to converge upon the single node when a same new node is selected in step (3) over consecutive iterations of steps (1) through (3).

11. The method of claim 1, wherein said two or more non-materialized views include every non-materialized view represented by the nodes in said particular path of nodes.

12. The method of claim 1, wherein said two or more non-materialized views represent a random sampling of non-materialized views represented by the nodes in said particular path of nodes.

13. The method of claim 1, wherein steps (a) through (d) are repeated for each view selection in the multidimensional database.

14. The method of claim 13, wherein on each iteration of step (a), a different first node is selected.

15. The method of claim 14, wherein said different first nodes include a node representing a root view and a node representing an end view.

16. The method of claim 1, further comprising the steps of:

concurrently executing multiple iterations of steps (a) through (d), including a first iteration of steps (a) through (d) and a second iteration of steps (a) through (d);

wherein the first iteration of steps (a) through (c) is executed with respect to a first particular node, wherein, for the first iteration of steps (a) through (c), the first particular node is the first node and selection of the new node in step (3) converges upon a first single node;

wherein the second iteration of steps (a) through (c) is executed with respect to a second particular node, wherein, for the second iteration of steps (a) through (c), the second particular node is the first node and selection of the new node in step (3) converges upon a second single node; and

executing step (d), wherein the single node is either the first single node or the second single node, depending on which of the views represented by the first single node and the second single node has a higher materialization benefit.

17. The method of claim 16, wherein the first particular node is a root node and the second particular node is an end node.

18. A machine-readable storage medium for selecting a view to be materialized for a multidimensional database, tangibly embodying a set of instructions that, when executed by the machine, causes the machine to perform the steps of:

(a) selecting a first node of a plurality of nodes of a multidimensional structure, each node of the plurality of nodes representing a potential view of the multidimensional database;

(b) performing, in multiple iterations, the steps of;

(1) based on a particular node, determining a particular path of nodes in a particular direction through the multidimensional structure, the particular path of nodes comprising the particular node;

(2) determining a materialization benefit for each of two or more non-materialized views represented by nodes in the particular path of nodes;

(3) selecting a new node in the particular path of nodes, the new node representing a non-materialized view of the two or more non-materialized views having a higher materialization benefit than any other of the two or more non-materialized views;

wherein, for a first iteration of steps (1)-(3), the particular node is a first node, the particular direction is a first direction, and the particular path of nodes is a first particular path of nodes;

wherein, for each iteration of one or more iterations of steps (1)-(3), each iteration being subsequent to the first iteration, the new node of an immediately preceding iteration becomes the particular node, a new direction becomes the particular direction, and a new path of nodes as becomes the particular path of nodes;

wherein the new path of nodes is different from the particular path of nodes of the immediately preceding iteration, and the new direction is different from the particular direction of the immediately preceding iteration;

(c) determining that, over multiple iterations of steps (1)-(3), selection of the new node in step (3) has converged upon a single node; and

,

(d) in response to step (c), materializing a particular view represented by the single node.

19. The machine-readable storage medium of claim 18, wherein said particular direction is horizontal or vertical.

20. The machine-readable storage medium of claim 19, wherein said particular direction alternates between horizontal and vertical on each of the multiple iteration of steps (1) through (3).

21. The machine-readable storage medium of claim 18, wherein said particular direction is along an axis of the multidimensional structure.

22. The machine-readable storage medium of claim 21, wherein said particular direction changes to a different axis of the multidimensional structure on each of the multiple iteration of steps (1) through (3).

23. The machine-readable storage medium of claim 18, wherein the set of instructions, when executed by the machine, further causes the machine to perform the step of executing steps (a) through (d) two or more times to generate a set of materialized views.

24. The machine-readable storage medium of claim 23, wherein the step of executing steps (a) through (d) two or more times comprises executing steps (a) through (d) until the set of materialized views is a predetermined size.

25. The machine-readable storage medium of claim 23, wherein the first node changes on each execution of steps (a) through (d).

26. The machine-readable storage medium of claim 18, wherein step (a) comprises selecting the first node randomly.

27. The machine-readable storage medium of claim 18, wherein the selection of the new node in step (3) is said to converge upon the single node when a same new node is selected in step (3) over consecutive iterations of steps (1) through (3).

28. The machine-readable storage medium of claim 18, wherein said two or more non-materialized views include every non-materialized view represented by the nodes in said particular path of nodes.

29. The machine-readable storage medium of claim 18, wherein said two or more non-materialized views represent a random sampling of non-materialized views represented by the nodes in said particular path of nodes.

30. The machine-readable storage medium of claim 18, wherein steps (a) through (d) are repeated for each view selection in the multidimensional database.

31. The machine-readable storage medium of claim 30, wherein on each iteration of step (a), a different first node is selected.

32. The machine-readable storage medium of claim 31, wherein said different first nodes include a node representing a root view and a node representing an end view.

33. The machine-readable storage medium of claim 18, wherein the set of instructions, when executed by the machine, further causes the machine to perform the steps of:

concurrently executing multiple iterations of steps (a) through (d), including a first iteration of steps (a) through (d) and a second iteration of steps (a) through (d);

wherein the first iteration of steps (a) through (c) is executed with respect to a first particular node, wherein, for the first iteration of steps (a) through (c), the first particular node is the first node and selection of the new node in step (3) converges upon a first single node;

wherein the second iteration of steps (a) through (c) is executed with respect to a second particular node, wherein, for the second iteration of steps (a) through (c), the second particular node is the first node and selection of the new node in step (3) converges upon a second single node; and

executing step (d), wherein the single node is either the first single node or the second single node, depending on which of the views represented by the first single node and the second single node has a higher materialization benefit.

34. The machine-readable storage medium of claim 33, wherein the first particular node is a root node and the second particular node is an end node.

35. An apparatus for selecting a view to be materialized for a multidimensional database, the apparatus including one or more processors coupled to a memory being configured to perform each of the following steps:

(a) selecting a first node of a plurality of nodes of a multidimensional structure, each node of the plurality of nodes representing a potential view of the multidimensional database;

(b) performing, in multiple iterations, the steps of;

(1) based on a particular node, determining a particular path of nodes in a particular direction through the multidimensional structure, the particular path of nodes comprising the particular node;

(2) determining a materialization benefit for each of two or more non-materialized views represented by nodes in the particular path of nodes;

(3) selecting a new node in the particular path of nodes, the new node representing a non-materialized view of the two or more non-materialized views having a higher materialization benefit than any other of the two or more non-materialized views;

wherein, for a first iteration of steps (1)-(3), the particular node is a first node, the particular direction is a first direction, and the particular path of nodes is a first particular path of nodes;

wherein, for each iteration of one or more iterations of steps (1)-(3), each iteration being subsequent to the first iteration, the new node of an immediately preceding iteration becomes the particular node, a new direction becomes the particular direction, and a new path of nodes as becomes the particular path of nodes;

wherein the new path of nodes is different from the particular path of nodes of the immediately preceding iteration, and the new direction is different from the particular direction of the immediately preceding iteration;

(c) determining that, over multiple iterations of steps (1)-(3), selection of the new node in step (3) has converged upon a single node; and

,

(d) in response to step (c), materializing a particular view represented by the single node.

36. The apparatus of claim 35, wherein said particular direction is horizontal or vertical.

37. The apparatus of claim 36, wherein said particular direction alternates between horizontal and vertical on each of the multiple iteration of steps (1) through (3).

38. The apparatus of claim 35, wherein said particular direction is along an axis of the multidimensional structure.

39. The apparatus of claim 38, wherein said particular direction changes to a different axis of the multidimensional structure on each of the multiple iteration of steps (1) through (3).

40. The apparatus of claim 35, further comprising steps of executing steps (a) through (d) two or more times to generate a set of materialized views.

41. The apparatus of claim 40, wherein the step of executing steps (a) through (d) two or more times comprises executing steps (a) through (d) until the set of materialized views is a predetermined size.

42. The apparatus of claim 40, wherein the first node changes on each execution of steps (a) through (d).

43. The apparatus of claim 35, wherein step (a) comprises selecting the first node randomly.

44. The apparatus of claim 35, wherein the selection of the new node in step (3) is said to converge upon the single node when a same new node is selected in step (3) over consecutive iterations of steps (1) through (3).

45. The apparatus of claim 35, wherein said two or more non-materialized views include every non-materialized view represented by the nodes in said particular path of nodes.

46. The apparatus of claim 35, wherein said two or more non-materialized views represent a random sampling of non-materialized views represented by the nodes in said particular path of nodes.

47. The apparatus of claim 35, wherein steps (a) through (d) are repeated for each view selection in the multidimensional database.

48. The apparatus of claim 47, wherein on each iteration of step (a), a different first node is selected.

49. The apparatus of claim 48, wherein said different first nodes include a node representing a root view and a node representing an end view.

50. The apparatus of claim 35, further comprising:

concurrently executing multiple iterations of steps (a) through (d), including a first iteration of steps (a) through (d) and a second iteration of steps (a) through (d);

wherein the first iteration of steps (a) through (c) is executed with respect to a first particular node, wherein, for the first iteration of steps (a) through (c), the first particular node is the first node and selection of the new node in step (3) converges upon a first single node;

wherein the second iteration of steps (a) through (c) is executed with respect to a second particular node, wherein, for the second iteration of steps (a) through (c), the second particular node is the first node and selection of the new node in step (3) converges upon a second single node; and

executing step (d), wherein the single node is either the first single node or the second single node, depending on which of the views represented by the first single node and the second single node has a higher materialization benefit.

51. The apparatus of claim 50, wherein the first particular node is a root node and the second particular node is an end node.