Analytical server including metrics engine

• US 7,890,546 B2
• Filed: 02/03/2006
• Issued: 02/15/2011
• Est. Priority Date: 04/17/2000
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Hyperion Solutions Corporation (Oracle Corporation)
• Inventors: Novy, Robert F., Shah, Arun
• Primary Examiner(s): Mofiz; Apu M
• Assistant Examiner(s): GIRMA, ANTENEH B
• Application Number:
  US11/347,468
• Publication Number:
  US 20060129564A1
• Time in Patent Office: 1,838 Days
• Field of Search: 707/1, 707/2, 707/3, 707/9, 707/10, 707/100, 707/101, 707/102, 707/104.1, 709/203, 709/225
• US Class Current: 707/803
• CPC Class Codes: G06F 16/2454 Optimisation of common expressions
• CPC Class Codes: G06F 16/2465 Query processing support for facilitating data mining operations in structured databases
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 21/6218 to a system of files or objects, e.g. local or distributed file system or database
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/956 Hierarchical
• CPC Class Codes: Y10S 707/957 Multidimensional
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Disclosed is a method for determining database queries to be sent by an analytical server to a Relational Dababase Monitoring System (RDBMS) by using hierarchical level metadata to describe the various structures within the database.

Claims

1. A method of operating an analytical server which is positioned between a client and a Relational Database Management System (RDBMS), the method operable to provide an interface between the client and a relational database located on the RDBMS, the relational database having fact tables, dimension tables, and metadata describing the relationships between the fact and dimension tables, the method comprising:

receiving in the analytical server at least a portion of the metadata information about the relational database and its organization, the metadata information including information about a logical hierarchy of a first dimension table and a logical hierarchy of a second dimension table and information about an aggregate table in the database,

wherein the aggregate table comprises values from a fact table summarized up to a first specified hierarchical level of a first dimension corresponding to the first dimension table and up to a second specified hierarchical level of a second dimension corresponding to the second dimension table, and

wherein the metadata information about the aggregate table includes information on the first and the second specified hierarchical levels of the first and the second dimensions respectively;

receiving in the analytical server, a metric query having a first required hierarchical level and a second required hierarchical level for each of the first dimension and the second dimension respectively;

determining whether the aggregate table supports the metric query by determining that the first specified hierarchical level of the first dimension of the aggregate table is at the same level or higher level than the first required hierarchical level of the first dimension in the metric query, and that the second specified hierarchical level of the second dimension of the aggregate table is at the same level or higher level than the second required hierarchical level of the second dimension in the metric query;

determining at least one database query according to the aggregate table if the aggregate table supports the metric query; and

sending the determined at least one database query to the RDBMS.

2. The method of claim 1 wherein a star schema is defined in the relational database, wherein the star schema comprises a single fact table and zero or more dimension tables which can be joined to the fact table according to selection constraints and aggregate groupings specified in the metadata.

3. The method of claim 2 wherein the metadata information about the aggregate table includes an array of integers wherein a first element of the array stores a number representing the first specified hierarchical level of the first dimension, and a second element of the array stores a number representing the second specified hierarchical level of the second dimension of the aggregate table,

and wherein determining whether the aggregate table supports the metric query involves comparing the integer stored in the first element of the array with the first required hierarchical level of the first dimension in the metric query and comparing the integer stored in the second element of the array with the second required hierarchical level of the second dimension in the metric query.

4. The method of claim 1 wherein the logical hierarchy of each of the first and the second dimension table includes a plurality of hierarchical levels of each of the first and the second dimension table respectively, a highest level in the plurality of hierarchical levels including all elements of that dimension table and a lower level in the plurality of hierarchical levels further subdividing all the elements into two or more non-overlapping groups.

5. The method of claim 4 wherein the first dimension table is a time dimension table and wherein the elements at the highest level in the logical hierarchy for the time dimension table includes year, quarter, month, week and day and wherein the second dimension table is a sales geography dimension table and wherein the elements at the highest level in the logical hierarchy for the sales geography dimension table includes country, region, and territory.

6. The method of claim 5, wherein the first dimension of the aggregate table is a time dimension corresponding to the time dimensional table summarized up to the first specified hierarchical level and the second dimension of the aggregate table is a sales geography dimension corresponding to the sales geography dimension table and is summarized up to the second specified hierarchical level.

7. The method of claim 1, wherein the metadata further comprises security hierarchy levels assigned to certain measures within the relational database and wherein the at least a portion of the metadata received in the analytical server, from the RDBMS, includes information about the security hierarchy levels, the method further comprising:

determining, based in part on the received information regarding the security hierarchy levels of the first and the second dimension tables and based on the metric query received from the client, which measures of the first and the second dimension tables are available in the relational database for responding to the metric query.

8. The method of claim 1, further comprising:

collecting usage statistics based upon the required availability and use of the fact and dimension tables within the database; and

reporting the usage statistics, the reporting of the usage statistics based at least in part on the first and the second specified hierarchical levels of the respective first and the second dimension tables accessed.

9. The method of claim 1, and further comprising:

receiving a plurality of metric queries from the client, the plurality of metric queries requesting at least two metrics which are broken down at the same hierarchical level; and

sending at least one database query to the RDBMS which retrieves measures from the RDBMS which are usable in common between the at least two metrics.

10. The method of claim 1 further comprising, determining specific measures required for calculating the metric query and determining for each measure an aggregate table that supports required hierarchical levels of dimensions in the metric query.

