COMPLEX RELATIONAL DATABASE EXTRACTION SYSTEM AND METHOD WITH PERSPECTIVE BASED DYNAMIC DATA MODELING

• US 20100191718A1
• Filed: 12/18/2009
• Published: 07/29/2010
• Est. Priority Date: 12/19/2008
• Status: Abandoned Application

• Current Assignee: Aprimo Marketing Operations UK Ltd. (Memo Global Holdings LP)
• Original Assignee: Aprimo, Inc. (Teradata Corporation)
• Inventors: Lothamer, Jason H., Coriell, Michael L., Reynolds, Michael J., Rardin, Richard A., McDole, Michael D., Sublette, Tim A., Lawrence, John A.
• Application Number:
  US12/641,694
• Publication Number:
  US 20100191718A1
• Time in Patent Office: Days
• US Class Current: 707/714
• 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/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

The present invention involves a computer for implementing a complex database management system. A processor has at least one complex database with extraction software. The database extraction software is capable of creating a perspective based data model from a user selected data table of the database. A query is specified by user selection of attributes from a data table of the database. A perspective based data model is created from the user selected data base table of the database and related tables. The extraction software creates a query structure defining a plurality of paths providing a selected portion of the information in the database, which is presented to the user based on the perspective based data model. The query structure defines a plurality of paths and having a plurality of nodes, each of the nodes having a corresponding operand. A downward evaluation of the query structure is performed, proceeding from a starting object down downwardly through the paths. An upward evaluation of the query structure is performed, proceeding upwardly from the bottom of each path. The operands are resolved according to predetermined rules so that the number of evaluations of the nodes is less than the number of instances of each operand in the query structure.

Claims

1. A computer for performing operations on a complex database in a database management system, said computer comprising:

a processor;

at least one database containing information organized into multiple interdependent tables; and

a database extraction manager coupled to said processor and said at least one database, said database extraction manager capable of creating a perspective based data model from a user selected data table, said database extraction manager including software for dynamically creating a data structure representing a selected portion of the information stored in said at least one database, said selected portion being presented to the user based on the perspective based data model.

2. The computer of claim 1 wherein said at least one database includes tables having both dimension and measurement data, and said database extraction manager is capable of presenting possible results for dimension data, and possible statistical calculations of the measurement data.

3. The computer of claim 2 wherein said database extraction manager includes software for excluding possible counting, aggregation, and statistical calculations based the creation of said perspective based data model.

4. The computer of claim 2 wherein said database extraction manager determines cardinal relationships between the user selected data table and other portions of the perspective based data model.

5. The computer of claim 4 wherein said database extraction manager allows at least one of:

measurement data portions of said at least one database that are upward in cardinality from the user selected data table to be treated as dimension data; and

selected measurement data portions of said at least one database that are downward in cardinality from the user selected data table to be hidden.

6. The computer of claim 1 wherein said database extraction manager includes SQL generation software for constructing at least one SQL query corresponding to a node of the data structure representing the selected portion of the information stored in said at least one database.

7. The computer of claim 6 wherein said database extraction manager includes reporting software, said reporting software using said at least one SQL query to generate a report relating to the node of the data structure representing the selected portion of information stored in said at least one database.

8. The computer of claim 1 wherein said database extraction manager creates a plurality of nodes for the data structure representing the selected portion of the information stored in said at least one database, said database extraction manager including SQL generation software for constructing at least one SQL query for each node, each said SQL query corresponding to one of the nodes of the data structure representing the selected portion of the information stored in said at least one database.

9. The computer of claim 8 wherein said database extraction manager selectively provides joins for inclusion in said SQL queries.

10. A computer for implementing a database management system, said computer comprising:

a processor;

at least one database having a plurality of tables; and

a database extraction manager coupled to said processor and said at least one database, said database extraction manager capable of creating a perspective based data model from a user selected data table of said at least one database, said database extraction manager including a plurality of instructions for enabling said processor to execute the method of;

specifying a query by user selection of at least one data table of said at least one database;

creating a perspective based data model from the user selected data base table of said at least one database;

creating a query structure defining a plurality of paths providing a selected portion of the information in said at least one database, said selected portion being presented to the user based on the perspective based data model.

11. The computer of claim 10 wherein said database extraction manager includes a plurality of instructions for enabling said processor to execute the further method steps of:

