Systems, methods and computer program products to determine useful relationships and dimensions of a database

• US 20030212676A1
• Filed: 05/10/2002
• Published: 11/13/2003
• Est. Priority Date: 05/10/2002
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Coates, Marlene L., Saueressig, Guilherme, Robinson, Gary, Bruce, Jay M.
• Granted Patent: US 6,947,929 B2
• Time in Patent Office: Days
• US Class Current: 707/5
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• 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/24575 using context
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• 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

Abstract

Systems, methods, and computer products that efficiently determine useful dimensions associated with a database when employing OLAP processing techniques. The present invention enables a user to see at a glance in which queries certain columns and tables are used. The present invention may be implemented with a query miner module that may include a query browser module that improves browsing through queries and their components over the past. The preferred embodiment of the present invention provides an easy to use graphical interface showing the queries, tables, and columns in a tree structure. Further, the preferred embodiment of the present invention presents information about relationships and dimensions associated with a database and about columns, tables, and queries to the user without discernable lag time between the user'"'"'s request and the generated information.

Claims

1. A computer-implemented method, said computer having a database having metadata, at least one table with at least one column, said computer having at least one query object directed to said database, said method efficiently determining useful dimensions associated with said database, comprising:

executing said query object against said database;

applying user-specific heuristics as rules to said metadata via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

determining said useful dimensions by analyzing said statistical information.

2. The computer-implemented method of claim 1 further comprising including said user-specific heuristics in at least one structured query language statement.

3. The computer-implemented method of claim 1 further comprising, when said column is associated with said query object, including frequency information about said column in said statistical information.

4. The computer-implemented method of claim 1 further comprising, when said table is associated with said query object, including frequency information about said table in said statistical information.

5. The computer-implemented method of claim 1 further comprising, executing said query object against said database.

6. A computer-implemented method, said computer having a database having data, at least one table with at least one column, said computer having at least one query object directed to said database, said method efficiently determining useful dimensions associated with said database, comprising:

executing said query object against said database;

applying user-specific heuristics as rules to said data via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

determining said useful dimensions by analyzing said statistical information.

7. The computer-implemented method of claim 6 further comprising including said user-specific heuristics in at least one structured query language statement.

8. The computer-implemented method of claim 6 further comprising, when said column is associated with said query object, including frequency information about said column in said statistical information.

9. The computer-implemented method of claim 6 further comprising, when said table is associated with said query object, including frequency information about said table in said statistical information.

10. The computer-implemented method of claim 6 further comprising, executing said query object against said database.

11. A computer system for determining useful dimensions associated with a database, said database having metadata, at least one table with at least one column, said computer system having at least one query object directed to said database, comprising:

said query object being executed against said database;

user-specific heuristics being applied as rules to said metadata via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

said statistical information being analyzed thereby determining said useful dimensions.

12. The computer system of claim 11 further comprising said user-specific heuristics including at least one structured query language statement.

13. The computer system of claim 11 further comprising, when said column is associated with said query object, said statistical information including frequency information about said column.

14. The computer system of claim 11 further comprising, when said table is associated with said query object, said statistical information including frequency information about said table.

15. The computer system of claim 11 further comprising, said query object being executed against said database.

16. A computer system for determining useful dimensions associated with a database, said database having data, at least one table with at least one column, said computer system having at least one query object directed to said database, comprising:

said query object being executed against said database;

user-specific heuristics being applied as rules to said data via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

said statistical information being analyzed thereby determining said useful dimensions.

17. The computer system of claim 16 further comprising said user-specific heuristics including at least one structured query language statement.

18. The computer system of claim 16 further comprising, when said column is associated with said query object, said statistical information including frequency information about said column.

19. The computer system of claim 16 further comprising, when said table is associated with said query object, said statistical information including frequency information about said table.

20. The computer system of claim 16 further comprising, said query object being executed against said database.

21. An article of manufacture comprising a computer usable medium embodying one or more instructions executable by said computer for causing said computer to efficiently determine useful dimensions associated with a database, said database having metadata, at least one table with at least one column, said computer having at least one query object directed to said database, wherein:

said computer usable instructions execute said query object against said database;

said computer usable instructions apply user-specific heuristics as rules to said metadata via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

said computer usable instructions determine said useful dimensions by analyzing said statistical information.

22. The article of manufacture of claim 21 wherein said computer usable instructions include said user-specific heuristics in at least one structured query language statement.

23. The article of manufacture of claim 21 wherein said computer usable instructions, when said column is associated with said query object, include frequency information about said column in said statistical information.

24. The article of manufacture of claim 21 wherein said computer usable instructions, when said table is associated with said query object, include frequency information about said table in said statistical information.

25. The article of manufacture of claim 21 wherein said computer usable instructions execute said query object against said database.

26. An article of manufacture comprising a computer usable medium embodying one or more instructions executable by said computer for causing said computer to efficiently determine useful dimensions associated with a database, said database having data, at least one table with at least one column, said computer having at least one query object directed to said database, wherein:

said computer usable instructions execute said query object against said database;

said computer usable instructions apply user-specific heuristics as rules to said data via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

said computer usable instructions determine said useful dimensions by analyzing said statistical information.

