Apparatus and method for capturing and propagating changes from an operational database to data marts

• US 6,032,158 A
• Filed: 05/02/1997
• Issued: 02/29/2000
• Est. Priority Date: 05/02/1997
• Status: Expired due to Term

• Current Assignee: Informatica LLC (Informatica, Inc. (California))
• Original Assignee: Informatica Corporation
• Inventors: Sankaran, Mohan, Mukhopadhyay, Pinaki, Suresh, Sankaran, Nesamoney, Diaz, Gupta, Sanjeev K.
• Primary Examiner(s): VON BUHR, MARIA N
• Application Number:
  US08/850,490
• Time in Patent Office: 1,033 Days
• Field of Search: 707/101, 707/201-204, 707/10
• US Class Current: 1/1
• CPC Class Codes: G06F 16/23 Updating
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

A method for updating a target table of a data mart in response to changes made by a transaction to data stored in a source table of an operational database. Data that was changed in the source table by the transaction is stored in a dynamic image table of a change capture database. Data that was not changed in the source table by the transaction, but which is nevertheless required to be mapped to the target table, is stored in a static image table of the change capture database. The change capture database also contains relevant information regarding the transaction. Once the dynamic and static image tables are properly staged, the changes are propagated from the change capture database to the target tables of the data marts. In other words, data is extracted from the change capture database and subsequently transformed and loaded, thereby minimizing the impact to the operational database. Thereupon, the tables of the change capture database are truncated to discard data which is now no longer needed.

Claims

1. A method for updating a target table of a data mart in response to changes made by a transaction to data stored in a source table of an operational database, comprising the steps of:

determining data that was changed in the source table by the transaction;

storing the data that was changed in a first table of a dedicated databases, wherein the dedicated database is separate from the operational database;

determining data that was not changed in the source table by the transaction and which is being mapped to the target table;

storing data that was not changed in a second table of the dedicated database;

propagating the data that was changed from the first table of the dedicated database and the data that was not changed from the second table of the dedicated database to a transformation process, wherein the transformation process transforms the data without impacting the operational database;

storing transformed data in the target table of the data mart.

2. The method of claim 1 further comprising the steps of:

designating a first source table as being a primary source table, wherein changes made to the primary source table are stored in the first table of the dedicated database;

designating a second source table as being a non-primary source table, wherein data that was not changed in the source table of the operational database is stored in the second table of the dedicated database;

mapping the primary source table and the non-primary source table to the target table.

3. The method of claim 2 further comprising the steps of:

copying changes made upon the first table to the second table;

storing data from the second source table to the second table of the dedicated database.

4. The method of claim 1 further comprising the step of truncating the first table and the second table after the data has been propagated from the first table and the second table to the target table.

5. The method of claim 4 further comprising the step of assigning internal transaction identifications which are used to determine points at which the first table and the second table are to be truncated.

6. The method of claim 1 further comprising the step of ensuring that data associated only with committed transactions are propagated to the target table.

7. The method of claim 1 further comprising the step of storing a transaction table in the dedicated database, wherein the transaction table includes information corresponding to a transaction, identification of the transaction, identifications of a first log record and a last log record corresponding to the transaction, and a state of the transaction.

8. The method of claim 7 further comprising the step of storing a log record table in the dedicated database, wherein the log record table includes an identification of the operational database, an identification of the source table, and an identification of the first table corresponding to the source table.

9. The method of claim 1 further comprising the step of storing an in-memory queue of in-flight transactions, wherein the in-memory queue is consulted to determine whether a table modified by a transaction has already been modified by the transaction.

10. An apparatus for updating changes made upon an operational database to data marts, comprising:

a storage device for storing the operational database;

a client computer coupled to the operational database for changing data corresponding to a source table of the operational database;

a change capture database coupled to the client computer having a dynamic image table for storing the changes that were made to the source table and a static image table for storing historical data corresponding to the source table, wherein the change capture database is separate from the operational database;

a capture process for capturing changes made to the source table and staging the changes to the dynamic image table;

a builder process for ensuring that the historical data is present in the static image table, wherein if the historical data is not present, the builder process reads the historical data from the source table and stores the historical data in the static image table;

a transaction process for extracting the data from the change capture database, transforming the data, and loading the data to the data marts without impacting the operational database.

11. The apparatus of claim 10 further comprising a mapping table used to map source tables to target tables, wherein a user specifies a primary source table for a particular target table and all other source tables mapped to that particular target table are designated as being non-primary sources.

12. The apparatus of claim 11, wherein the builder process copies changes made upon the dynamic table to the static image table.

13. The apparatus of claim 11 further comprising a truncator process for truncating the dynamic image table and the static image table after the data has been propagated to the data marts.

14. The apparatus of claim 13, wherein internal transaction identifications are assigned to each record of the dynamic image table and static image table which are used to determine points at which the dynamic image table and the static image table are to be truncated.

15. The apparatus of claim 10, wherein the builder process ensures that data associated only with committed transactions are propagated to a target table of one of the data marts.

16. The apparatus of claim 10, wherein a transaction table is stored in the change capture database, the transaction table includes information corresponding to a transaction, identification of the transaction, identifications of a first log record and a last log record corresponding to the transaction, and a state of the transaction.

17. The apparatus of claim 16, wherein a log record table is stored in the change capture database, the log record table includes an identification of the operational database, an identification of the source table, and an identification of a first table corresponding to the source table.

18. The apparatus of claim 17, wherein an in-memory queue of in-flight transactions is consulted to determine whether a table modified by a transaction has already been modified by the transaction.