System and method for analyzing data sources to generate metadata

• US 20080140696A1
• Filed: 12/07/2006
• Published: 06/12/2008
• Est. Priority Date: 12/07/2006
• Status: Abandoned Application

• Current Assignee: Pantheon Systems Incorporated
• Original Assignee: Pantheon Systems Incorporated
• Inventors: Mathuria, Janak
• Application Number:
  US11/634,902
• Publication Number:
  US 20080140696A1
• Time in Patent Office: Days
• US Class Current: 707/102
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof

Abstract

A system and method are provided for generating metadata relating to an enterprise management system including at least one data source having one or more of tables and columns. Constraints existing on at least one of the tables and columns in the data source are inferred based on data in the tables and columns. Metadata that includes information on the inferred constraints is generated.

Claims

1. A method for generating metadata relating to at least one data source, the at least one data source including one or more tables, each table having one or more columns, said method comprising steps of:

inferring constraints existing on at least one of the tables and columns in the data source based on data in the tables and columns; and

generating metadata including information on the inferred constraints.

2. The method of claim 1 wherein said inferring step further includes generating at least one query statement and inferring constraints based upon a result of the at least one query statement.

3. The method of claim 1 further comprising a step of storing the metadata in a metadata repository.

4. The method of claim 1 wherein said inferring step further includes identifying at least one query statement for accessing the one or more tables and inferring constraints based on the at least one identified query statement.

5. The method of claim 4 wherein said inferring step further includes normalizing the at least one identified query statement and inferring constraints based on the at least one normalized query statement.

6. The method of claim 5 further comprising a step of identifying potential keys for each of the tables based on at least one of a normalized query statement, an alias, and an index.

7. The method of claim 6 further comprising a step of storing metadata on the potential keys in a metadata repository.

8. The method of claim 3 further comprising a step of generating one or more aliases for each column based on at least one of ontologies and normalized query statements for querying data from the at least one data source;

and wherein said storing step further includes storing the one or more aliases.

9. The method of claim 1 further comprising a step of calculating a cardinality of each of the tables and columns in the at least one data source;

and

wherein said inferring step further includes inferring constraints based on the calculated cardinalities.

10. The method of claim 1, wherein the constraints include at least one of NOT NULL constraints, unique key constraints, and referential integrity constraints.

11. The method of claim 4, wherein the constraints include at least one of NOT NULL constraints, unique key constraints, and referential integrity constraints.

12. The method of claim 1, further comprising:

a step of, for each inferred constraint, calculating a probability that the inference is valid;

a step of, for each inferred constraint, marking the constraint as valid in the metadata repository when the corresponding probability equals or exceeds an upper threshold; and

a step of, for each inferred constraint, marking the constraint as invalid in the metadata repository when the corresponding probability is less than a lower threshold.

13. A system for generating metadata for one or more data sources, said system comprising:

one or more computer program units configured to access one or more data sources and to generate metadata by inferring constraints based on data in tables and columns of the one or more data sources.

14. The system of claim 13 wherein said one or more computer program units are configured to generate at least one query statement and infer constraints based upon results of the at least one query statement.

15. The system of claim 13 further comprising a metadata repository;

wherein said one or more computer program units are further configured to store generated metadata in said metadata repository.

16. The system of claim 13 wherein said one or more computer program units are further configured to identify at least one query statement for accessing the identified tables and to infer constraints based on the at least one identified query statement.

17. The system of claim 16 wherein said one or more computer program units are further configured to normalize the at least one identified query statement and to infer constraints based on the at least one normalized query statement.

18. The system of claim 13 wherein said one or more computer program units are further configured to identify one or more aliases for tables and columns in each data source based on ontologies.

19. The system of claim 13 wherein said one or more computer program units are further configured to obtain aliases of tables and columns in each data source by parsing the name of said tables and columns into separate words and generating aliases by applying ontologies to the separate words.

20. The system of claim 13 wherein said one or more computer program units are further configured to confirm validity of inferred constraints from normalized structured query language (SQL) code used to query data from said data source.

21. The system of claim 17 wherein said one or more computer program units are further configured to identify potential keys for one or more of the tables based on at least one of the normalized query statements, indexes and aliases.

22. The system of claim 13 further comprising:

a client interface for inputting data relating to said one or more data sources, and wherein said one or more computer program units are further configured to analyze each data source to generate metadata further based on data inputted via the client interface.

23. The system of claim 13 wherein said metadata repository includes a database, and wherein said one or more computer program units are further configured to access said database to insert, modify, delete and query metadata stored therein.