creating a query structure defining a plurality of paths and having a plurality of nodes, each of the nodes having a corresponding operand;

performing a downward evaluation of the query structure, proceeding from a starting object down downwardly through said paths;

performing an upward evaluation of said query structure, proceeding upwardly from the bottom of each path; and

resolving the operands according to predetermined rules.

12. The computer of claim 10 wherein said database extraction manager includes a plurality of instructions for enabling said processor to execute the further method steps of:

determining the cardinality of each of the nodes during said downward evaluation and said upward evaluation.

13. The computer of claim 12 wherein said database extraction manager includes a plurality of instructions for resolving the operands according to predetermined rules wherein one of said predetermined rules includes one of:

allowing measurement data having an upward cardinality from the starting object to be resolved as dimension data; and

selectively allowing measurement data portions of said at least one database that are downward in cardinality from the user selected data table to be hidden.

14. The computer of claim 10 wherein said at least one database includes tables having both dimension and measurement data, and said database extraction includes a plurality of instructions for enabling said processor to execute the further method steps of presenting possible counting and aggregation for dimension data, and possible statistical calculations of the measurement data.

15. The computer of claim 14 wherein said database extraction manager includes a plurality of instructions for enabling said processor to execute the further method steps of excluding possible counting, aggregation, and statistical calculations based on error analysis of said perspective based data model.

16. The computer of claim 14 wherein said database extraction manager includes a plurality of instructions for enabling said processor to execute the further method steps of determining cardinal relationships between the user selected data table and other portions of the perspective based data model.

17. The computer of claim 16 wherein said database extraction manager includes a plurality of instructions for enabling said processor to execute the further method steps of allowing measurement data portions of said at least one database that are upward in cardinality from the user selected data table to be treated as dimension data;

and selectively allowing measurement data portions of said at least one database that are downward in cardinality from the user selected data table to be hidden.

18. The computer of claim 10 wherein said database extraction manager includes a plurality of instructions for enabling said processor to execute the further method steps of selectively providing joins for inclusion in said SQL queries.

19. A method of using a computer to implement a complex database management system having a query system, said method comprising the steps of:

creating a perspective based data model based on an initial selection of information from a database;

displaying a user query having a plurality of operands relating to selected portion of said database, said selected portion based on the perspective based data model;

creating a query structure defining a plurality of paths and having a plurality of nodes, each said node having a corresponding operand;

creating a database query for resolving the user query by performing a first evaluation of said query structure, proceeding from a starting node down downwardly through said paths, and performing a second evaluation of said query structure, proceeding upwardly from the bottom of each path.

20. The method of claim 19 further comprising the steps of:

creating a query structure defining a plurality of paths and having a plurality of nodes, each of the nodes having a corresponding operand;

performing a downward evaluation of the query structure, proceeding from a starting object down downwardly through said paths;

performing an upward evaluation of said query structure, proceeding upwardly from the bottom of each path; and

resolving the operands according to predetermined rules.

21. The method of claim 20 further comprising the steps of:

determining the cardinality of each of the nodes during said downward evaluation and said upward evaluation.

22. The method of claim 21 wherein the resolving step includes resolving the operands according to predetermined rules wherein one of said predetermined rules includes allowing measurement data having an upward cardinality from the starting object to be resolved as dimension data.

23. The method of claim 19 wherein the complex database system includes tables having both dimension and measurement data, and the method further includes the steps of presenting possible counting and aggregation for dimension data, and possible statistical calculations of the measurement data.

24. The method of claim 23 further comprising the steps of excluding possible counting, aggregation, and statistical calculations based on error analysis of said perspective based data model.

25. The method of claim 19 further comprising the steps of determining cardinal relationships between the user selected data table and other portions of the perspective based data model.

26. The method of claim 25 further comprising the steps of allowing measurement data portions of said at least one database that are upward in cardinality from the user selected data table to be treated as dimension data.

27. The method of claim 19 further comprising the steps of selectively providing joins for inclusion in the queries.

28. A machine-readable program storage device storing encoded instructions for a method of implementing a marketing management system, said method comprising the steps of:

creating a perspective based data model based on an initial selection of information from at least one database;

displaying a user query having a plurality of operands relating to selected portion of said at least on database, said selected portion based on the perspective based data model;