11. The method of claim 1 further comprising, when the aggregate table does not support the metric query:

determining whether a star group comprising the aggregate table comprises a second aggregate table that supports the metric query;

determining at least one database query according to the second aggregate table in the star group if the second aggregate table supports the metric query.

12. The method of claim 11 further comprising querying the database to calculate the metrics if the second aggregate table does not support the metric query.

13. The method of claim 1 wherein the first and the second specified hierarchical levels are specified based on the logical hierarchy of the first dimension table and the second dimension table respectively.

14. A method of operating an analytical server which is positioned between a client and a Relational Database Management System (RDBMS), the method operable to provide an interface between the client and a relational database located on the RDBMS, the relational database having fact tables, dimension tables, and metadata describing the relationships between the fact and dimension tables, the method comprising:

receiving in the analytical server at least a portion of the metadata information about the relational database and its organization, the metadata information including information about a logical hierarchy of a first dimension table and a logical hierarchy of a second dimension table and information about a first aggregate table in the database,

wherein the first aggregate table comprises values from a fact table summarized up to a first specified hierarchical level of a first dimension corresponding to the first dimension table and up to a second specified hierarchical level of a second dimension corresponding to the second dimension table, and

wherein the metadata information about the first aggregate table includes information on the first and the second specified hierarchical levels of the first and the second dimensions respectively;

receiving in the analytical server, a first metric query having a first required hierarchical level and a second required hierarchical level for each of the first dimension and the second dimension respectively;

determining whether the first aggregate table supports the first metric query by determining that the first specified hierarchical level of the first dimension of the first aggregate table is at the same level or higher level than the first required hierarchical level of the first dimension in the first metric query, and that the second specified hierarchical level of the second dimension of the first aggregate table is at the same level or higher level than the second required hierarchical level of the second dimension in the first metric query;

determining a first database query according to the first aggregate table if the first aggregate table supports the first metric query;

receiving in the analytical server, a second metric query;

determining a second aggregate table that supports the second metric query up to required hierarchical levels of dimensions in the second metric query;

determining a second database query according to the second aggregate table; and

sending a combined database query to the RDBMS where the combined query combines the first database query and the second database query when the first database query and the second database query have a common fact table and at least one common dimension.

15. The method of claim 14 wherein the first dimension table is a time dimension table and wherein elements at the highest level in the logical hierarchy for the time dimension table includes year, quarter, month, week and day and wherein the second dimension table is a sales geography dimension table and wherein elements at the highest level in the logical hierarchy for the sales geography dimension table includes country, region, and territory.

16. The method of claim 15, wherein the first dimension of the first aggregate table is a time dimension corresponding to the time dimension table summarized up to the first specified hierarchical level and the second dimension of the first aggregate table is a sales geography dimension corresponding to the sales geography dimension table and is summarized up to the second specified hierarchical level.

17. The method of claim 14, wherein the metadata further comprises security hierarchy levels assigned to certain measures within the relational database and wherein the at least a portion of the metadata received in the analytical server, from the RDBMS, includes information about the security hierarchy levels, the method further comprising:

determining, based in part on the received information regarding the security hierarchy levels of the dimension tables and based on the metric query received from the client, which measures of the dimension tables are available in the relational database for responding to the metric query.

18. The method of claim 14, and further comprising:

collecting usage statistics based upon the required availability and use of the fact and dimension tables within the database; and

reporting the usage statistics, the reporting of the usage statistics based at least in part on the hierarchical level of the dimension tables accessed.

19. A computer readable storage medium storing a plurality of instructions wherein execution of the plurality of instructions by a processor causes said processor to provide access to tables stored in a relational database located on a relational database management system (RDBMS) in order to compute a metric query, the relational database comprising fact tables, dimension tables, and metadata describing the relationships between the fact and dimension tables, the plurality of instructions comprising instructions for:

receiving at least a portion of the metadata information about the relational database and its organization, the metadata information including information about a logical hierarchy of a first dimension table and a logical hierarchy of a second dimension table and information about an aggregate table in the database,

wherein the aggregate table comprises values from a fact table summarized up to a first specified hierarchical level of a first dimension corresponding to the first dimension table and up to a second specified hierarchical level of a second dimension corresponding to the second dimension table,

wherein the metadata information about the aggregate table includes information on the first and the second specified hierarchical levels of the first and the second dimensions respectively;

receiving the metric query having a first required hierarchical level and a second required hierarchical level for each of the first dimension and the second dimension respectively;

determining whether the aggregate table supports the metric query by determining that the first specified hierarchical level of the first dimension of the aggregate table is at the same level or higher level than the first required hierarchical level of the first dimension in the metric query, and that the second specified hierarchical level of the second dimension of the aggregate table is at the same level or higher level than the second required hierarchical level of the second dimension in the metric query;

determining at least one database query according to the aggregate table if the aggregate table supports the metric query; and

sending the determined at least one database query to the RDBMS.

20. The computer readable storage medium of claim 19 further comprising a first set of instructions of the plurality of instructions that are executed when the aggregate table does not support the metric query, the first set of instructions comprising:

determining whether a star group comprising the aggregate table comprises a second aggregate table that supports the metric query;

determining at least one database query according to the second aggregate table in the star group if the second aggregate table supports the metric query.

21. The computer readable storage medium of claim 20 further comprising instructions for querying the database to calculate the metrics if the second aggregate table does not support the metric query.