24. The system of claim 15 wherein said one or more computer program units are further configured to extract table and column information from SQL code, and to store said extracted table and column information in said metadata repository.

25. The system of claim 13 wherein said one or more computer program units further include:

a computer program unit for generating SQL query code for determining cardinalities and data ranges for the tables and columns; and

a computer program unit for identifying potential relationships between the tables based on said cardinalities and data ranges.

26. The system of claim 13 wherein the constraints comprise at least one of NOT NULL constraints, unique key constraints, and referential integrity constraints.

27. The system of claim 17 wherein the constraints comprise at least one of NOT NULL constraints, unique key constraints, and referential integrity constraints.

28. The system of claim 15 wherein said one or more program units are configured to calculate a probability that an inference is valid, mark the constraint as valid in the metadata repository when the corresponding probability equals or exceeds an upper threshold, and mark the constraint as invalid in the metadata repository when the corresponding probability is less than a lower threshold.

29. A computer-readable medium storing computer-executable instructions for generating metadata relating to at least one data source having a one or more of tables, each table having one or more columns, by performing operations comprising:

inferring constraints existing on at least one of the tables and columns in the data source based on data in the tables and columns; and

generating metadata including information on the inferred constraints.

30. The computer-readable medium of claim 29 wherein said inferring operation further includes generating at least one query statement and inferring constraints based upon results of the at least one query statement.

31. The computer-readable medium of claim 29 comprising further computer-executable instructions for storing the metadata in a metadata repository.

32. The computer-readable medium of claim 29 comprising further computer-executable instructions for identifying at least one query statement for accessing the one or more of tables and inferring constraints based on the at least one identified query statement.

33. The computer-readable medium of claim 32 comprising further computer-executable instructions for normalizing the at least one identified query statement and inferring constraints based on the at least one normalized query statement.

34. The computer-readable medium of claim 33 comprising further computer-executable instructions for generating one or more aliases for each of the columns based on at least one of ontologies and normalized query statements for querying data from the database and storing the one or more aliases.

35. The computer-readable medium of claim 34 comprising further computer-executable instructions for identifying potential keys for each of the tables based on at least one of the one or more normalized query statements, indices and the one or more aliases.

36. The computer-readable medium of claim 35 comprising further computer-executable instructions for storing metadata on the potential keys in a metadata repository.

37. The computer-readable medium of claim 29 comprising further computer-executable instructions for calculating a cardinality of each of the tables and columns in the data source and

inferring constraints based on the calculated cardinalities.

38. The computer-readable medium of claim 29 wherein the constraints comprise at least one of NOT NULL constraints, unique key constraints, and referential integrity constraints.

39. The computer-readable medium of claim 33 wherein the constraints comprise at least one of NOT NULL constraints, unique key constraints, and referential integrity constraints.

40. The computer-readable medium of claim 31 comprising further computer-executable instructions for calculating a probability that each inferred constraint is valid;

marking each inferred constraint as valid in the metadata repository when the corresponding probability equals or exceeds an upper threshold; and

marking each inferred constraint as invalid in the metadata repository when the corresponding probability is less than a lower threshold.

41. A method for generating a metadata repository comprising metadata relating to one or more data sources, said method comprising steps of:

A. identifying a set of data sources to be analyzed from said data sources and connection information corresponding to each identified data source, and storing the identification of each data source and the corresponding connection information in a metadata repository;

B. for each database instance identified in step A, determining one or more tables of interest from each database instance along with column names for each table and storing the identified tables of interest along with the column names in the metadata repository;

C. for each table and column obtained in step B, determining a list of explicitly defined constraints for each table, and storing the list of explicitly defined constraints in the metadata repository;

D. converting column names obtained in step B to a user-friendly form by applying a function to each column name to generate aliases and storing the aliases in the metadata repository;

E. determining indices on each of the tables obtained in step B;

F. identifying view definitions including corresponding query statements for each of the data sources;

G. determining procedural code including corresponding query statements for each of the data sources;

H. obtaining a list of query statements that have been executed against each of the data sources;

I. normalizing each query statement identified in steps F through H to extract table and column information, and storing the table and column information in the metadata repository;

J. identifying potential keys for each table identified in step C based on the table and column information of at least one of steps E through I and storing the potential keys in the metadata repository; and

K. identifying sets of columns that are not known to be potential keys that have similar names to potential keys identified in step J and storing the sets of columns as additional potential keys in the metadata repository.