METHOD AND APPARATUS FOR CONVERTING HETEROGENEOUS DATABASES INTO STANDARDIZED HOMOGENEOUS DATABASES

• US 20110231454A1
• Filed: 06/03/2011
• Published: 09/22/2011
• Est. Priority Date: 07/10/2009
• Status: Active Grant

• Current Assignee: Robert Mack
• Original Assignee: Robert Mack
• Inventors: Mack, Robert
• Granted Patent: US 8,554,801 B2
• Time in Patent Office: Days
• US Class Current: 707/803
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database

Abstract

A method, an apparatus, and a system for configuring, designing, and/or implementing database tables are detailed that provides a framework into which a remainder of database tables is developed. Also detailed is a method to develop this framework of database tables. This so developed framework provides a platform for converting multiple independent heterogeneous databases into standardized homogeneous databases.

Claims

1. A method comprising

accessing a first repository of metadata in a computer memory;

using the first repository of metadata to set up and store a first entity-relationship diagram in a first data model in a computer memory prior to storing data values in a first database in a computer memory, wherein the first entity-relationship diagram includes a first set of data attributes stored in computer memory;

using a computer processor to interpret the first entity-relationship diagram in the first data model in order to form the first database; and

storing a first plurality of data records in a first database table, each including a row of a plurality of data values, in the first database;

accessing a second repository of metadata in a computer memory, wherein the second repository of metadata and the first repository of metadata are substantially different;

using the second repository of metadata to set up and store a second entity-relationship diagram in a second data model in a computer memory prior to storing data values in a second database in a computer memory, wherein the second entity-relationship diagram includes a second set of data attributes;

using a computer processor to interpret the second entity-relationship diagram in the second data model in order to form the second database; and

storing a second plurality of data records in a second database table, each including a row of a plurality of data values, in the second database;

adding a first data entity and a first set of data entity relationships to the first entity-relationship diagram and storing in a computer memory;

wherein the first data entity is added to the first entity-relationship diagram as an ultimate parent data entity of the first data model;

wherein the first set of data entity relationships connect the first data entity to the first entity-relationship diagram;

adding the first data entity and a second set of data entity relationships to the second entity-relationship diagram and storing in a computer memory;

wherein the first data entity is added to the second entity-relationship diagram as an ultimate parent data entity of the second data model;

wherein the second set of data entity relationships connect the first data entity to the second entity-relationship diagram;

wherein an ultimate parent data entity of the first data model does not inherit any foreign key data attributes from any other data entity of the first data model, wherein a foreign key data attribute is a named data field which is used to identify a column of data values after a database has been formed and wherein a foreign key data attribute is derived from data attributes that form a unique key of a data entity; and

wherein an ultimate parent data entity of the second data model does not inherit any foreign key data attributes from any other data entity of the second data model, wherein a foreign key data attribute is a named data field which is used to identify a column of data values after a database has been formed and wherein a foreign key data attribute is derived from data attributes that form unique key of a data entity;

wherein the first data entity is a component of the first entity-relationship diagram which becomes a third database table in the first database, once the first database is instantiated from the first entity-relationship diagram; and

wherein the first data entity is a component of the second entity-relationship diagram which becomes a fourth database table once the second database is instantiated from the second entity-relationship diagram.

2. The method of claim 1 wherein

the first data entity has metadata which is derived from a third repository of metadata stored in a computer memory, wherein the third repository of metadata is substantially different from the first and the second repositories of metadata;

and further comprising using the metadata of the first data entity in the first data model and in the second data model;

and further comprising storing a fundamental business key data attribute in computer memory;

wherein the fundamental business key data attribute forms a unique key of the first data entity;

and wherein the fundamental business key data attribute is based upon a fundamental measurement;

wherein a fundamental measurement is a measurement which is not derived from other measurements and which is determined directly from observation.

3. The method of claim 2 wherein

the first data entity is a dimensional data entity, such that the first data entity has multiple distinct levels of data record granularity.

4. The method of claim 2 further comprising

adding a second data entity and a third set of data entity relationships to the first entity-relationship diagram and storing in a computer memory, wherein the second data entity has metadata which is derived from a third repository of metadata stored in a computer memory, wherein the third repository of metadata is substantially different from the first and the second repositories of metadata;

adding the second data entity and a fourth set of data entity relationships to the second entity-relationship diagram and storing in a computer memory; and

wherein the second data entity is a unified boundary data entity in the first entity-relationship diagram, such that the second data entity is stored in computer memory and does not inherit any foreign key data attributes from any other data entity of the first entity-relationship diagram or of the second entity-relationship diagram;

