Method and system for federated querying of data sources

• US 8,412,746 B2
• Filed: 12/29/2004
• Issued: 04/02/2013
• Est. Priority Date: 05/25/2001
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Fox, Joshua, Meir, Rannen, Schreiber, Zvi
• Primary Examiner(s): Chen, Susan
• Application Number:
  US11/026,358
• Publication Number:
  US 20050234889A1
• Time in Patent Office: 3,016 Days
• Field of Search: None
• US Class Current: 707/803
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/256 in federated or virtual databases

Abstract

A system for managing and querying a plurality of data sources, including an interface to a plurality of inter-related data sources of diverse types, each of the plurality of data sources having a schema that describes its data structure, a schema generator communicating with the interface, for generating a federated schema that describes the structures of the plurality of data sources and their inter-relationships, and for modifying the federated schema over time as the plurality of data sources undergo changes, and a query generator communicating with the schema generator, for generating a query for the federated schema. A method and a computer-readable storage medium are also described.

Claims

1. A system, implemented in a data processing system, for managing and querying a plurality of inter-related data sources, the system comprising:

an interface to the plurality of inter-related data sources of diverse types, wherein each of the plurality of inter-related data sources has a corresponding schema that describes a corresponding data structure of a corresponding data source;

a schema generator communicating with said interface, wherein the schema generator generates a federated schema that describes structures of the plurality of inter-related data sources and inter-relationships of the plurality of inter-related data sources, and wherein the schema generator further modifies the federated schema over time as the plurality of inter-related data sources undergo changes;

a query generator communicating with said schema generator, wherein the query generator generates a query for the federated schema; and

a storage device in communication with the data processing system, the storage device operable to store the query.

2. The system of claim 1 further comprising a query engine communicating with said interface, wherein the query engine executes the query to the federated schema and for returning query results.

3. The system of claim 1 wherein said query engine is a component of a business process management environment.

4. The system of claim 1 further comprising a schema-to-ontology generator communicating with said interface and with said schema generator, wherein the schema-to-ontology generator maps the plurality of inter-related data sources into a common ontology model using semantic mappings, and wherein said query generator generates the query formulated in terms of nomenclature of the common ontology model.

5. The system of claim 4 further comprising a view generator communicating with the schema-to-ontology generator, wherein the view generator generates a database view of the plurality of inter-related data sources, using the federated schema, wherein the database view is formulated in terms of nomenclature of the common ontology model, and wherein said query generator generates the query for the database view.

6. The system of claim 5 wherein the database view is formulated using SQL statements.

7. The system of claim 5 wherein the database view is formulated using XQuery statements.

8. The system of claim 5 wherein said view generator updates the database view when the common ontology model is changed.

9. The system of claim 4 further comprising a portal communicating with the schema-to-ontology generator, wherein the portal manages user access privileges to the plurality of inter-related data sources, and wherein the user access privileges are expressed in terms of nomenclature of the common ontology model.

10. The system of claim 4 further comprising a data thesaurus communicating with the schema-to-ontology generator, wherein the data thesaurus looks-up a meaning of a data construct within the plurality of inter-related data sources, wherein the meaning is relative to the common ontology model.

11. The system of claim 10 wherein said data thesaurus traces an origin of a data construct within the plurality of inter-related data sources.

12. The system of claim 10 wherein said data thesaurus traces query results back to data sources.

13. The system of claim 4 wherein said query generator comprises a web-based user interface for creating and editing queries while browsing the common ontology model.

14. The system of claim 4 wherein the common ontology model includes classes, properties of classes, inheritances among classes, and business rules relating properties of classes, and wherein said query generator formulates a semantic query from an expression language that uses the classes, the properties, the inheritances, and the business rules.

15. The system of claim 14 further comprising a view generator communicating with the schema-to-ontology generator, wherein the view generator generates a database view of the plurality of inter-related data sources, using the federated schema, wherein the database view is formulated in terms an expression language that uses the classes, the properties, the inheritances, and the business rules, and wherein said query generator generates a query for the database view.

16. The system of claim 15 wherein said view generator automatically embeds the business rules within view definitions.

17. The system of claim 15 wherein said view generator updates the database view when a relevant business rule is changed.

18. The system of claim 4 wherein the common ontology model provides common semantics for referencing data assets, and wherein the common ontology model encapsulates constructs from the plurality of inter-related schemas, and wherein the constructs comprise:

database tables;

corresponding fields of the database tables;

interrelationships of the database tables through foreign keys;

XML complex types;

elements of the XML complex types;

type inter-relationships of the XML complex types; and

business rules that relate table fields to one another and XML elements to one another.

19. The system of claim 1 wherein the plurality of inter-related data sources of diverse types include at least one Microsoft SQL database and one XML document data source.

20. The system of claim 1 wherein the plurality of inter-related data sources of diverse types include at least one Microsoft SQL database and one IBM DB2 database.