creating a query structure defining a plurality of paths and having a plurality of nodes, each said node having a corresponding operand;

creating a database query for resolving the user query by performing a first evaluation of said query structure, proceeding from a starting node down downwardly through said paths, and performing a second evaluation of said query structure, proceeding upwardly from the bottom of each path.

29. The machine-readable program storage device storing encoded instructions for a method of implementing a marketing management system of claim 28, said method comprising the further steps of:

creating a query structure defining a plurality of paths and having a plurality of nodes, each of the nodes having a corresponding operand;

performing a downward evaluation of the query structure, proceeding from a starting object down downwardly through said paths;

performing an upward evaluation of said query structure, proceeding upwardly from the bottom of each path; and

resolving the operands according to predetermined rules.

30. The machine-readable program storage device storing encoded instructions for a method of implementing a marketing management system of claim 29, said method comprising the further steps of:

determining the cardinality of each of the nodes during said downward evaluation and said upward evaluation.

31. The machine-readable program storage device storing encoded instructions for a method of implementing a marketing management system of claim 30, wherein the resolving step includes resolving the operands according to predetermined rules wherein one of said predetermined rules includes allowing measurement data having an upward cardinality from the starting object to be resolved as dimension data.

32. The machine-readable program storage device storing encoded instructions for a method of implementing a marketing management system of claim 28, wherein the complex database system includes tables having both dimension and measurement data, and the method further includes the steps of presenting possible counting and aggregation for dimension data, and possible statistical calculations of the measurement data.

33. The machine-readable program storage device storing encoded instructions for a method of implementing a marketing management system of claim 32, said method comprising the further steps of excluding possible counting, aggregation, and statistical calculations based on error analysis of said perspective based data model.

34. The machine-readable program storage device storing encoded instructions for a method of implementing a marketing management system of claim 28, said method comprising the further steps of:

determining cardinal relationships between the user selected data table and other portions of the perspective based data model.

35. The machine-readable program storage device storing encoded instructions for a method of implementing a marketing management system of claim 34, said method comprising the further steps of:

allowing measurement data portions of said at least one database that are upward in cardinality from the user selected data table to be treated as dimension data.

36. The machine-readable program storage device storing encoded instructions for a method of implementing a marketing management system of claim 28, said method comprising the further steps of:

selectively providing joins for inclusion in the queries.

37. A computer for performing operations on a complex database in a database management system, said computer comprising:

a processor;

at least one database containing information organized into multiple interdependent tables; and

a database extraction manager coupled to said processor and said at least one database, said database extraction manager capable of creating a perspective based data model from a user selected data table, said database extraction manager including software for displaying to a user a specified portion of the information stored in said at least one database based on the user selected table, said specified portion being presented to the user to allow the user to create a report based on user selected portions of said specified portion, said database extraction manager creating queries of said at least one database based on the user selection portions of said specified portion to create a report.

38. The computer of claim 37 wherein said at least one database includes tables having both dimension and measurement data, and said database extraction manager is capable of including in the report results for dimension data, and statistical calculations of the measurement data.

39. The computer of claim 38 wherein said database extraction manager includes software for excluding from user reports possible counting, aggregation, and statistical calculations based on the creation of said perspective based data model.

40. The computer of claim 38 wherein said database extraction manager determines cardinal relationships between the user selected data table and other portions of the perspective based data model.

41. The computer of claim 40 wherein said database extraction manager allows at least one of:

measurement data portions of said at least one database that are upward in cardinality from the user selected data table to be treated as dimension data;

and selected measurement data portions of said at least one database that are downward in cardinality from the user selected data table to be hidden.

42. The computer of claim 38 wherein said database extraction manager includes SQL generation software for constructing at least one SQL query corresponding to a node of the data structure representing the selected portion of the information stored in said at least one database so that the results of said at least one SQL query are included in the report.

43. The computer of claim 38 wherein said database extraction manager creates a plurality of nodes for the data structure representing the selected portion of the information stored in said at least one database, said database extraction manager including SQL generation software for constructing at least one SQL query for each node for inclusion in the report, each said SQL query corresponding to one of the nodes of the data structure representing the selected portion of the information stored in said at least one database.

44. The computer of claim 43 wherein said database extraction manager selectively provides joins for inclusion in said SQL queries.