and further comprising storing a fundamental business key data attribute in computer memory;

wherein the fundamental business key data attribute forms a unique key of the second data entity;

wherein the fundamental business key data attribute is based upon a data registry,

wherein the data registry is a set of data records that define reference data;

and further comprising allowing access to the set of data records of the data registry to a plurality of databases including the first database and the second database;

wherein each of the first database and the second database includes a first unified boundary database table;

and further comprising using the second data entity to instantiate the second unified boundary database table in the first database and to instantiate the second unified boundary database table in the second database; and

populating the second unified boundary database table in the first database and populating the second unified boundary database table in the second database by using the data registry.

5. The method of claim 4 wherein

the second data entity is a dimensional data entity, such that the second data entity has multiple distinct levels of data record granularity.

6. The method of claim 4 wherein

the first data entity is a dimensional data entity, such that the first data entity has multiple distinct levels of data record granularity;

the second data entity is a dimensional data entity, such that the second data entity has multiple distinct levels of data record granularity;

and further comprising adding a first summary data entity to the first entity-relationship diagram in computer memory, wherein the first summary data entity has a fifth set of data entity relationships stored in computer memory, and wherein the first summary data entity has metadata which is derived from the third repository of metadata;

wherein the first summary data entity is derived from the first plurality of data attributes;

wherein one of the fifth set of data entity relationships of the first summary data entity connects the first dimensional data entity to the first summary data entity;

wherein one of the fifth set of data entity relationships of the first summary data entity connects the second dimensional data entity to the first summary data entity;

and further comprising adding a second summary data entity to the second entity-relationship diagram in computer memory, wherein the second summary data entity has a sixth set of data entity relationships stored in computer memory, and wherein the second summary data entity has metadata which is derived from the third repository of metadata;

wherein the second summary data entity is derived from the second plurality of data attributes; and

wherein one of the sixth set of data entity relationships of the second summary data entity connects the first dimensional data entity to the second summary data entity; and

wherein one of the sixth set of data entity relationships of the second summary data entity connects the second dimensional data entity to the second summary data entity.

7. The method of claim 1 further comprising

accessing a third repository of metadata in a computer memory, wherein the third repository of metadata is substantially different from the first and the second repositories of metadata;

using the third repository of metadata to set up and store a third entity-relationship diagram in a third data model in a computer memory;

adding the first data entity to the third entity-relationship diagram in a computer memory, in a manner such that the first data entity does not inherit any foreign key data attributes from any other data entity of the third entity-relationship diagram;

wherein when the first data entity is added to the third entity-relationship diagram the third entity-relationship diagram is not linked to any populated database; and

wherein the first data entity is formed from the third repository of metadata.

8. The method of claim 7 further comprising storing a fundamental business key data attribute in computer memory;

wherein the fundamental business key data attribute uniquely identifies the first data entity;

and wherein the fundamental business key data attribute is based upon a fundamental measurement;

wherein a fundamental measurement is a measurement which is not derived from other measurements and which is determined directly from observation.

9. The method of claim 8 wherein

the first data entity is a dimensional data entity, such that the first data entity has multiple distinct levels of data record granularity.

10. The method of claim 1 further comprising

adding a first summary data entity and a third set of data entity relationships to the first entity relationship diagram stored in computer memory,

wherein the first summary data entity is formed from the first plurality of data attributes;

wherein one of the third set of data entity relationships of the first summary data entity connects the first data entity to the first summary data entity.

11. The method of claim 10 wherein

the first data entity is a dimensional data entity, such that the first data entity has multiple distinct levels of data record granularity.

12. A method comprising

forming a first database in a computer memory, wherein the first database includes a first set of database tables and a first set of foreign key constraints;

wherein the first set of database tables has a first plurality of database columns;

adding a first unified boundary database table and a second set of foreign key constraints to the first database in the computer memory;

wherein the first unified boundary database table does not inherit any of the first plurality of database columns from any of the first set of database tables and adding the first unified boundary database table does not change any of the first set of foreign key constraints;

wherein the first unified boundary database table has a second plurality of database columns, which include one or more fundamental business key database columns;

wherein the first unified boundary database table has a unique database index which is based upon one or more fundamental business key database columns;

wherein each of the one or more fundamental business key database columns is based upon a fundamental measurement or is based upon a data registry of reference data;

wherein a fundamental measurement is a measurement which is not derived from other measurements and which is determined directly from observation;

wherein the data registry is a set of reference data stored in computer memory;

