Systems, methods, and computer program products that automatically discover metadata objects and generate multidimensional models

• US 7,707,143 B2
• Filed: 06/14/2004
• Issued: 04/27/2010
• Est. Priority Date: 06/14/2004
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bruce, Jay M., Coates, Marlene L.
• Primary Examiner(s): Jalil; Neveen Abel
• Assistant Examiner(s): Pulliam; Christyann R
• Application Number:
  US10/867,970
• Publication Number:
  US 20050278290A1
• Time in Patent Office: 2,143 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

Systems, methods, and computer products that include an automated discovery process that discovers useful metadata objects from an intelligent starting point thereby generating at least one multidimensional model for OLAP analysis. Further, generation of the intelligent starting point may be derived by use of a multidimensional analysis program that analyzes the results of query mining and query analysis. The preferred embodiment of the present invention determines whether metadata useful for OLAP analysis exists by evaluating patterns found in the queries. In addition to using the starting point derived from the results of query mining and query analysis, the preferred embodiment of the present invention may also limit search parameters to narrow the scope of searching for an intelligent starting point and thereby both increase the probability of producing an accurate cube multidimensional model and increase the efficiency of determining the intelligent starting point.

Claims

1. A computer-implemented method to generate a multidimensional model, said computer including a relational database and at least one metadata object, said method comprising:

receiving, with a computer system including a processor, results of query mining and query analysis from a query management facility;

analyzing, with said computer system, said results, including at least one query object that comprises a query statement and that queries said relational database, to provide one or more starting points to discover metadata objects, wherein each of said one ore more starting points is one of a table in the relational database and a query object, wherein said analyzing includes mining said at least one query object;

in response to input from a user, selecting, with said computer system, a starting point from said one or more starting points;

discovering, with said computer system, said at least one metadata object from said starting point to a maximum depth level by making recommendations on combinations of tables, columns, and joins of said relational database that should be defined as dimension objects or fact objects, wherein the maximum depth level is provided by said user and is defined by a join condition, wherein discovering recommendations as to additional dimension objects or fact objects is terminated when the maximum depth level is reached, wherein said discovering begins with tables referenced by a query object when a starting point is said query object, wherein said discovering begins with a table when said starting point is said table, wherein said discovering includes analyzing statistical information about said starting point and forming said recommendations as to said discovered at least one metadata object from which a multidimensional model is to be created, wherein said discovered at least one metadata object includes at least one fact object, at least one dimension object, and at least one join object, wherein the discovering is a first pass, and further comprising;

determining that the discovering did not discover meaningful dimension objects and fact objects after the maximum depth level has been reached;

selecting a new said starting point based on said at least one analyzed query object by selecting said new starting point having a highest rating from said one or more other starting points; and

performing, with said computer system, a second pass of discovering using said new starting point, wherein previously followed leads, including tables that did not produce metadata in the first pass, are ignored, wherein when no meaningful dimension objects and fact objects are found in the second pass, it is determined that no meaningful dimension objects and fact objects exist based upon data access patterns described by user query patterns and user mining patterns as described in the query objects; and

generating, with said computer system, said multidimensional model from said at least one discovered metadata object, wherein said multidimensional model is represented on a screen to the user, wherein selection of the multidimensional model by the user displays metadata objects making up the multidimensional model, wherein said multidimensional model comprises an On-Line Analytical Processing (OLAP) cube multidimensional model.

2. The computer-implemented method of claim 1, wherein said at least one analyzed query object includes said at least one mined query object.

3. The computer-implemented method of claim 1, further comprising:

receiving input from the user identifying one or more filters, wherein said analyzing includes filtering said at least one analyzed query object using the one or more filters to limit information accessed in said at least one analyzed query object.

4. The computer-implemented method of claim 3, wherein said filtering includes matching patterns associated with said at least one analyzed query object.

5. The computer-implemented method of claim 1, wherein said starting point comprises said table associated with said relational database.

6. The computer-implemented method of claim 1, wherein said selecting the starting point further comprises accessing said table from said relational database.

7. The computer-implemented method of claim 1, further comprising determining said maximum depth level from said at least one analyzed query object to limit said discovery of at least one metadata object to said maximum depth level.

8. The computer-implemented method of claim 1, wherein said generating multidimensional model comprises generating at least two said generated multidimensional models, further comprising:

ordering said at least two generated multidimensional models; and

presenting said ordered multidimensional models to a user.

9. A computer system to generate a multidimensional model including a relational database and at least one metadata object, the computer system comprising:

a processor;

memory;

results of query mining and query analysis received, using said processor, from a query management facility;

at least one query object that comprises a query statement and that queries said relational database to provide one or more starting points to discover metadata objects, wherein each of said one or more starting points is one of a table in the relational database and a query object, wherein said results, including at least one query object, are analyzed, and wherein said analyzing includes mining said at least one query object;

a starting point that is selected from said one or more starting points in response to input from a user;

said at least one metadata object that is discovered from said starting point to a maximum depth level by making recommendations on combinations of tables, columns, and joins of said relational database that should be defined as dimension objects or fact objects, wherein the maximum depth level is provided by said user and is defined by a join condition, wherein discovering recommendations as to additional dimension objects or fact objects is terminated when the maximum depth level is reached, wherein said discovering begins with tables referenced by a query object when said starting point is said query object, wherein said discovering begins with a table when said starting point is said table, wherein said discovering includes analyzing statistical information about said starting point and forming said recommendations as to said discovered at least one metadata object from which a multidimensional model is to be created, wherein said discovered at least one metadata object includes at least one fact object, at least one dimension object, and at least one join object, wherein the discovering is a first pass, and further comprising;

