System and method for optimizing federated and ETL'd databases having multidimensionally constrained data

• US 20070276851A1
• Filed: 05/02/2006
• Published: 11/29/2007
• Est. Priority Date: 05/02/2006
• Status: Active Grant

• Current Assignee: Kyndryl Incorporated (Kyndryl Holdings, Inc.)
• Original Assignee: International Business Machines Corporation
• Inventors: Hennessy, Richard, Friedlander, Robert, Kraemer, James
• Granted Patent: US 7,523,118 B2
• Time in Patent Office: Days
• 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: G06F 16/907 Retrieval characterised by using metadata, e.g. metadata not derived from the content or metadata generated manually
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

Method, system, and program product for configuring and using a federated database management system. The design of the metadata and queries includes the steps of first determining schema and metadata configurations of the data source servers. The next step is enumerating available resources, and enumerating security and confidentiality requirements. These are used for calculating an optimal federated database management system design based on the schema and metadata, the enumerated available resources, and the enumerated security and confidentiality requirements; and designing an optimal federated database management system.

Claims

1. A method for optimizing a federated database management system having a federated database server and a plurality of data source servers comprising the steps of:

a) determining schema and metadata configurations of the data source servers;

b) enumerating available resources;

c) enumerating security and confidentiality requirements;

d) calculating an optimal federated database management system design based on the schema and metadata, the enumerated available resources, and the enumerated security and confidentiality requirements; and

e) designing an optimal federated database management system; and

f) providing functionality for user input including user heuristics and data inputs.

2. The method of claim 1 wherein available resources include people, skill sets, network capability, computing equipment, budget, time constraints, software systems, database management system application program interfaces, and storage.

3. The method of claim 1 wherein the security and confidentiality requirements include medical records confidentiality.

4. The method of claim 1 comprising designing the optimal federated database management system using one or more of an entity unification scheme, conformal dimensions, an aggregation scheme, unified metadata, and a unified data model.

5. A method of submitting a query to a federated database management system and obtaining an optimized output therefrom, comprising the steps of:

a) submitting a query to an application associated to a federated server;

b) optimizing the query in the federated server;

c) decomposing the query into fragments for execution at individual data sources;

d) invoking wrappers to execute the fragments;

e) returning streams of data to the federated server;

f) combining returning streams, and performs additional processing not accomplished by a data source; and

g) returning a final result to the application associated to the federated server.

6. The method of claim 5 wherein said final result is a request for user interaction.

7. The method of claim 5 wherein said final response is data.

8. The method of claim 5 comprising executing the fragments by the step of the wrapper performing data source operations to carry out specified function calls and queries, where queries are submitted to the data source in its native query language.

9. The method of claim 5 wherein execution at individual data sources comprises extracting, transfer, and loading data from a data server.

10. The method of claim 9 wherein execution at individual data sources comprises extracting, transfer, and loading data from a data server in real time on the fly without materialization thereof.

11. A program product comprising a storage medium containing computer readable program code for directing a computer system to carry out the steps of:

a) submitting a query to an application associated to a federated server;

b) optimizing the query in the federated server;

c) decomposing the query into fragments for execution at individual data sources;

d) invoking wrappers to execute the fragments;

e) returning streams of data to the federated server;

f) combining returning streams, and performs additional processing not accomplished by a data source; and

g) returning a final result to the application associated to the federated server.

12. A program product comprising a storage medium containing computer readable program code for directing a computer system to carry out the steps of:

a) determining schema and metadata configurations of the data source servers;

b) enumerating available resources;

c) enumerating security and confidentiality requirements;

d) calculating an optimal federated database management system design based on the schema and metadata, the enumerated available resources, and the enumerated security and confidentiality requirements; and

e) designing an optimal federated database management system; and

f) providing functionality for user input including user heuristics and data inputs.

13. The program product of claim 12 wherein available resources include people, skill sets, network capability, computing equipment, budget, time constraints, software systems, database management system application program interfaces, and storage.

14. The program product of claim 12 wherein the security and confidentiality requirements include medical records confidentiality.

15. The program product of claim 12 comprising designing the optimal federated database management system using one or more of an entity unification scheme, conformal dimensions, an aggregation scheme, unified metadata, and a unified data model.

16. A program product comprising a storage medium containing computer readable program code for directing a computer system to carry out the steps of

a) submitting a query to an application associated to a federated server;

b) optimizing the query in the federated server;

c) decomposing the query into fragments for execution at individual data sources;

d) invoking wrappers to execute the fragments;

e) returning streams of data to the federated server,

f) combining returning streams, and performs additional processing not accomplished by a data source; and

g) returning a final result to the application associated to the federated server.

17. The program product of claim 16 wherein said final result is a request for user interaction.

18. The program product of claim 16 wherein said final response is data.

19. The program product of claim 16 comprising executing the fragments by the step of the wrapper performing data source operations to carry out specified function calls and queries, where queries are submitted to the data source in its native query language.

20. The program product of claim 16 wherein execution at individual data sources comprises extracting, transfer, and loading data from a data server.

21. The program product of claim 20 wherein execution at individual data sources comprises extracting, transfer, and loading data from a data server in real time on the fly without materialization thereof.