Reporting engine for data warehouse

• US 7,143,107 B1
• Filed: 06/26/2003
• Issued: 11/28/2006
• Est. Priority Date: 06/26/2003
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Nebres, Diosdado L. Jr.
• Primary Examiner(s): Rimell, Sam
• Assistant Examiner(s): Kim, Paul
• Application Number:
  US10/606,720
• Time in Patent Office: 1,251 Days
• Field of Search: 707/3, 707/4, 707/6, 707/10, 707/2, 707/100, 707/101, 707/1, 707/200, 707/102, 715/505
• US Class Current: 707/603
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A reporting engine for generating customized reports from relational data stored in a database. Metadata associated with the data in the database describes the data according to one or more characteristics of the data. One or more of tables organizing the metadata is exposed as a dimension having at least one column of attributes. The reporting enging compares search criteria, specified by a request from a user, to the metadata attributes, and executes a predefined procedure for retrieving selected data from the database. The procedure constructs a query as a function of the comparison between the search criteria and the metadata attributes for implementing the query to optimize retrieval of the selected data from the database. Other aspects of the invention are directed to computer-readable media for use in connection with the reporting engine.

Claims

1. A method of generating a customized report from relational data stored in a database, said method comprising:

providing metadata associated with the data in the database, said metadata describing the data according to one or more characteristics of the data, said metadata being associated with one or more attributes and measures;

defining a plurality of tables according to the characteristics of the data in the database as described by the metadata;

estimating a size of each of the defined tables;

receiving a request for information from a user, said request specifying search criteria, said search criteria specifying at least one of said one or more attributes and measures; and

executing a predefined procedure for comparing the search criteria specified by the received request to the metadata and identifying at least one of the defined tables containing data described by metadata associated with substantially all of the attributes and the greatest number of measures specified in the search criteria, said procedure further constructing a query based on the identified at least one of the defined tables having the smallest estimated size, said query retrieving selected data from the at least one of the defined tables having the smallest estimated size in response to the request for information as a function of the characteristics of the data as described by the metadata for optimizing retrieval of the selected data.

2. The method of claim 1, further comprising generating the report in response to the query, said report including the selected data retrieved from the database when the procedure is executed.

3. The method of claim 1, wherein the request from the user specifies a format for the report, search criteria, or both.

4. The method of claim 1, further comprising defining a plurality of tables for organizing the metadata relative to the data in the database.

5. The method of claim 4, wherein one or more of the tables is exposed as a dimension having one or more columns of attributes.

6. The method of claim 5, further comprising creating a structured query language (SQL) view for each of the tables to be exposed as a dimension.

7. The method of claim 4, further comprising creating, for each fact, a fact view to expose attributes and measures for querying.

8. The method of claim 4, wherein each table contains metadata associated with one of the following:

an attribute;

a dimension;

a measure;

or a fact.

9. The method of claim 1, wherein executing the predefined procedure to construct the query includes matching the metadata to search criteria in the request for information.

10. The method of claim 1, wherein the query constructed by the predefined procedure comprises one or more SQL statements.

11. The method of claim 1, wherein providing the metadata further comprises defining measures and associating the defined measures to applicable facts.

12. The method of claim 1, further comprising generating a SQL view to display information relating to the query constructed by the predefined procedure.

13. The method of claim 1, further comprising defining a query syntax according to which the request for information specifies the search criteria for the selected data.

14. The method of claim 13, wherein the request for information according to the query syntax comprises one or more delimited lists.

15. The method of claim 13, wherein the request for information according to the query syntax includes a user-selected input representative of one or more of the following:

a column list parameter;

a slice parameter;

a fact-type parameter;

a crosstab result parameter;

an options parameter; and

a sort order parameter.

16. The method of claim 13, wherein the query syntax includes a user-selected input representative of a column list parameter and wherein the column list parameter comprises a delimited list of dimensions, measures, or both, said delimited list representing a type of data to be retrieved from the database in response to the query.

17. The method of claim 13, wherein the query syntax includes a user-selected input representative of a slice parameter and wherein the slice parameter specifies a date range search criteria.

18. The method of claim 13, wherein the query syntax includes a user-selected input representative of a slice parameter and wherein the slice parameter specifies a name search criteria.

19. The method of claim 13, wherein the query syntax includes a user-selected input representative of an options parameter and wherein the options parameter specifies one or more of the following:

a debugging option;

a non-sorting option;

a row limiting option; and

a hierarchical navigation option.

20. The method of claim 1, wherein the predefined procedure comprises a template for generating a set of SQL statements for implementing the query.

21. One or more computer-readable media have computer-executable instructions for performing the method of claim 1.

22. A method of constructing a query for retrieving selected data from a database in response to a request from a user for information, said method comprising:

defining metadata to describe data in the database according to one or more characteristics of the data, said metadata being associated with one or more attributes and measures;

defining a plurality of tables according to the characteristics of the data in the database as described by the metadata;

estimating a size of each of the defined tables;