21. The system of claim 1 wherein the plurality of inter-related data sources of diverse types include at least one XML document data source and one IBM DB2 database.

22. The system of claim 1 wherein said schema generator modifies the federated schema such that at least one of the plurality of inter-related data sources appears to be removed.

23. The system of claim 1 wherein said schema generator modifies the federated schema when a new data source is added to the plurality of inter-related data sources.

24. The system of claim 23 wherein said schema generator automatically detects and scans new data sources.

25. The system of claim 1 wherein said schema generator modifies the federated schema when one of the plurality of inter-related data sources is modified.

26. The system of claim 1 wherein the federated schema is an ontology model, and wherein each corresponding schema of the plurality of inter-related data sources is mapped to the ontology model.

27. A method, implemented in a data processing system, for managing and querying a plurality of inter-related data sources, the method comprising:

generating a federated schema that describes structures of the plurality of inter-related data sources and inter-relationships of the plurality of inter-related data sources;

modifying the federated schema over time as the plurality of inter-related data sources undergo changes, wherein each of the plurality of inter-related data sources has a corresponding schema that describes a corresponding data structure of a corresponding data source;

generating a query for the federated schema; and

storing the federated schema.

28. The method of claim 27 further comprising:

executing a query to the federated schema; and

returning query results.

29. The method of claim 28 wherein said executing is performed by a software application.

30. The method of claim 29 wherein said executing is performed by a business intelligence tool.

31. The method of claim 29 wherein said executing is performed within a business process management environment.

32. The method of claim 27 further comprising mapping the plurality of inter-related data sources into a common ontology model using semantic mappings, and wherein generating the query comprises formulating the query in terms of nomenclature of the common ontology model.

33. The method of claim 32 further comprising generating a database view of the plurality of inter-related data sources, using the federated schema, wherein the database view is formulated in terms of nomenclature of the common ontology model, and wherein generating the query comprises generating the query for the database view.

34. The method of claim 33 wherein the database view is formulated using SQL statements.

35. The method of claim 33 wherein the database view is formulated using XQuery statements.

36. The method of claim 33 wherein generating the database view comprises updating the database view when the common ontology model is changed.

37. The method of claim 32 further comprising managing user access privileges to the plurality of inter-related data sources, wherein user access privileges are expressed in terms of nomenclature of the common ontology model.

38. The method of claim 32 further comprising looking up a meaning of a data construct within the plurality of inter-related data sources, wherein the meaning is relative to the common ontology model.

39. The method of claim 38 wherein looking up comprises tracing an origin of a data construct within the plurality of inter-related data sources.

40. The method of claim 38 wherein looking up comprises tracing query results back to at least one data source.

41. The method of claim 32 wherein generating the query comprises employing a web-based user interface for creating and editing queries while browsing the common ontology model.

42. The method of claim 32 wherein the common ontology model includes classes, properties of classes, inheritances among classes, and business rules relating properties of classes, and wherein generating the query comprises formulating the query from an expression language that uses the classes, the properties, the inheritances, and the business rules.

43. The method of claim 42 further comprising generating a database view of the plurality of inter-related data sources, using the federated schema, wherein the database view is formulated in terms an expression language that uses the classes, the properties, the inheritances, and the business rules, and wherein generating the query comprises generating the query for the data base view.

44. The method of claim 43 wherein generating the database view comprises automatically embedding the business rules within view definitions.

45. The method of claim 43 wherein generating the database view comprises updating the database view when a relevant business rule is changed.

46. The method of claim 27 wherein the plurality of inter-related data sources of diverse types include at least one Microsoft SQL database and one XML document data source.

47. The method of claim 27 wherein the plurality of inter-related data sources of diverse types include at least one Microsoft SQL database and one IBM DB2 database.

48. The method of claim 27 wherein the plurality of inter-related data sources of diverse types include at least one XML document data source and one IBM DB2 database.

49. The method of claim 27 wherein generating the federated schema comprises modifying the federated schema such that at least one of the plurality of inter-related data sources appears to be removed.

50. The method of claim 27 wherein generating the federated schema comprises modifying the federated schema when a new data source is added to the plurality of inter-related data sources.

51. The method of claim 50 further comprising automatically detecting and scanning new data sources.

52. The method of claim 27 wherein generating the federated schema comprises modifying the federated schema when one of the plurality of inter-related data sources is modified.

53. A computer-readable storage medium storing program code for causing a computer to perform the steps of:

generating a federated schema that describes structures of the plurality of inter-related data sources and inter-relationships of the plurality of inter-related data sources;

modifying the federated schema over time as the plurality of inter-related data sources undergo changes, wherein each of the plurality of inter-related data sources has a corresponding schema that describes a corresponding data structure of a corresponding data source; and

generating a query for the federated schema.