Efficient SQL access to multidimensional data

• US 8,200,612 B2
• Filed: 05/06/2004
• Issued: 06/12/2012
• Est. Priority Date: 05/07/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Soylemez, Ekrem, Welton, Caleb, Dorman, Gregory, Dombroski, Matthew, Hopeman, Albert A. IV, Lubashev, Igor, Margulis, Lev, Kearney, Christopher, Carey, James
• Primary Examiner(s): PULLIAM, CHRISTYANN R
• Application Number:
  US10/841,941
• Publication Number:
  US 20040236767A1
• Time in Patent Office: 2,959 Days
• Field of Search: 707/3, 707/104.1
• US Class Current: 707/600
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

In processing a query on multidimensional data in a multidimensional schema, a multidimensional database server performs various processes to limit the amount of data that is extracted from the database and presented to a relational database server in response to the request. A subset of data is identified to extract from the multidimensional data, based on the query. In various embodiments, cell-filtering, measure-filtering, and column-filtering criteria identified from the query are used to filter the subset of data, in order to return a minimized set of multidimensional data values from the subset of data. Therefore, computational resources are conserved with respect to further processing by the relational database server in response to the query.

Claims

1. A method comprising the computer-implemented steps of:

receiving, at a relational database server, a relational database statement that specifies one or more relational operations to perform on a data subset of one or more n-dimensional data objects that are stored according to a multidimensional schema;

passing, from said relational database server to a multidimensional database server, a) a function that identifies the data subset based on information that is contained in said relational database statement and, b) separate from the function, one or more value-filtering criteria involved in the one or more relational operations;

identifying, by the multidimensional database server and based on said function, the data subset of said n-dimensional data objects;

generating, by said multidimensional database server, a filtered set of one or more values from the data subset based on the one or more value-filtering criteria;

passing, from said multidimensional database server to said relational database server, said filtered set of one or more values;

performing, at the relational database server, the one or more specified relational operations on the filtered set of one or more values returned to the relational database server; and

producing and returning, by said relational database server and in response to said relational database statement, results of the one or more relational operations;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, further comprising the computer-implemented steps of:

extracting, by said multidimensional database server, values within said data subset; and

the multidimensional database server passing said filtered set of one or more values from said data subset to the relational database server in a particular relational form.

3. The method of claim 2, further comprising the computer-implemented steps of:

determining, by said multidimensional database server, said particular relational form.

4. The method of claim 2, further comprising the computer-implemented steps of:

determining, by said multidimensional database server, said particular relational form based on information contained in said relational database statement.

5. The method of claim 2, wherein the step of passing the filtered set of one or more values from the data subset comprises passing said filtered set of one or more values in a form of a relational database table.

6. The method of claim 2, wherein said relational database statement is a SQL statement, and wherein the step of passing the filtered set of one or more values from the data subset comprises passing said filtered set of one or more values in said particular relational form to a SQL processor of said relational database server.

7. The method of claim 2, further comprising the computer-implemented step of:

dynamically generating one or more abstract data type definitions that define formats of at least a portion of said values from said subset.

8. The method of claim 7, further comprising the computer-implemented step of:

the multidimensional database server communicating the dynamically generated abstract data type definitions to the relational database server; and

the relational database server accessing data in the filtered set of one or more values based on the abstract data type definitions.

9. The method of claim 2, wherein the step of passing said filtered set of one or more values in a particular relational form comprises passing said filtered set of one or more values in a form of a relational database table in which a particular column is associated with one or more particular measure values, the method further comprising the computer-implemented step of:

the multidimensional database server dynamically creating an abstract data type definition that defines a format of said values in said column.

10. The method of claim 1, wherein the step of identifying said data subset comprises identifying all cells associated with a respective range of keys for each of a plurality of dimensions of said n-dimensional data objects.

11. The method of claim 1, wherein the data subset consists of non-contiguous cells, and wherein the step of identifying a data subset comprises identifying only cells associated with one or more user-specified key combinations that each comprises a particular key for each of a plurality of dimensions of said n-dimensional data objects.

12. The method of claim 1, further comprising the computer-implemented step of:

generating, by said multidimensional database server, the filtered set of one or more values by comparing values in cells of said data subset to the value-filtering criteria.

13. The method of claim 12, wherein the step of generating said filtered set of one or more values comprises:

looping through each cell associated with a respective range of keys for each of a plurality of dimensions of said n-dimensional data objects; and