wherein the set of reference data includes a plurality of data records;

wherein the plurality of data records includes a plurality of data values, wherein one or more of the plurality of data values uniquely identifies each of the plurality of data records;

wherein each of the second set of foreign key constraints references a unique database index associated with the first unified boundary database table;

wherein the unique database index is based upon one or more of the second plurality of database columns of the first unified boundary database table;

wherein the unique database index is used to uniquely identify each data record of a plurality of data records stored in the first unified boundary database table;

wherein each of the foreign key constraints of the second set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the first set of database tables, so that a first plurality of foreign key database columns is instantiated by the second set of foreign key constraints; and

wherein the first plurality of foreign key database columns are duplicates of the one or more database columns that form the basis of the unique database index of the first unified boundary database table.

13. The method of claim 12 wherein

the first unified boundary database table is a dimensional database table such that the first unified boundary database table has multiple distinct levels of data record granularity.

14. The method of claim 12 further comprising

adding a second unified boundary database table and a third set of foreign key constraints to the first database in the computer memory;

wherein the second unified boundary database table does not inherit any of the first plurality of database columns from any of the first set of database tables and adding the second unified boundary database table does not change any of the first set of foreign key constraints;

wherein the second unified boundary database table has a third plurality of database columns, which include one or more fundamental business key database columns;

wherein the second unified boundary database table has a unique database index which is based upon one or more fundamental business key database columns of the third plurality of database columns;

wherein each of the third set of foreign key constraints references a unique database index associated with the second unified boundary database table;

wherein the unique database index of the second unified boundary database table is based upon one or more of the third plurality of database columns of the second unified boundary database table;

wherein the unique database index of the second unified boundary database table is used to uniquely identify each data record of a plurality of data records stored in the second unified boundary database table;

wherein each of the foreign key constraints of the third set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the first set of database tables, so that a second plurality of foreign key database columns is instantiated by the third set of foreign key constraints; and

wherein the second plurality of foreign key database columns instantiated by the third set of foreign key constraints are duplicates of the one or more database columns that form the basis of a unique database index of the second unified boundary database table.

15. The method of claim 14 wherein

the second unified boundary database table is a dimensional database table such that the second unified boundary database table has multiple distinct levels of data record granularity.

16. The method of claim 14 further comprising

adding a first summary database table to the first database in computer memory;

wherein the first summary database table is comprised of a fourth plurality of database columns that are based on the first plurality of database columns and a fifth plurality of database columns that are based on the third plurality of foreign key database columns and the fourth plurality of foreign key database columns;

adding a fourth set of foreign key constraints to the first database in computer memory;

wherein one of the fourth set of foreign key constraints connects the first unified boundary database table to the first summary database table;

wherein the first summary database table inherits the third plurality of foreign key database columns from the first unified boundary database table, such that the third plurality of foreign key database columns are stored in the first summary database table in computer memory;

and wherein the third plurality of foreign key database columns from the first unified boundary database table are part of the fifth plurality of database columns of the first summary database table;

wherein a second one of the fourth set of foreign key constraints connects the second unified boundary database table to the first summary database table;

wherein the first summary database table inherits the fourth plurality of foreign key database columns from the second unified boundary database table;

and wherein the fourth plurality of foreign key database columns from the second unified boundary database table are part of the fifth plurality of database columns for the first summary database table.

17. The method of claim 16 wherein

the first unified boundary database table is a dimensional database table such that the first unified boundary database table has multiple distinct levels of data record granularity;

and

the second unified boundary database table is a dimensional database table such that the second unified boundary database table has multiple distinct levels of data record granularity.

18. The method of claim 16 further comprising

adding a third unified boundary database table and a fifth set of foreign key constraints to the first database in the computer memory;

wherein the third unified boundary database table does not inherit any of the first plurality of database columns from any of the first set of database tables and adding the third unified boundary database table does not change any of the first set of foreign key constraints;

wherein the third unified boundary database table has a sixth plurality of database columns, which include one or more fundamental business key database columns;

wherein the third unified boundary database table has a unique database index which is based upon one or more fundamental business key database columns of the sixth plurality of database columns;

wherein each of the fifth set of foreign key constraints references a unique database index associated with the third unified boundary database table;

wherein the unique database index of the third unified boundary database table is based upon one or more of the sixth plurality of database columns of the third unified boundary database table;

wherein the unique database index of the third unified boundary database table is used to uniquely identify each data record of a plurality of data records stored in the third unified boundary database table;

wherein each of the foreign key constraints of the fifth set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the first set of database tables, so that a plurality of foreign key database columns is instantiated by the fifth set of foreign key constraints; and

