Analytical server including metrics engine

• US 20040122813A1
• Filed: 12/08/2003
• Published: 06/24/2004
• 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
• Granted Patent: US 7,031,953 B2
• Time in Patent Office: Days
• US Class Current: 707/3
• 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

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.

11. 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, wherein the fact tables include measures which can be additive or non-additive, and wherein the metadata includes a designation of which measures are additive and which measures are non-additive, the method comprising:

(a) receiving in the analytical server, from the RDBMS, at least a portion of the metadata information regarding the relational database and its organization, including information about whether certain measures are additive and information about a logical hierarchy of the fact and dimension tables;

(b) receiving in the analytical server, from the client, a metric query, the metric query calling for a metric broken down across a dimension and also calling for a roll-up of that metric;

(c) determining, based on the metric query received from the client and based on the received information from the RDBMS regarding the logical hierarchy of the fact and dimension tables and whether the measures of the requested metric are additive, which hierarchical levels of the fact and dimension tables are available in the relational database for responding to the metric query;

(d) determining at least one database query according to the available hierarchical levels of the fact and dimension tables; and

(e) sending the determined at least one database query to the RDBMS whereby the metric query can be responded to by the analytical server based on the responses of the RDBMS to the determined at least one database query, whereby the response is tailored to the most efficient hierarchical level necessary depending on whether the measures of the requested metric are additive.

12. The method of claim 11 wherein the designation of which measures are additive and which are non-additive further specifies by dimension which measures are additive and which are non-additive.

13. The method of claim 12 wherein the determining of the at least one database query takes into account whether the requested metric is additive specifically across the requested dimension.

14-1. The method of claim 14 wherein the client'"'"'s security level is set according to a user'"'"'s log-in.

15. The method of claim 14 and further comprising determining whether any response to the metric query is possible for the client'"'"'s configured security level.

3. The method of claim 2 wherein the metadata includes a table which stores the supported level for each dimension used in the star schema.

16. The method of claim 15 and further comprising determining at least one database query according to the available hierarchical levels of the fact and dimension tables based on the client'"'"'s configured security level.

17. The method of claim 16 wherein the requested hierarchical level along the time dimension was for a measure broken down by month.

18. The method of claim 17 wherein to approximate the measure on the monthly basis, the measure broken down by quarter is divided by three and assigned to the requested months.

19. The method of claim 18 wherein to approximate the measure on the monthly basis, the measure is repeated at the requested months.

20. The method of claim 19 wherein the measure was an average.

15-2. 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:

(a) receiving in the analytical server, from the RDBMS, at least a portion of the metadata information regarding the relational database and its organization, including information about a logical hierarchy of the fact and dimension tables;

(b) receiving in the analytical server, from the client, a metric query;

(c) determining, based on the received information regarding the logical hierarchy of the fact and dimension tables and based on the metric query received from the client, which hierarchical levels of the fact and dimension tables are available in the relational database for responding to the metric query, wherein at least one measure within the fact and dimension tables are insufficiently fine in a requested dimension in order to respond to the metric query;

(d) determining, according to the available hierarchical levels of the fact and dimension tables, at least one metric by which the at least one measure which was unavailable at the requested fineness can be approximated by the using the measure along the requested dimension at a higher hierarchical level than the one needed to directly respond to the metric query; and

(e) sending a database query to the RDBMS based on the determining of available hierarchical levels of the fact and data tables and upon the at least one higher hierarchical level for the needed measure along the dimension which was unavailable at the needed hierarchical level.

16-3. The method of claim 15 wherein the measure which was unavailable at the requested fineness was a time measure.