comparing a measure value associated with each respective cell to a corresponding measure value specified in a condition in said relational database statement.

14. The method of claim 12, wherein the step of generating said filtered set of one or more values comprises:

identifying cells of said data subset that satisfy cell-filtering criteria specified in said relational database statement;

comparing values, to said value-filtering criteria, only from cells of said data subset that satisfy said cell-filtering criteria.

15. The method of claim 12, wherein the step of generating said filtered set of one or more values comprises:

identifying, based on said relational database statement, all measures that are required by the relational database server to execute said relational database statement; and

presenting, as at least a portion of said filtered set of one or more values, measure values for said measures that are required by the relational database server to execute said relational database statement.

16. The method of claim 15, wherein the step of presenting comprises presenting only measure values for said measures that are required by the relational database server to execute said relational database statement.

17. The method of claim 15, wherein the step of generating said filtered set of one or more values comprises:

comparing, to said value-filtering criteria, only values for said measures that are required by the relational database server to execute said relational database statement.

18. The method of claim 12, wherein the step of generating said filtered set of one or more values comprises:

identifying, based on said relational database statement, all dimensions from which associated values are required by the relational database server to execute said relational database statement; and

presenting, as at least a portion of said filtered set of one or more values, values associated with said dimensions from which associated values are required by the relational database server to execute said relational database statement.

19. The method of claim 18, wherein the step of presenting comprises presenting only values associated with said dimensions from which associated values are required by the relational database server to execute said relational database statement.

20. The method of claim 1, wherein the step of receiving the relational database statement comprises receiving a SQL statement.

21. The method of claim 1, wherein said function is a table function that was specified in the relational database statement.

22. The method of claim 1, wherein the one or more value-filtering criteria include one or more of:

a JOIN clause, a SELECT clause, or a WHERE clause.

23. A system comprising:

one or more processors;

memory communicatively coupled to said one or more processors;

wherein said memory stores instructions which, when executed by said one or more processors, cause performance of;

receiving, at a relational database server, a relational database statement that specifies one or more relational operations to perform on a data subset of one or more n-dimensional data objects that are stored according to a multidimensional schema;

passing, from said relational database server to a multidimensional database server, a) a function that identifies the data subset based on information that is contained in said relational database statement and, b) separate from the function, one or more value-filtering criteria involved in the one or more relational operations;

identifying, by the multidimensional database server and based on said function, the data subset of said n-dimensional data objects;

generating, by said multidimensional database server, a filtered set of one or more values from the data subset based on the one or more value-filtering criteria;

passing, from said multidimensional database server to said relational database server, said filtered set of one or more values;

performing, at the relational database server, the one or more specified relational operations on the filtered set of one or more values returned to the relational database server; and

producing and returning, by said relational database server and in response to said relational database statement, results of the one or more relational operations.

24. One or more non-transitory computer-readable media storing instructions which, when executed by one or more computing devices cause:

receiving, at a relational database server, a relational database statement that specifies one or more relational operations to perform on a data, from subset of one or more n-dimensional data objects, that is are stored according to a multidimensional schema;

passing, from said relational database server to a multidimensional database server, a) a function that identifies the data subset based on information that is contained in said relational database statement and, b) separate from the function, one or more value-filtering criteria involved in the one or more relational operations;

identifying, by the multidimensional database server and based on said function, the data subset of said n-dimensional data objects;

generating, by said multidimensional database server, a filtered set of one or more values from the data subset based on the one or more value-filtering criteria;

passing, from said multidimensional database server to said relational database server, said filtered set of one or more values;

performing, at the relational database server, the one or more specified relational operations on the filtered set of one or more values returned to the relational database server; and

producing and returning, by said relational database server and in response to said relational database statement, results of the one or more relational operations.

25. The one or more non-transitory computer readable media of claim 24, wherein the step of identifying said data subset comprises identifying all cells associated with a respective range of keys for each of a plurality of dimensions of said n-dimensional data objects.

26. The one or more non-transitory computer readable media of claim 24, wherein the data subset consists of non-contiguous cells, and wherein a data subset comprises identifying only cells associated with one or more user-specified key combinations that each comprises a particular key for each of a plurality of dimensions of said n-dimensional data objects.

27. The one or more non-transitory computer readable media of claim 24, wherein receiving the relational database statement comprises receiving a SQL statement.

28. The one or more non-transitory computer readable media of claim 24, wherein said function is a table function that was specified in the relational database statement.