wherein the plurality of foreign key database columns instantiated by the fifth set of foreign key constraints are duplicates of the one or more database columns that form the basis of a unique database index of the third unified boundary database table.

19. The method of claim 18 wherein

the third unified boundary database table is a dimensional database table such that the third unified boundary database table has multiple distinct levels of data record granularity.

20. The method of claim 19 further comprising

adding a second summary database table to the first database in computer memory;

wherein the second summary database table is comprised of a seventh plurality of database columns that are based on the first plurality of database columns and a eighth plurality of database columns that are based on the third plurality of foreign key database columns, the fourth plurality of database columns and the fifth plurality of foreign key database columns;

adding a sixth set of foreign key constraints to the first database in computer memory;

wherein a first foreign key constraint of the sixth set of foreign key constraints connects the first unified boundary database table to the second summary database table;

wherein the second summary database table inherits the third plurality of foreign key database columns from the first unified boundary database table, such that the third plurality of foreign key database columns are stored in the second summary database table in computer memory;

and wherein the third plurality of foreign key database columns from the first unified boundary database table are part of the eight plurality of database columns of the second summary database table;

wherein a second foreign key constraint of the sixth set of foreign key constraints connects the second unified boundary database table to the second summary database table;

wherein the second summary database table inherits the fourth plurality of foreign key database columns from the second unified boundary database table, such that the fourth plurality of foreign key database columns are stored in the second summary database table in computer memory;

and wherein the fourth plurality of foreign key database columns from the second unified boundary database table are part of the eighth plurality of database columns for the second summary database table;

wherein a third foreign key constraint of the sixth set of foreign key constraints connects the third unified boundary database table to the second summary database table;

wherein the second summary database table inherits the fifth plurality of foreign key database columns from the third unified boundary database table, such that the fifth plurality of foreign key database columns are stored in the second summary database table in computer memory;

and wherein the fifth plurality of foreign key database columns from the third unified boundary database table are part of the eighth plurality of database columns of the second summary database table.

21. A method comprising

forming a first database in a computer memory from a first repository of metadata in a computer memory;

wherein the first database includes a first set of database tables and a first set of foreign key constraints;

wherein the first set of database tables has a first plurality of database columns;

populating the first database with data records after the first database is formed;

forming a second database in a computer memory from a second repository of metadata in a computer memory, wherein the second database includes a second set of database tables and a second set of foreign key constraints;

wherein the second set of database tables has a second plurality of database columns;

wherein the second repository of metadata is substantially different from the first repository of metadata;

populating the second database with data records after the second database is formed;

adding a first database table and a third set of foreign key constraints to the first database after the first database has been populated, and storing the first database table and the third set of foreign key constraints added to the first database in a computer memory, wherein the third set of foreign key constraints connect the first database table to one or more database tables in the first database; and

adding the first database table and a fourth set of foreign key constraints to the second database after the second database has been populated, and storing the first database table and the fourth set of foreign key constraints added to the second database in a computer memory, wherein the fourth set of foreign key constraints connect the first database table to one or more database tables in the second database;

populating the first database table in the first database from a first set of data records; and

populating the first database table in the second database from the first set of data records.

22. The method of claim 21 wherein

the first database table is a dimensional database table, such that the first database table has multiple distinct levels of data record granularity.

23. The method of claim 21 wherein

wherein the first database table does not inherit any of the first plurality of database columns from any of the first set of database tables in the first database and adding the first database table does not change any of the first set of foreign key constraints;

wherein the first database table does not inherit any of the second plurality of database columns from any of the second set of database tables in the second database and adding the first database table does not change any of the second set of foreign key constraints;

wherein the first database table has a third plurality of database columns, which include one or more fundamental business key database columns;

wherein the first database table has a unique database index which is based upon one or more fundamental business key database columns from the third plurality of database columns;

wherein each of the one or more fundamental business key database columns is based upon a fundamental measurement or is based upon a data registry of reference data;

wherein the fundamental measurement is a measurement which is not derived from other measurements and which is determined directly from observation;

wherein the data registry is a set of reference data stored in computer memory;

wherein the set of reference data includes a plurality of data records;

wherein the plurality of data records includes a plurality of data values, wherein one or more of the plurality of data values uniquely identifies each of the plurality of data records;

wherein each of the third and the fourth set of foreign key constraints references a unique database index associated with the first database table;

wherein the unique database index is based upon one or more of the third plurality of database columns of the first database table;

wherein the unique database index is used to uniquely identify each data record of a plurality of data records stored in the first database table;

