System and method for automatically building an OLAP model in a relational database

• US 7,716,167 B2
• Filed: 12/18/2002
• Issued: 05/11/2010
• Est. Priority Date: 12/18/2002
• Status: Active Grant

• Current Assignee: Green Market Square Limited
• Original Assignee: International Business Machines Corporation
• Inventors: DeKimpe, Daniel Martin, Colossi, Nathan Gevaerd
• Primary Examiner(s): PALADINI, ALBERT WILLIAM
• Application Number:
  US10/325,245
• Publication Number:
  US 20040122646A1
• Time in Patent Office: 2,701 Days
• Field of Search: 707/102, 707/100, 707 1- 4, 707/103.R
• US Class Current: 707/714
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A system automatically generates OLAP metadata objects for a multidimensional analysis program such as OLAP from SQL statements without involving the database administrator, and eliminates the need for database administrators to manually perform this analysis and design. The system deconstructs a set of SQL statements into tables and aggregates metrics for measures and joins. It recognizes that the relational database structure contains both dimension tables and fact tables. Over many SQL statements, fact tables will have a large measure metric while dimension tables will have a low measure metric. Tables are linked based on large join metrics; small join metrics are ignored. The present system builds the OLAP cube model from the fact tables, dimension tables, and joins. The analysis of the SQL statements allows the system to map the hierarchies within the dimension table, and provides the system with information about attribute relationships and cubes.

Claims

1. A method for automatically building metadata objects for use by a multidimensional analysis program from a set of query statements, comprising:

analyzing the query statements to obtain usage metrics;

generating metadata of the multidimensional analysis program from the query statements by evaluating the usage metrics to select candidate metadata objects and defining one or more metadata objects from the candidate metadata objects; and

populating the metadata of the multidimensional analysis program by creating the one or more defined metadata objects.

2. The method of claim 1, wherein the multidimensional analysis program comprises an on-line analytical processing, OLAP, program;

and

wherein the query statements comprise SQL statements.

3. The method of claim 2, wherein the OLAP product comprises a relational OLAP, ROLAP, program.

4. The method of claim 2, wherein the OLAP product comprises a hybrid OLAP, HOLAP, program.

5. The method of claim 2, wherein the OLAP product comprises a multidimensional OLAP, MOLAP, program.

6. The method of claim 2, further comprising acquiring the SQL statements from an SQL statement source.

7. The method of claim 2, wherein analyzing the SQL statements comprises analyzing at least one of the SQL statements for references to tables, expressions, joins, predicates, groupings, havings, and ordering.

8. The method of claim 7, further comprising building an object configuration of cube models.

9. The method of claim 8, wherein building the object configuration of cube models comprises building at least one of facts, dimensions, hierarchies, joins, attributes, measures, attribute relationships, cubes, cube facts, cube dimensions and cube hierarchies.

10. The method of claim 2, wherein generating metadata comprises selectively filtering the metadata based on heuristics.

11. A computer program product having instruction codes for automatically building metadata objects for use by a multidimensional analysis program from a set of query statements, comprising:

a first set of instruction codes for analyzing the query statements to obtain usage metrics;

a second set of instruction codes for generating metadata of the multidimensional analysis program from the query statements by evaluating the usage metrics to select candidate metadata objects and defining one or more metadata objects from the candidate metadata objects; and

a third set of instruction codes for populating the metadata of the multidimensional analysis program by creating the one or more defined metadata objects.

12. The computer program product of claim 11, wherein the multidimensional analysis program comprises an on-line analytical processing, OLAP, program;

and

wherein the query statements comprise SQL statements.

13. The computer program product of claim 12, wherein the OLAP product comprises a relational OLAP, ROLAP, program.

14. The computer program product of claim 12, wherein the OLAP product comprises a hybrid OLAP, HOLAP, program.

15. The computer program product of claim 12, wherein the OLAP product comprises a multidimensional OLAP, MOLAP, program.

16. The computer program product of claim 12, further comprising a fourth set of instruction codes for acquiring the SQL statements from an SQL statement source.

17. The computer program product of claim 12, wherein the first set of instruction codes analyzes at least one of the SQL statements for references to tables, expressions, joins, predicates, groupings, havings, and ordering.

18. The computer program product of claim 17, further comprising a fifth set of instruction codes for building an object configuration of cube models.

19. The computer program product of claim 18, wherein the fifth set of instruction codes builds at least one of facts, dimensions, hierarchies, joins, attributes, measures, attribute relationships, cubes, cube facts, cube dimensions and cube hierarchies.

20. The computer program product of claim 12, wherein the second set of instruction codes selectively filters the metadata based on heuristics.

21. A system for automatically building metadata objects for use by a multidimensional analysis program from a set of query statements, comprising:

means for analyzing the query statements to obtain usage metrics;

means for generating metadata of the multidimensional analysis program from the query statements by evaluating the usage metrics to select candidate metadata objects and defining one or more metadata objects from the candidate metadata objects; and

means for populating the metadata of the multidimensional analysis program by creating the one or more defined metadata objects.

22. The system of claim 21, wherein the multidimensional analysis program comprises an on-line analytical processing, OLAP, program;

and

wherein the query statements comprise SQL statements.

23. The system of claim 22, wherein the OLAP product comprises a relational OLAP, ROLAP, program.

24. The system of claim 22, wherein the OLAP product comprises a hybrid OLAP, HOLAP, program.

25. The system of claim 22, wherein the OLAP product comprises a multidimensional OLAP, MOLAP, program.

26. The system of claim 22, further comprising means for acquiring the SQL statements from an SQL statement source.

27. The system of claim 22, wherein the means for analyzing the SQL statements analyzes at least one of the SQL statements for references to tables, expressions, joins, predicates, groupings, havings, and ordering.

28. The system of claim 27, further comprising means for building an object configuration of cube models.

29. The system of claim 28, wherein the means for building the object configuration of cube models builds at least one of facts, dimensions, hierarchies, joins, attributes, measures, attribute relationships, cubes, cube facts, cube dimensions and cube hierarchies.

30. The system of claim 22, wherein the means for generating metadata of the multidimensional analysis program from the SQL statements selectively filters the metadata based on heuristics.