27. The article of manufacture of claim 26 wherein said computer usable instructions include said user-specific heuristics in at least one structured query language statement.

28. The article of manufacture of claim 26 wherein said computer usable instructions, when said column is associated with said query object, include frequency information about said column in said statistical information.

29. The article of manufacture of claim 26 wherein said computer usable instructions, when said table is associated with said query object, include frequency information about said table in said statistical information.

30. The article of manufacture of claim 26 wherein said computer usable instructions execute said query object against said database.

31. A computer-implemented method, said computer having a database having metadata, at least one table with at least one column, said computer having at least one query object directed to said database, said method efficiently determining useful relationships associated with said database, comprising:

executing said query object against said database;

applying user-specific heuristics as rules to said metadata via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

determining said useful relationships by analyzing said statistical information.

32. The computer-implemented method of claim 31 further comprising including said user-specific heuristics in at least one structured query language statement.

33. The computer-implemented method of claim 31 further comprising, when said column is associated with said query object, including frequency information about said column in said statistical information.

34. The computer-implemented method of claim 31 further comprising, when said table is associated with said query object, including frequency information about said table in said statistical information.

35. The computer-implemented method of claim 31 further comprising, executing said query object against said database.

36. A computer-implemented method, said computer having a database having data, at least one table with at least one column, said computer having at least one query object directed to said database, said method efficiently determining useful relationships associated with said database, comprising:

executing said query object against said database;

applying user-specific heuristics as rules to said data via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

determining said useful relationships by analyzing said statistical information.

37. The computer-implemented method of claim 36 further comprising including said user-specific heuristics in at least one structured query language statement.

38. The computer-implemented method of claim 36 further comprising, when said column is associated with said query object, including frequency information about said column in said statistical information.

39. The computer-implemented method of claim 36 further comprising, when said table is associated with said query object, including frequency information about said table in said statistical information.

40. The computer-implemented method of claim 36 further comprising, executing said query object against said database.

41. A computer system for determining useful relationships associated with a database, said database having metadata, at least one table with at least one column, said computer system having at least one query object directed to said database, comprising:

said query object being executed against said database;

user-specific heuristics being applied as rules to said metadata via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

said statistical information being analyzed thereby determining said useful relationships.

42. The computer system of claim 41 further comprising said user-specific heuristics including at least one structured query language statement.

43. The computer system of claim 41 further comprising, when said column is associated with said query object, said statistical information including frequency information about said column.

44. The computer system of claim 41 further comprising, when said table is associated with said query object, said statistical information including frequency information about said table.

45. The computer system of claim 41 further comprising, said query object being executed against said database.

46. A computer system for determining useful relationships associated with a database, said database having data, at least one table with at least one column, said computer system having at least one query object directed to said database, comprising:

said query object being executed against said database;

user-specific heuristics being applied as rules to said data via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

said statistical information being analyzed thereby determining said useful relationships.

47. The computer system of claim 46 further comprising said user-specific heuristics including at least one structured query language statement.

48. The computer system of claim 46 further comprising, when said column is associated with said query object, said statistical information including frequency information about said column.

49. The computer system of claim 46 further comprising, when said table is associated with said query object, said statistical information including frequency information about said table.

50. The computer system of claim 46 further comprising, said query object being executed against said database.

51. An article of manufacture comprising a computer usable medium embodying one or more instructions executable by said computer for causing said computer to efficiently determine useful relationships associated with a database, said database having metadata, at least one table with at least one column, said computer having at least one query object directed to said database, wherein:

said computer usable instructions execute said query object against said database;

said computer usable instructions apply user-specific heuristics as rules to said metadata via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

said computer usable instructions determine said useful relationships by analyzing said statistical information.

52. The article of manufacture of claim 51 wherein said computer usable instructions include said user-specific heuristics in at least one structured query language statement.

53. The article of manufacture of claim 51 wherein said computer usable instructions, when said column is associated with said query object, include frequency information about said column in said statistical information.

54. The article of manufacture of claim 51 wherein said computer usable instructions, when said table is associated with said query object, include frequency information about said table in said statistical information.

55. The article of manufacture of claim 51 wherein said computer usable instructions execute said query object against said database.

56. An article of manufacture comprising a computer usable medium embodying one or more instructions executable by said computer for causing said computer to efficiently determine useful relationships associated with a database, said database having data, at least one table with at least one column, said computer having at least one query object directed to said database, wherein:

said computer usable instructions execute said query object against said database;

said computer usable instructions apply user-specific heuristics as rules to said data via said executing query object thereby collecting statistical information about said query object based on said user-specific heuristics; and

said computer usable instructions determine said useful relationships by analyzing said statistical information.

57. The article of manufacture of claim 56 wherein said computer usable instructions include said user-specific heuristics in at least one structured query language statement.

58. The article of manufacture of claim 56 wherein said computer usable instructions, when said column is associated with said query object, include frequency information about said column in said statistical information.

59. The article of manufacture of claim 56 wherein said computer usable instructions, when said table is associated with said query object, include frequency information about said table in said statistical information.

60. The article of manufacture of claim 56 wherein said computer usable instructions execute said query object against said database.