wherein each of the foreign key constraints of the third set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the first set of database tables, so that a plurality of foreign key database columns is instantiated by the third set of foreign key constraints;

wherein each of the foreign key constraints of the fourth set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the second set of database tables, so that a plurality of foreign key database columns is instantiated by the fourth set of foreign key constraints;

and wherein the plurality of foreign key database columns are duplicates of the one or more database columns that form the basis of the unique database index.

24. The method of claim 23 wherein

the first database table is a dimensional database table, such that the first database table has multiple distinct levels of data record granularity.

25. The method of claim 21 further comprising

adding a second database table and a fifth set of foreign key constraints to the first database in computer memory, and storing the second database table and the fifth set of foreign key constraints added to the first database in a computer memory, wherein the fifth set of foreign key constraints connect the second database table to one or more database tables in the first database;

adding a second database table and a sixth set of foreign key constraints to the second database in computer memory, and storing the second database table and the sixth set of foreign key constraints added to the first database in a computer memory, wherein the sixth set of foreign key constraints connect the second database table to one or more database tables in the second database;

populating the second database table in the first database from a second set of data records; and

populating the second database table in the second database from the second set of data records; and

wherein the first database table and the second database table are formed from a third repository of metadata that is substantially different from the first repository of metadata and the second repository of metadata.

26. The method of claim 25 further comprising

using a computer processor to add a first summary database table to the first database in computer memory;

wherein the first summary database table is comprised of a third plurality of database columns have their data values derived from the data values of the first plurality of database columns of the first database and a fourth plurality of database columns that are based on a first plurality of foreign key database columns of the first database and a second plurality of foreign key database columns of the first database;

wherein the metadata of the third plurality of database columns is formed from the third repository of metadata;

using a computer processor to add a seventh set of foreign key constraints to the first database in computer memory;

wherein one of the seventh set of foreign key constraints connects a first database table of the first database to the first summary database table of the first database;

wherein the first summary database table inherits the first plurality of foreign key database columns from the first database table of the first database, such that the first plurality of foreign key database columns are stored in the first summary database table of the first database in computer memory;

and wherein the first plurality of foreign key database columns from the first database table of the first database are part of the fourth plurality of database columns of the first summary database table;

wherein a second one of the seventh set of foreign key constraints connects the second database table of the first database to the first summary database table;

wherein the first summary database table inherits the second plurality of foreign key database columns from the second database table of the first database;

and wherein the second plurality of foreign key database columns from the second database table of the first database are part of the fourth plurality of database columns for the first summary database table.

27. The method of claim 26 wherein

the first database table is a unified database table;

wherein the first unified boundary database table does not inherit any of the first plurality of database columns from any of the first set of database tables and adding the first unified boundary database table does not change any of the first set of foreign key constraints in the first database;

wherein the first unified boundary database table does not inherit any of the second plurality of database columns from any of the second set of database tables and adding the first unified boundary database table does not change any of the second set of foreign key constraints in the second database;

wherein the third plurality of database columns of the first unified boundary database table include one or more fundamental business key database columns;

wherein the first unified boundary database table has a unique database index which is based upon one or more fundamental business key database columns;

wherein each of the one or more fundamental business key database columns is based upon a fundamental measurement or is based upon a data registry of reference data;

wherein a fundamental measurement is a measurement which is not derived from other measurements and which is determined directly from observation;

wherein the data registry is a set of reference data stored in computer memory;

wherein the set of reference data includes a plurality of data records;

wherein the plurality of data records includes a plurality of data values, wherein one or more of the plurality of data values uniquely identifies each of the plurality of data records;

wherein each of the third set of foreign key constraints and the fourth set of foreign key constraints references a unique database index associated with the first unified boundary database table;

wherein the unique database index is based upon one or more of the third plurality of database columns of the first unified boundary database table;

wherein the unique database index is used to uniquely identify each data record of a plurality of data records stored in the first unified boundary database table;

wherein each of the foreign key constraints of the third set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the first set of database tables in the first database, so that a first plurality of foreign key database columns is instantiated by the third set of foreign key constraints;

wherein each of the foreign key constraints of the fourth set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the second set of database tables in the second database, so that a first plurality of foreign key database columns is instantiated by the fourth set of foreign key constraints; and

wherein the first plurality of foreign key database columns are duplicates of the one or more database columns that form the basis of the unique database index of the first unified boundary database table.

28. The method of claim 26 wherein

the first database table is a dimensional database table, such that the first database table has multiple distinct levels of data record granularity.