29. The one or more non-transitory computer readable storage media of claim 24, wherein the one or more value-filtering criteria include one or more of:

a JOIN clause, a SELECT clause, or a WHERE clause.

30. The one or more non-transitory computer readable storage media of claim 24, wherein the instructions, when executed by the one or more computing devices, cause performance of:

extracting, by said multidimensional database server, values within said data subset; and

the multidimensional database server passing said filtered set of one or more values from said data subset to the relational database server in a particular relational form.

31. The one or more non-transitory computer readable media of claim 30, wherein the instructions, when executed by the one or more computing devices, further cause:

determining, by said multidimensional database server, said particular relational form.

32. The one or more non-transitory computer readable media of claim 30, wherein the instructions, when executed by the one or more computing devices, further cause:

determining, by said multidimensional database server, said particular relational form.

33. The one or more non-transitory computer readable media of claim 30, wherein the instructions, when executed by the one or more computing devices, further cause:

determining, by said multidimensional database server, said particular relational form based on information contained in said relational database statement.

34. The one or more non-transitory computer readable media of claim 30, wherein passing the filtered set of one or more values from the data subset comprises passing said filtered set of one or more values in a form of a relational database table.

35. The one or more non-transitory computer readable media of claim 30, wherein said relational database statement is a SQL statement, and wherein filtered set of one or more values from the data subset comprises passing said filtered set of one or more values in said particular relational form to a SQL processor of said relational database server.

36. The one or more non-transitory computer readable media of claim 30, wherein the instructions, when executed by the one or more computing devices, further cause:

dynamically generating one or more abstract data type definitions that define formats of at least a portion of said values from said subset.

37. The one or more non-transitory computer readable media claim 36, wherein the instructions, when executed by the one or more computing devices, further cause:

the multidimensional database server communicating the dynamically generated abstract data type definitions to the relational database server, and

the relational database server accessing data in the data filtered set of one or more values based on the abstract data type definitions.

38. The one or more non-transitory computer readable media of claim 30, wherein passing said filtered set of one or more values in a particular relational form comprises passing said filtered set of one or more values in a form of a relational database table in which a particular column is associated with one or more particular measure values, wherein the instructions, when executed by the one or more computing devices further cause:

the multidimensional database server dynamically creating an abstract data type definition that defines a format of said values in said column.

39. The one or more non-transitory computer readable media of claim 24, wherein the instructions, when executed by the one or more computing devices, further cause:

generating, by said multidimensional database server, the filtered set of one or more values by comparing values in cells of said data subset to the value-filtering criteria.

40. The one or more non-transitory computer readable media of claim 39, wherein generating said filtered set of one or more values comprises:

looping through each cell associated with a respective range of keys for each of a plurality of dimensions of said n-dimensional data objects; and

comparing a measure value associated with each respective cell to a corresponding measure value specified in a condition in said relational database statement.

41. The one or more non-transitory computer readable media of claim 39, wherein generating said filtered set of one or more values comprises:

identifying cells of said data subset that satisfy cell-filtering criteria specified in said relational database statement;

comparing values, to said value-filtering criteria, only from cells of said data subset that satisfy said cell-filtering criteria.

42. The one or more non-transitory computer readable media of claim 39, wherein generating said filtered set of one or more values comprises:

identifying, based on said relational database statement, all measures that are required by the relational database server to execute said relational database statement; and

presenting, as at least a portion of said filtered set of one or more values, measure values for said measures that are required by the relational database server to execute said relational database statement.

43. The one or more non-transitory computer readable media of claim 42, wherein presenting comprises presenting only measure values for said measures that are required by the relational database server to execute said relational database statement.

44. The one or more non-transitory computer readable media of claim 42, wherein generating said filtered set of one or more values comprises:

comparing, to said value-filtering criteria, only values for said measures that are required by the relational database server to execute said relational database statement.

45. The one or more non-transitory computer readable media of claim 39, wherein generating said filtered set of one or more values comprises:

identifying, based on said relational database statement, all dimensions from which associated values are required by the relational database server to execute said relational database statement; and

presenting, as at least a portion of said filtered set of one or more values, values associated with said dimensions from which associated values are required by the relational database server to execute said relational database statement.

46. The one or more non-transitory computer readable media of claim 45, wherein presenting comprises presenting only values associated with said dimensions from which associated values are required by the relational database server to execute said relational database statement.