Method and apparatus for supervising extraction/transformation/loading processes within a database system

• US 7,299,216 B1
• Filed: 10/08/2002
• Issued: 11/20/2007
• Est. Priority Date: 10/08/2002
• Status: Active Grant

• Current Assignee: Taiwan Semiconductor Manufacturing Company Limited
• Original Assignee: Taiwan Semiconductor Manufacturing Company Limited
• Inventors: Liang, Shih-Tsung, Chien, Chiu-Fang
• Primary Examiner(s): Alam; Shahid
• Assistant Examiner(s): Ly; Anh
• Application Number:
  US10/266,409
• Time in Patent Office: 1,869 Days
• Field of Search: 707100-1041, 707/3, 707/6, 707/204, 707200-202, 707/1, 707/2, 707/4, 705/8, 705/10, 709/224
• US Class Current: 1/1
• CPC Class Codes: G06F 11/0727 in a storage system, e.g. in a DASD or network based storage system drivers for digital recording or reproducing units G06F3/06; circuits for error detection or correction within digital recording or reproducing units G11B20/18; for distributed storage of data in networks, e.g. transport arrangements for network file system [NFS], storage area networks [SAN] or network attached storage [NAS], H04L67/1097
• CPC Class Codes: G06F 11/0781 Error filtering or prioritizing based on a policy defined by the user or on a policy defined by a hardware/software module, e.g. according to a severity level
• CPC Class Codes: G06F 11/3476 Data logging G06F11/14, G06F11/2205 take precedence
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A method and apparatus that catalogs the characteristics and scheduling of multiple ETL procedures or processes as the multiple ETL procedures extract, transform data from a first database to load the data to a second database. The method and apparatus catalogs the progress and success of execution of multiple ETL procedures. The method and apparatus determines the necessary scheduling for cleansing operations of the first database, then initiates the cleansing operation of the first database, and then catalogs the progress of a cleansing process performed on a database to eliminate inconsistencies, redundancies, and corruptions of the database. The method and apparatus determines any errors in the execution of the ETL procedures and transmits an error message describing the errors to a person.

Claims

1. A method for the supervision of at least one extraction/transformation/loading (ETL) procedure of a first database to create a second data base comprising the steps of:

logging procedure records for said ETL procedure detailing characteristics and scheduling of said ETL procedure;

logging control records for said ETL procedure detailing status of said ETL procedure;

logging a cleansing record for said ETL procedure detailing a cleansing schedule for said first database;

logging errors associated with the logging of procedure records end control records;

evaluating a severity of the errors for determining whether a personnel is to be notified,

wherein logging control records for said ETL procedure comprises;

recording the name for said ETL procedure;

recording a trigger table for said ETL procedure, said trigger table defining a staging source table for said ETL procedure;

recording a key field within said trigger table to provide a reference for said ETL procedure;

recording a current time for said ETL procedure indicating that said ETL procedure is executed;

recording an error message for an abnormal data transfer during said ETL procedure; and

recording an error code for said abnormal data transfer during said ETL procedure.

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

determining a scheduling for execution of said ETL procedure; and

at a time when said ETL procedure is scheduled to be executed, executing said ETL procedure,

3. The method of claim 1 futher comprising the steps of:

determining from said ETL procedure a scheduling for cleansing said first database; and

at a time when said cleansing is scheduled to be executed, cleansing said first database to eliminate inconsistencies, redundancies, and corruptions from said first database.

4. The method of claim 1 wherein logging procedure records for said ETL procedure comprises the steps of:

recording a name for said ETL procedure;

recording a subject for said ETL procedure;

recording a sequence number of said ETL procedure, said sequence number indicative of an order of said ETL procedure in grouping of related ETL procedures;

recording a trigger table for said ETL procedure, said trigger table defining a staging source table for said ETL procedure;

recording a key field within said trigger table to provide a reference for said ETL procedure;

recording a period at which said ETL procedure is executed;

recording a last time at which said ETL procedure was updated; and

recording a current time for said ETL Proc e indicating that said ETL procedure is executed.

5. The method of claim 1 further comprising the step of:

communicating said error message to alert a person of the abnormal data transfer during said ETL procedure.

6. An apparatus of a computer processor for the supervision of at least one extraction/transformation/loading (ETL) procedure of a first database to create a second data base comprising the steps of:

logging procedure records for said ETL procedure detailing characteristics and scheduling of said ETL procedure;

logging control records for said ETL procedure detailing status of said ETL procedure;

logging a cleansing record for said ETL procedure detailing a cleansing schedule for said first database;

logging errors associated with the logging of procedure records end control records;

evaluating a severity of the errors for determining whether a personnel is to be notified,

wherein logging control records for said ETL procedure comprises;

recording the name for said ETL procedure;

recording a trigger table for said ETL procedure, said trigger table defining a staging source table for said ETL procedure;

recording a key field within said trigger table to provide a reference for said ETL procedure;

recording a current time for said ETL procedure indicating that said ETL procedure is executed;

recording an error message for an abnormal data transfer during said ETL procedure; and

recording an error code for said abnormal data transfer during said ETL procedure.

7. The apparatus of claim 6 further comprising the steps of:

determining a scheduling for execution of said ETL procedure; and

executing said ETL procedure, at a time when said ETL procedure is scheduled to be executed.

8. The apparatus of claim 6 further comprising the steps of:

determining from said ETL procedure a scheduling for cleansing said first database; and

cleansing said first database to eliminate inconsistencies, redundancies, and corruptions from said first database, at a time when said cleansing is scheduled to be executed.

9. The apparatus of claim 6 wherein logging procedure records for said ETL procedure comprises the steps of:

recording a name for said ETL procedure;

recording a subject for said ETL procedure;

recording a sequence number of said ETL procedure, said sequence number indicative of an order of said ETL procedure in grouping of related ETL procedures;

recording a trigger table for said ETL procedure, said trigger table defining a staging source table for said ETL procedure;

recording a key field within said trigger table to provide a reference for said ETL procedure;

recording a period at which said ETL procedure is executed;

recording a last time at which said ETL procedure was updated; and

recording a current time for said ETL procedure indicating that said ETL procedure is executed.

10. The apparatus of claim 6, further comprising the step of:

communicating said error message to alert a person of the abnormal data transfer during said ETL procedure.

11. A database processing system comprising;

a plurality of database storage units containing a plurality of databases;

at least one extraction/transformation/loading (ETL) procedure in communication with the plurality of databases to execute ETL procedures to transfer database information between the database storage units; and

an ETL procedure supervisor in communication with the plurality of database storage units and the ETL procedures for controlling and monitoring execution of ETL procedures between a first database and a second database incorporated within the plurality of database storage units; and

a procedure recorder in communication with the second database to recieve logging information from said ETL procedure detailing characteristics and scheduling of said ETL procedure upon said second database, wherein the procedure recorder executes the steps of;

recording a name for said ETL procedure;

recording a subject for said ETL procedure;

recording a sequence number of said ETL procedure, said sequence number indicative of an order of said ETL procedure in grouping of related ETL procedures;

recording a trigger table for said ETL procedure, said trigger table defining a staging source table for said ETL procedure;

recording a key field within said trigger table to provide a reference for said ETL procedure;

recording a period at which said ETL procedure is executed;

recording a last time at which said ETL procedure was updated;

recording a current time for said ETL procedure indicating that said ETL procedure is executed, and wherein said procedure controller records an error message for an abnormal data transfer during said ETL procedure and an error code for said abnormal data transfer during said ETL procedure;

wherein the ETL procedure supervisor executes the steps of;

logging errors associated with the logging of procedure records and control records; and

evaluating a severity of the errors for determining whether a personnel is to be notified.

12. The database processing system of claim 11 wherein the ETL procedure supervisor comprises:

a procedure recorder in communication with the second database to receive logging information from said ETL procedure detailing characteristics and scheduling of said ETL procedure upon said second database; and

an ETL procedure controller in communication with an ETL procedure for controlling said ETL procedure and for receiving logging control information from said ETL procedure detailing status of said ETL procedure.

13. The database processing system of claim 11 wherein the ETL procedure supervisor further comprises:

a cleansing controller in communication with the first database to institute cleansing of said first database and for receiving cleansing information from said first database detailing a cleansing schedule for said first database.

14. The database processing system of claim 11 wherein the BTL procedure controller executes the steps of:

determining a scheduling for execution of said ETL procedure; and

at a time when said ETL procedure is scheduled to be executed, executing said ETL procedure.

15. The database processing system of claim 11 wherein the cleansing controller executes the steps of:

determining from said ETL procedure a scheduling for cleansing said first database; and

at a time when said cleansing is scheduled to be executed, cleansing said first database to eliminate inconsistencies, redundancies, and corruptions from said first database.

16. The database processing system of claim 11 further comprising:

communication controller in communication with the ETL procedure controller to transmit said error message so as to alert a person of the abnormal data transfer during said ETL procedure.

17. A computer-implemented database system comprising:

a plurality of database storage units each containing a plurality of databases; and

at least one execution processor in communication with the plurality of databases to execute extraction/transformation/loading (ETL) procedures for transferring database information between the database storage units and to execute at least one ETL supervisor process for controlling and monitoring execution of said ETL procedures between a first database and a second database incorporated within the plurality of database storage units, wherein said ETL supervisor process comprises the steps of;

recording a name for said ETL procedure;

recording a subject for said ETL procedure;

recording a sequence number of said ETL procedure, said sequence number indicative of an order of said ETL procedure in grouping of related ETL procedures;

recording a trigger table for said ETL procedure, said trigger table defining a staging source table for said ETL procedure;

recording a key field within said trigger table to provide a reference for said ETL procedure;

recording a period at which said ETL procedure is executed;

recording a last time at which said ETL procedure was updated; and

recording a current time for said ETL procedure indicating that said ETL procedure is executed, and wherein recording an error message for an abnormal data transfer during said ETL procedure;

recording an error code for said abnormal data transfer during said ETL procedure logging errors associated with the logging of procedure records and control records; and

evaluating a severity of the errors for determining whether a personnel is to be notified.

18. The computer implemented database system of claim 17 wherein the ETL supervisor process comprises the steps of:

logging procedure records for said ETL procedure detailing characteristics and scheduling of said ETL procedure;

logging control records for said ETL procedure detailing status of said ETL procedure; and

logging a cleansing record for said ETL procedure detailing a cleansing schedule for said first database.

19. The computer-implemented database system of claim 18 wherein the ETL supervisor process further comprises the steps of:

determining a scheduling for execution of said ETL procedure; and

at a time when said ETL procedure is scheduled to be executed, executing said ETL procedure.

20. The computer-implemented database system of claim 18 wherein the ETL supervisor process further comprises the steps of:

determining from said ETL procedure a scheduling for cleansing said first database; and

at a time when said cleansing is scheduled to be executed, cleansing said first database to eliminate inconsistencies, redundancies, and corruptions from said first database.

21. The computer-implemented database system of claim 18 wherein the ETL supervisor process further comprising the step of:

communicating said error message to alert a person of the abnormal data transfer during said ETL procedure.