determining that the discovering did not discover meaningful dimension objects and fact objects after the maximum depth level has been reached;

selecting a new said starting point based on said at least one analyzed query object by selecting said new starting point having a highest rating from said one or more other starting points; and

performing a second pass of discovering using said new starting point, wherein previously followed leads, including tables that did not produce metadata in the first pass, are ignored, wherein when no meaningful dimension objects and fact objects are found with the second pass, it is determined that no meaningful dimension objects and fact objects exist based upon data access patterns described by user query patterns and user mining patterns as described in the query objects; and

generating said multidimensional model from said at least one discovered metadata object, wherein said multidimensional model is represented on a screen to the user, wherein selection of the multidimensional model by the user displays metadata objects making up the multidimensional model, wherein said multidimensional model comprises an On-Line Analytical Processing (OLAP) cube multidimensional model.

10. The computer system of claim 9, wherein said at least one analyzed query object includes said at least one mined query object.

11. The computer system of claim 9, further comprising:

one or more filters received from input from the user, wherein said analyzing includes filtering said at least one analyzed query object using the one or more filters to limit information accessed in said at least one analyzed query object.

12. The computer system of claim 11, wherein said at least one filtered query object is obtained by matching patterns associated with said at least one analyzed query object.

13. The computer system of claim 9, wherein said starting point comprises said table associated with said relational database.

14. The computer system of claim 9, wherein said selecting the starting point further comprises accessing said table from said relational database

15. The computer system of claim 9, further comprising said maximum depth level that is determined from said at least one analyzed query object to limit said discovery of at least one metadata object to said maximum depth level.

16. The computer system of claim 9, wherein said generating multidimensional model comprises generating at least two said generated multidimensional models, further comprising:

ordering said at least two generated multidimensional models; and

presenting said ordered multidimensional models to a user.

17. An article of manufacture comprising a computer program usable storage medium embodying one or more instructions executable by a processor of a computer system to generate a multidimensional model, said computer system including a relational database and at least one metadata object, wherein the one or more instructions when executed on said computer system perform:

receiving, with a computer including a processor, results of query mining and query analysis from a query management facility;

analyzing said results, including at least one query object that comprises a query statement and that queries said relational database, to provide one or more starting points to discover metadata objects, wherein each of said one or more starting points is one of a table in the relational database and a query object, wherein said analyzing includes mining said at least one query object;

in response to input from a user, selecting a starting point from said one or more starting points;

discovering said at least one metadata object from said starting point to a maximum depth level by making recommendations on combinations of tables, columns, and joins of said relational database that should be defined as dimension objects or fact objects, wherein the maximum depth level is provided by said user and is defined by a join condition, wherein discovering recommendations as to additional dimension objects or fact objects is terminated when the maximum depth level is reached, wherein said discovering begins with tables referenced by a query object when said starting point is said query object, wherein said discovering begins with a table when said starting point is said table, wherein said discovering includes analyzing statistical information about said starting point and forming said recommendations as to said discovered at least one metadata object from which a multidimensional model is to be created, wherein said discovered at least one metadata object includes at least one fact object, at least one dimension object, and at least one join object, wherein the discovering is a first pass, and further comprising;

determining that the discovering did not discover meaningful dimension objects and fact objects after the maximum depth level has been reached;

selecting a new said starting point based on said at least one analyzed query object by selecting said new starting point having a highest rating from said one or more other starting points; and

performing a second pass of discovering using said new starting point, wherein previously followed leads, including tables that did not produce metadata in the first pass, are ignored, wherein when no meaningful dimension objects and fact objects are found with the second pass, it is determined that no meaningful dimension objects and fact objects exist based upon data access patterns described by user query patterns and user mining patterns as described in the query objects; and

generating said multidimensional model from said at least one discovered metadata object, wherein said multidimensional model is represented on a screen to the user, wherein selection of the multidimensional model by the user displays metadata objects making up the multidimensional model, wherein said multidimensional model comprises an On-Line Analytical Processing (OLAP) cube multidimensional model.

18. The article of manufacture of claim 17, wherein said at least one analyzed query object includes said at least one mined query object.

19. The article of manufacture of claim 17, wherein the one or more instructions when executed on said computer perform:

receiving input from the user identifying one or more filters, wherein said analyzing includes filtering said at least one analyzed query object using the one or more filters to limit information accessed in said at least one analyzed query object.

20. The article of manufacture of claim 19, wherein the one or more instructions when executed on said computer perform:

matching patterns associated with said at least one analyzed query object.

21. The article of manufacture of claim 17, wherein said starting point comprises said table associated with said relational database.

22. The article of manufacture of claim 17, wherein said selecting the starting point further comprises accessing said table from said relational database.

23. The article of manufacture of claim 17, wherein the one or more instructions when executed on said computer perform:

determining said maximum depth level from said at least one analyzed query object to limit said discovery of at least one metadata object to said maximum depth level.

24. The article of manufacture of claim 17, wherein said generating multidimensional model comprises generating at least two said generated multidimensional models, and wherein the one or more instructions when executed on said computer perform:

ordering said at least two generated multidimensional models; and

presenting said ordered multidimensional models to a user.