Sampling in a multidimensional database

• US 7,324,991 B1
• Filed: 08/30/2002
• Issued: 01/29/2008
• Est. Priority Date: 08/30/2002
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Hyperion Solutions Corporation (Oracle Corporation)
• Inventors: Anjur, Vijayakumar
• Primary Examiner(s): Rones; Charles
• Assistant Examiner(s): Betit; Jacob F.
• Application Number:
  US10/233,053
• Time in Patent Office: 1,978 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries
• 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
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

Sampling may be supported in a multidimensional database by integrating it into metadata and/or data navigation requests. Additionally, biasing may be introduced to allow a user to focus results.

Claims

1. A method for sampling data in a multidimensional database, the multidimensional database having metadata and data, the method comprising:

retrieving metadata corresponding to a first set of members identified by a user;

retrieving metadata corresponding to a second set of members identified by a user, wherein the metadata corresponding to the first set of members and the metadata corresponding to the second set of members comprises a set of dimensions, dimension members, and one or more formulas attached to said members;

determining the cross product of said metadata corresponding to a first set of members and said metadata corresponding to a second set of members, to arrive at an intersection of members;

randomly selecting a subset of said intersection of members according to a percentage indicated by a user; and

retrieving data corresponding to said subset of said intersection of members.

2. The method of claim 1, wherein the multidimensional database is implemented in a relational portion using a set of dimension tables that models dimensional hierarchies and a fact table that captures cell values.

3. The method of claim 1, wherein the data is cell values.

4. The method of claim 1, wherein said first and second set of members identified by a user are identified by selecting elements in a spreadsheet corresponding to the metadata.

5. The method of claim 4, wherein said percentage is indicated by selecting an option in said spreadsheet.

6. A method for sampling data in a multidimensional database, the multidimensional database having metadata and data, the method comprising:

retrieving metadata corresponding to a first set of members identified by a user;

retrieving metadata corresponding to a second set of members identified by a user wherein the metadata corresponding to the first set of members and the metadata corresponding to the second set of members comprises a set of dimensions, dimension members, and one or more formulas attached to said members;

determining the cross product of said metadata corresponding to a first set of members and said metadata corresponding to a second set of members, to arrive at an intersection of members;

retrieving data corresponding to said intersection of members; and

randomly selecting a subset of said retrieved data according to a percentage indicated by a user.

7. The method of claim 6, wherein the multidimensional database is implemented in a relational portion using a set of dimension tables that models dimensional hierarchies and a fact table that captures cell values.

8. The method of claim 6, wherein the data is cell values.

9. The method of claim 6, wherein said first and second set of members identified by a user are identified by selecting elements in a spreadsheet corresponding to the metadata.

10. The method of claim 9, wherein said percentage is indicated by selecting an option in said spreadsheet.

11. The method of claim 6, wherein said randomly selecting a subset of said retrieved data according to a percentage indicated by a user comprises randomly selecting a subset of said retrieved data according to a percentage indicated by a user by sampling only existing cells.

12. A method for sampling data in a multidimensional database, the multidimensional database having metadata and data, the method comprising:

retrieving metadata corresponding to a subset of a first set of members identified by a user, said subset of a first set of members randomly selected according to a first bias percentage indicated by a user;

retrieving metadata corresponding to a subset of a second set of members identified by a user, said subset of a second set of members randomly selected according to a second bias percentage indicated by a user wherein the metadata corresponding to the subset of the first set of members and the metadata corresponding to the subset of the second set of members comprises a set of dimensions, dimension members, and one or more formulas attached to said members;

determining the cross product of said metadata corresponding to the subset of a first set of members and said metadata corresponding to the subset of a second set of members, to arrive at an intersection of members; and

retrieving data corresponding to said intersection of members.

13. The method of claim 12, wherein said subset of a first set of members are randomly selected in conjunction with said subset of a second set of members such that said cross product results in a number of members in said intersection of members equal to said sample size percentage of the cross product of metadata corresponding to said first set of members and metadata corresponding to said second set of members.

14. The method of claim 12, wherein the multidimensional database is implemented in a relational portion using a set of dimension tables that models dimensional hierarchies and a fact table that captures cell values.

15. The method of claim 12, wherein the data is cell values.

16. The method of claim 12, wherein said first and second set of members identified by a user are identified by selecting elements in a spreadsheet corresponding to the metadata.

17. The method of claim 16, wherein said percentage is indicated by selecting an option in said spreadsheet.

18. The method of claim 12, wherein said retrieving metadata corresponding to a subset of a first set of members further comprises retrieving metadata corresponding to a subset of a first set of members identified by a user, said subset of a first set of members randomly selected according to a first bias percentage and a sample size percentage indicated by a user.

19. The method of claim 18, wherein said retrieving metadata corresponding to a subset of a second set of members further comprises retrieving metadata corresponding to a subset of a second set of members identified by a user, said subset of a second set of members randomly selected according to a second bias percentage indicated by a user and said sample size percentage.

20. A program storage device readable by a machine, tangibly embodying a program of instructions executable by the machine to perform a method for sampling data in a multidimensional database, the multidimensional database having metadata and data, the method comprising:

retrieving metadata corresponding to a first set of members identified by a user;

retrieving metadata corresponding to a second set of members identified by a user, wherein the metadata corresponding to the first set of members and the metadata corresponding to the second set of members comprises a set of dimensions, dimension members, and one or more formulas attached to said members;

determining the cross product of said metadata corresponding to a first set of members and said metadata corresponding to a second set of members, to arrive at an intersection of members;

randomly selecting a subset of said intersection of members according to a percentage indicated by a user; and

retrieving data corresponding to said subset of said intersection of members.

21. A program storage device readable by a machine, tangibly embodying a program of instructions executable by the machine to perform a method for sampling data in a multidimensional database, the multidimensional database having metadata and data, the method comprising:

retrieving metadata corresponding to a first set of members identified by a user;

retrieving metadata corresponding to a second set of members identified by a user, wherein the metadata corresponding to the first set of members and the metadata corresponding to the second set of members comprises a set of dimensions, dimension members, and one or more formulas attached to said members;

determining the cross product of said metadata corresponding to a first set of members and said metadata corresponding to a second set of members, to arrive at an intersection of members;

retrieving data corresponding to said intersection of members; and

randomly selecting a subset of said retrieved data according to a percentage indicated by a user.

22. A program storage device readable by a machine, tangibly embodying a program of instructions executable by the machine to perform a method for sampling data in a multidimensional database, the multidimensional database having metadata and data, the method comprising:

retrieving metadata corresponding to a subset of a first set of members identified by a user, said subset of a first set of members randomly selected according to a first bias percentage indicated by a user;

retrieving metadata corresponding to a subset of a second set of members identified by a user, said subset of a second set of members randomly selected according to a second bias percentage indicated by a user, wherein the metadata corresponding to the subset of the first set of members and the metadata corresponding to the subset of the second set of members comprises a set of dimensions, dimension members, and one or more formulas attached to said members;

determining the cross product of said metadata corresponding to the subset of a first set of members and said metadata corresponding to the subset of a second set of members, to arrive at an intersection of members; and

retrieving data corresponding to said intersection of members.