Method and system for providing aggregate data access

• US 20060020580A1
• Filed: 07/22/2004
• Published: 01/26/2006
• Est. Priority Date: 07/22/2004
• Status: Active Grant

• Current Assignee: Daedalus Blue LLC
• Original Assignee: International Business Machines Corporation
• Inventors: Dettinger, Richard D., Tenner, Jeffrey W., Stevens, Richard J.
• Granted Patent: US 8,341,172 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

A method and apparatus for defining and processing aggregate data is disclosed. Using database abstraction techniques, a set of logical fields may be used to compose queries of a set of underlying physical data sources. In one embodiment, a logical field may represent an aggregate data value calculated from the elements appearing in a column of a relational table in an underlying physical data source. The elements may be divided into to subsets to calculate multiple aggregate values. An abstract derived entity is a data object present in a database abstraction model that may be accessed as though it were a relational table contained in an underlying physical data source. In one embodiment, columns of the table defined by the abstract derived entity may be populated with aggregate data values joined to other data as specified by a composition rule included in the definition of the abstract derived entity.

Claims

1. A method for providing access to aggregate data values, comprising:

providing an abstract data layer comprising a set of logical fields for composing an abstract query, wherein each logical field provides an access method that specifies at least a method for accessing data corresponding to the logical field, and wherein the method for accessing data specified by at least one logical field comprises an aggregate access method that specifies a set of input data and an expression for determining an aggregate data value from the set of input data;

receiving, from a requesting entity, an abstract query composed from the set of logical fields that includes the at least one logical field specifying an aggregate access method;

retrieving the set of input data specified by the aggregate access method;

calculating at least one aggregate data value from the set of input data according to the expression specified by the aggregate access method.

2. The method of claim 1, wherein the set of input data comprises data elements retrieved from a column of a relational table stored in a relational database.

3. The method of claim 1, wherein the aggregate access method further specifies a grouping condition used to divide the set of input data into subsets, wherein each subset of input data is used to determine an aggregate data value.

4. The method of claim 1, further comprising:

transforming the logical fields included in the abstract query that do not specify an aggregate access method into a query contribution that is consistent with the data source specified by the access method for the logical fields;

accessing the data source corresponding to the logical fields using the query contribution and retrieving a set of query results data; and

returning, to the requesting entity, the at least one aggregate data value and the set of query results data.

5. The method of claim 1, wherein the abstract data layer further provides at least one abstract derived entity, wherein the abstract derived entity defines:

(i) sets of data elements selected from at least one of;

(a) data defined by other abstract derived entities;

(b) data stored in the multiple data sources; and

(c) data accessed using logical fields appearing in the data abstraction layer; and

(ii) a composition rule used to join the sets of data elements into a derived relational table wherein each set of data elements comprises a column of the derived relational table.

6. The method of claim 5, wherein at least one of the sets of data comprises aggregate data values obtained using the at least one logical field that specifies an aggregate access method.

7. The method of claim 6 wherein at least one access method references the abstract derived entity as the location of the data corresponding to the logical field.

8. The method of claim 7, wherein the at least one access method is a filtered access method that uses a filtering condition that constrains the data used to select the sets of data elements to data that satisfies the filtering condition.

9. The method of claim 6, wherein the abstract derived entity further defines at least one parametric condition applied to the derived relational table to restrict the rows included in the query results data to rows wherein at least the data element for one column satisfies the parametric condition.

10. The method of claim 9 further comprising, prompting the requesting entity for a value used to apply the parametric condition to the query results.

11. The method of claim 10, wherein prompting the requesting entity for a value used to apply the parametric condition to the query results comprises, presenting a user interacting with a computer connected to a network with one or more graphical user interface screens used to obtain the value.

12. The method of claim 5, further comprising:

transforming each other logical field included in the abstract query into a query contribution that is consistent with the data source specified by the access method for each logical field;

accessing the data source corresponding to each of the other logical fields using the query contribution and retrieving a set of query results data; and

returning, to the requesting entity, the at least one aggregate data value and the set of query results data.

13. The method of claim 6, wherein the query contribution for the at least one access method that references the abstract derived entity comprises an SQL query of the derived table defined by the abstract derived entity.

14. A system for generating aggregate data values from data stored in a data source, comprising:

a database service available in a network environment, the database service comprising (a) a data source, (b) an abstract data layer, wherein the abstract data layer comprises a plurality of logical fields used to compose an abstract query to query the data source, and for each logical field, providing an access method specifying at least a method for accessing the data, wherein at least one logical field specifies an aggregate access method; and

(c) a runtime component configured to process an abstract query that includes the at least one logical field by (i) retrieving a definition for the aggregate access method, (ii) determining aggregate data values according to the definition, (iii) merging the aggregate data values with query results obtained for other logical fields included in the abstract query, and (iv) returning the results to the requesting entity.

15. The system of claim 14, wherein the aggregate access method specifies a set of input data and an expression, wherein the expression defines an operation for determining an aggregate data value from the set of input data, and optionally defines a grouping condition used to divide the set of input of data into multiple subsets, wherein each subset is used to determine an aggregate data value according to the expression specified by the aggregate access method.

16. The system of claim 14, wherein the data source comprises a relational database and the set of input data comprises a column from a relational table stored in the database.

17. The system of claim 14, wherein the data source for at least one access method comprises an abstract derived entity that defines a derived relational table.

18. The system of claim 17, wherein the derived relational table is composed from sets of data elements selected from at least one of:

(i) data defined by other abstract derived entities;

(ii) data stored in the multiple data sources; and

(iii) data accessed using logical fields appearing in the data abstraction layer.

19. The system of claim 17, wherein at least one set of data included in the relational table comprises the aggregate data values generated from the at least at least one logical field that specifies an aggregate access method.

20. A computer-readable medium, that when executed by a processor performs operations comprising:

providing, for a requesting entity, an abstract data layer comprising a plurality of logical fields for composing an abstract query, and for each logical field;

defining an access method that specifies at least a method for accessing the data corresponding to the logical field, wherein at least one logical field specifies an aggregate access method for accessing the data corresponding to the at least one logical field;

receiving, from the requesting entity, an abstract query that includes the at least one logical field specifying an aggregate access method;

retrieving a set of input data defined by the aggregate access method;

determining at least an aggregate data value from the set of input data;

transforming each other logical field included in the abstract query into a query contribution that is consistent with the data source specified by the access method for each logical field;

accessing the data source corresponding to each of the other logical fields using the query contribution and retrieving a set of query results data; and

returning, to the requesting entity, the at least one aggregate data value and the set of query results data.