comparing search criteria specified by the request for information to the metadata attributes, said search criteria specifying at least one of the one or more attributes and measures; and

executing a predefined procedure in response to the request for information to identify at least one of the defined tables containing data described by metadata associated with substantially all of the attributes and the greatest number of measures specified in the search criteria, said procedure further generating a set of structured query language (SQL) statements based on the identified at least one of the defined tables having the smallest estimated size, said query retrieving selected data from the at least one of the identified tables having the smallest estimated size as a function of the characteristics of the data as described by the metadata to optimize retrieval of the selected data from the database.

23. The method of claim 22, further comprising generating the report in response to the query, said report including the selected data retrieved from the database when the procedure is executed.

24. The method of claim 22, further comprising creating a SQL view for each of the tables to be exposed as a dimension.

25. The method of claim 22, further comprising creating, for each fact, a fact view to expose attributes and measures for querying.

26. The method of claim 22, wherein each table contains metadata associated with one of the following:

an attribute;

a dimension;

a measure;

or a fact.

27. The method of claim 22, wherein defining the metadata further comprises defining measures and associating the defined measures to applicable facts.

28. The method of claim 22, further comprising generating a SQL view to display information relating to the query constructed by the predefined procedure.

29. The method of claim 22, further comprising defining a query syntax according to which the request for information identifies search criteria for the selected data.

30. The method of claim 29, wherein the request for information according to the query syntax comprises one or more user-selected parameters representing a type of data to be retrieved from the database in response to the query.

31. One or more computer-readable media have computer-executable instructions for performing the method of claim 22.

32. One or more computer-readable media having computer-executable components for generating a report from data stored in a database, said computer-readable media comprising:

a metadata component for describing the data in the database according to one or more characteristics of the data, said metadata component further defining a plurality of tables according to the characteristics of the data in the database as described by the metadata, said metadata being associated with one or more attributes and measures, said metadata component further estimating a size of each of the defined tables;

an interface component for receiving a request for information from a user, said request specifying search criteria, said search criteria specifying at least one of the one or more attributes and measures; and

a procedure component responsive to the request for information for constructing a query to retrieve selected data from the database, said procedure component comparing the search criteria specified by the received request to the metadata and identifying at least one of the defined tables containing data described by metadata associated with substantially all of the attributes and the greatest number of measures specified in the search criteria, said procedure further constructing the query based on the identified at least one of the defined tables having the smallest estimated size, said query retrieving selected data from the database in response to the request for information as a function of the characteristics of the data as described by the metadata for optimizing retrieval of the selected data.

33. The computer-readable media of claim 32, wherein the request from the user specifies a format for the report, search criteria, or both.

34. The computer-readable media of claim 32, wherein the metadata component comprises a plurality of tables containing metadata, said tables organizing the metadata relative to the data in the database.

35. The computer-readable media of claim 34, wherein at least one of the tables is exposed as a dimension having one or more columns of attributes.

36. The computer-readable media of claim 35, wherein the metadata component includes a structured query language (SQL) view for each of the tables to be exposed as a dimension.

37. The computer-readable media of claim 36, wherein the metadata component automatically populates the metadata through the SQL view.

38. The computer-readable media of claim 34, wherein the metadata component includes, for each fact, a fact view to expose attributes and measures for querying.

39. The computer-readable media of claim 34, wherein each table contains metadata associated with one of the following:

an attribute;

a dimension;

a measure;

or a fact.

40. The computer-readable media of claim 32, wherein the query constructed by the procedure component comprises one or more SQL statements.

41. The computer-readable media of claim 32, wherein the interface component comprises a query syntax according to which the request for information identifies search criteria for the selected data.

42. The computer-readable media of claim 41, wherein the request for information according to the query syntax comprises one or more delimited lists.

43. The computer-readable media of claim 41, wherein the request for information according to the query syntax includes a user-selected input representative of one or more of the following:

a column list parameter;

a slice parameter;

a fact-type parameter;

a crosstab result parameter;

an options parameter; and

a sort order parameter.

44. The computer-readable media of claim 41, wherein the query syntax includes a user-selected input representative of a column list parameter and wherein the column list parameter comprises a delimited list of dimensions, measures, or both, representing a type of data to be retrieved from the database in response to the query.

45. The computer-readable media of claim 41, wherein the query syntax includes a user-selected input representative of a slice parameter and wherein the slice parameter specifies a date range search criteria.

46. The computer-readable media of claim 41, wherein the query syntax includes a user-selected input representative of a slice parameter and wherein the slice parameter specifies a name search criteria.

47. The computer-readable media of claim 41, wherein the query syntax includes a user-selected input representative of an options parameter and wherein the options parameter specifies one or more of the following:

a debugging option;

a non-sorting option;

a row limiting option; and

a hierarchical navigation option.

48. The computer-readable media of claim 32, wherein the procedure component comprises a predefined template for generating a set of SQL statements for implementing the query.

49. The computer-readable media of claim 32, wherein the interface component comprises an application programming interface.