Reporting aggregate results from database queries

• US 6,405,207 B1
• Filed: 10/15/1999
• Issued: 06/11/2002
• Est. Priority Date: 10/15/1999
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Petculescu, Cristian, Netz, Amir
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US09/418,805
• Time in Patent Office: 970 Days
• Field of Search: 707/3, 707/4, 707/5, 707/100, 707/102, 707/104.1, 707/200, 707/203, 707/1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A facility for multidimensional, multilevel databases has a query syntax for specifying that aggregate-data cells representing higher levels of the database in a report include only the data from certain ones of the lower-level database items, rather than from all of them. A database engine extracts data from the lower-level query items from the database, and aggregates only the data from those items into one or more aggregate cells in accordance with a function named in the query.

Claims

1. A method for providing data in a report from a database having members at multiple hierarchical levels, the method comprising:

receiving a query containing a specification for data from one or more members at a higher of the database levels, the specification including a designation of one or more of the members at a lower of the levels, the members at the lower level being less than all the descendants of the member at the higher level;

placing the retrieved data into one or more base cells of the report;

aggregating the data from the designated lower-level members only;

placing the aggregated data into an aggregated cell of the report.

2. The method of claim 1 where the database has multiple dimensions.

3. The method of claim 2 where the query contains multiple specifications along different ones of the dimensions.

4. The method of claim 1 where aggregating the data comprises:

building a plurality of hierarchical context trees for the database;

identifying the maximal hierarchical contexts of the trees;

iterating recursively through each of the maximal hierarchical contexts and through each member thereof; and

replacing each of the members with an aggregate of the data of its designated lower-level members.

5. The method of claim 1 where aggregating the data from the base cells comprises:

iterating through multiple trees representing the database;

iterating through the members of each tree;

for at least one of the members, identifying the designated lower-level members of the one node; and

replacing the value of the one member with an aggregate of the data of its designated lower-level members.

6. The method of claim 1 where aggregating the data further comprises:

identifying static members of the tree during the iteration; and

for each static member, aggregating the value of all descendant members of the one member.

7. The method of claim 6 where aggregating the data further comprises:

identifying members of the tree that are not static members; and

for each such non-static member, aggregating the value of only those of the descendant members designated in the query.

8. A medium bearing instructions and data for causing a suitably programmed digital computer to carry out the method of claim 1.

9. A method for producing a report from a database having multiple levels, the method comprising:

receiving a query containing a specification of a visual total;

assembling certain database data designated in the query into a plurality of base cells in the report; and

aggregating the data from only the base cells into at least one aggregated cell in the report.

10. The method of claim 9 where the specification includes:

a designation of at least one database member at a higher of the database levels;

a designation of one or more contributor members at a lower of the levels; and

an indication that the aggregated cell is to contain data only from the contributor members.

11. The method of claim 10 where the certain data is associated with the contributor members.

12. The method of claim 9 where aggregating the data comprises:

building a plurality of hierarchical context trees for the database;

identifying the maximal hierarchical contexts of the trees;

iterating recursively through each of the maximal hierarchical contexts and through each member thereof; and

replacing each of the members with an aggregate of the data of its contributor members.

13. The method of claim 9 where aggregating the data from the base cells comprises:

iterating through the members of at least one tree representing the database;

for at least one of the members, identifying the contributor members of the one node; and

replacing the value of the one member with an aggregate of the data of its contributor members.

14. The method of claim 13 where the iteration is a non-recursive traversal of the one tree.

15. The method of claim 13 where aggregating the data further comprises:

identifying static members of the tree during the iteration; and

for each static member, aggregating the value of all descendant members of the one member.

16. The method of claim 15 where aggregating the data further comprises:

identifying members of the tree that are not static members; and

for each such non-static member, aggregating the value of only those of the descendant members designated in the query.

17. The method of claim 15 where identifying static members comprises determining that all descendants of one of a member are static.

18. A medium bearing representations of instructions and data for causing a suitably programmed digital computer to carry out the method of claim 9.

19. A query for requesting a report containing data from a multiple-level database, the query including:

a designation of at least one database member at a higher of the database levels;

a designation of one or more contributor members at a lower of the levels; and

an indication that the aggregate cell is to contain data only from the contributor members.

20. The query of claim 19 where the contributor members constitute less than all of the descendant members of the database member at the higher level.

21. A report containing data derived form a multiple-level database, the report including:

a plurality of base cells containing data from specified members at a lower level of the database; and

at least one aggregate cell representing a member at a higher level of the database, and containing data derived only from the specified members at the lower level.

22. The report of claim 21 further comprising a plurality of names, one of the names having a designation identifying the aggregate cell.

23. A system for providing data to a client, comprising:

a database having members at multiple levels;

a query processor for converting a specification requesting a visual total into a plan for producing a report including at least one aggregate cell containing the visual total;

an execution engine responsive to the plan for retrieving data from the database into a plurality of base cells designated in the specification and for aggregating data from only the base cells into at least one aggregated cell in the report.

24. The system of claim 23 where the execution engine iterates through the members of at least one tree representing the database, for at least one of the members, identifies the contributor members of the one node, and

replaces the value of the one member with an aggregate of the data of its contributor members.

25. A computer server for providing a report to a user, comprising:

a storage for holding a database having multiple levels;

communications equipment for receiving over a network a specification requesting a visual total from the database;

a processor for assembling data from the database into a plurality of base cells designated in the specification and for aggregating data from only the base cells into at least one aggregated cell in the report.

26. A medium bearing representations of instructions and data for causing a suitably programmed digital computer to carry out a method for producing a report from a database having multiple levels, the method comprising:

receiving a query containing a specification of a visual total;

assembling database data designated in the specification into a plurality of base cells in the report; and

aggregating the data from only the base cells into at least one aggregated cell in the report.

27. The medium of claim 26 where the representations are manifestations in a digital storage.

28. The medium of claim 26 where the representations are signals in a communications channel.