Federated information management (FIM) system and method for providing data site filtering and translation for heterogeneous databases

A database controller for integrating data from a plurality of interconnected local databases to provide users with access to a virtual database, comprising:

• a plurality of local databases at different remote locations, said local databases including different subsets of data fields from a set of commonly used data fields, in which a particular data field in said set has heterogeneous local formats in said local databases;
• a network for interconnecting the remotely located local databases;
• a user interface for generating a global query to search the interconnected local databases using a single global schema that defines a virtual database, said global query including at least one data field from the set of commonly used data fields whose values are represented in an input format;
• a centralized database that interacts with the local databases over the network to retrieve data related to the value of the data field in the global query, comprising:
• a smart data dictionary (SDD) that contains configuration data for each of said local databases and said global schema, said configuration data including respective local formats for each of said commonly used data fields;
• a filter that enumerates the local databases that contain data related to the value of the data field in the global query to improve the efficiency of the search of the virtual database;
• an input translator that converts the value of said data field in said global query into local values in the respective local formats for the enumerated local databases to facilitate a complete search of the virtual database;
• a data information manager (DIM) that generates local queries including said data field's local values, for the enumerated local databases in response to said global query and in accordance with the respective configuration data, transmits the local queries to the enumerated local databases, and receives data in response to the local queries in their respective local formats;
• and
• an output translator that converts the data passed back by the local databases from their respective local formats into said input format, said DIM combining said data into am integrated response in said local format and transmitting the response back to the requesting user;
• and
• a plurality of local information managers (LIMs) at the respective local databases that interact with the centralized database in accordance with the global schema to execute the local queries to search for and retrieve from the respective local databases data that is associated with the local values of said data field, said LIMs passing the data back to the centralized database over the network where the data is translated into the input format and combined to present the requesting user with an integrated response.

A database controller for integrating data from a network of interconnected local databases to provide users with access to a virtual database, comprising:

• a smart data dictionary (SDD) containing data representing schema, data distribution, local site configuration and inter-site relationships of data among the local databases for each of said local databases and a single global schema that defines a virtual database, said local site configurations including respective local formats and a global format for each data field in a set of commonly used data fields, said SDD maintaining data consistency of the existing local databases and global schema as new local databases are added to the network;
• a user interface for selecting an i/o format from among said local and global formats and for generating a global query to search said virtual data base, said global query including at least one data field from said set of commonly used data fields whose values are represented in the selected i/o format;
• a global translator that converts the value of said data field in said global query into a global value in the global format;
• a global filter that enumerates the local databases that contain data related to the global value;
• a local translator that converts the global value into local values in their local formats for the enumerated local databases, respectively;
• a data information manager (DIM) that generates local queries, which include said local values, for the enumerated local databases in response to said global query and in accordance with the SDD data;
• a plurality of local information managers (LIMs) that execute the local queries to search for and retrieve from the enumerated local databases data that is associated with the local values of said data field, each of said LIMs being further adapted to generate, in accord with the data contained in said SDD, a data retrieval request for execution by another LIM and for receiving data from that local database;
• and
• an output translator that converts the retrieved data from the local formats into the selected i/o format and passes the data back to the DIM where it is combined to present the requesting user with an integrated response.

In a computer data network having a plurality of interconnected local databases with a plurality of users each capable of generating a global query for accessing and retrieving data from said databases in accord with a single query protocol, said global query including at least one data field from a set of commonly used data fields, a database controller for directing the transmission of the user generated global query to individual ones of the local databases and for receiving and integrating the requested data received from the databases into a single response and for transmitting the integrated single response to the requesting user, said database controller comprising:

• a central database comprising:
• a smart data dictionary (SDD) containing a database of data representing schema, data distribution, local site configuration and inter-site relationships of data among the local databases in the network for each database in the network that together define a single global schema for accessing a virtual database;
• a filter that enumerates the local databases having data responsive to the global query in accord with said data field and the data contained in the SDD;
• an input translator that converts the data field in the global query into local data fields compatible with the enumerated local databases in accord with the local site configuration in the SDD;
• and
• a data information manager (DIM) communicating both with said SDD to retrieve data therefrom, and with said user to receive said global query therefrom and to transmit responsive data thereto, for decomposing the global data query into a local-site execution plan that includes said local data fields for retrieval of data from the enumerated local databases, and for transmitting that portion of said local-site execution plan to be executed to the appropriate database for execution, and receiving data therefrom responsive to said local-site execution plan and said local data field;
• a plurality of local information managers (LIMs), each communicating with said DIM and said SDD, for controlling data flow to and from a specified database in the network in response to that portion of said local-site execution plan received from said DIM and for transmitting retrieved data responsive to that portion of said local-site execution plan to said DIM,
• each of said LIMs further adapted for generating, in accord with the data contained in said SDD, a data retrieval request for execution by another LIM and for receiving data therefrom in response thereto, in order to complete that portion of said local-site execution plan received by it for execution;
• and
• an output translator in said central database that converts the data received from each of the LIMs in accord with its local site configuration to a single site configuration, said DIM combining said data into an integrated single response and transmitting it back to the requesting user.

A method for integrating data for a plurality of interconnected local databases to provide users with access to a virtual database, comprising:

• a) providing meta-data information for each of a plurality of local databases and a virtual database in a central location, said meta-data information including respective local and global formats for each data field in a set of commonly used data fields;
• b) generating a global query to search said virtual database, said global query including at least one data field from said set of commonly used data fields whose values are represented in an i/o format;
• c) filtering said global query to enumerate those local databases that contain data related to said date field in accord with the meta-data information;
• d) translating the value of said date field in said global query into local values in the respective local formats in accord with the meta-data information for the enumerated local databases;
• e) generating local queries for the enumerated local databases that include the respective local values in accordance with the meta-data information;
• f) passing the local queries to the respective local databases;
• g) executing the local queries to search for and retrieve from the local databases data that is associated with the local values of said data field;
• h) translating the data retrieved from the local databases from their respective local formats into said i/o format;
• and
• i) integrating said data to present the requesting user with an integrated response.

A virtual database, comprising:

• a plurality of local databases at different remote locations, said local databases including data fields, in which a particular data field has heterogeneous local formats in said local databases and a particular value for a data field is stored in a subset of the local databases;
• a network for interconnecting the remotely located local databases;
• a user interface for generating a global query to search a virtual database, said global query including at least one data field from a set of commonly used data fields whose values are represented in an input format;
• a centralized database that interacts with and searches via the network the local databases that contain data related to the value of the data field in the global query, said database comprising:
• a smart data dictionary (SDD) containing meta-data including schema, data distribution, local site configuration and inter-site relationships of data among the local databases in the network for each local database and a single global schema that defines a virtual database, said local site configuration including respective local formats for each of said commonly used data fields, said SDD maintaining data consistency of the existing local databases and global schema.sub.-- as new local databases are added to the network;
• a filter that enumerates the local databases having data responsive to the global query in accord with the value of said data field and the meta-data contained in the SDD;
• an input translator that converts the value of said data field into local values in the respective local formats for the enumerated local databases in accord with the meta-data contained in the SDD;
• and
• a data information manager (DIM) that generates local queries, including said data field's local values, for the enumerated local databases in response to said global query and in accordance with the SDD data, transmits the local queries to the enumerated local databases, and receives data in response to the local queries in their respective local formats;
• and
• an output translator that converts the data passed back by the local databases from their respective local formats into said input format, said DIM combining said data into an integrated response in said input format and transmitting the response back to the requesting user;
• and
• a plurality of local information managers (LIMs) at the respective local databases that execute the local queries to search for and retrieve from the respective local databases data that is associated with the local values of said data field, each of said LIMs further adapted for generating, in accord with the data contained in said SDD, a data retrieval request for execution by another LIM and for receiving data therefrom in response thereto, to complete the execution of its local query said LIMs passing the data back to the centralized database over the network where the data is translated into the input format and combined to present the requesting user with an integrated response.