SYSTEM AND METHOD FOR AUTOMATING ETL APPLICATION

• US 20090177671A1
• Filed: 03/28/2008
• Published: 07/09/2009
• Est. Priority Date: 01/03/2008
• Status: Active Grant

• Current Assignee: Accenture Global Services Limited (Accenture PLC)
• Original Assignee: Accenture Global Services GmbH (Accenture PLC)
• Inventors: PELLEGRINI, PAOLO, Grasso, Antonio
• Granted Patent: US 8,024,369 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 9/5038 considering the execution order of a plurality of tasks, e.g. taking priority or time dependency constraints into consideration scheduling strategies G06F9/4881 and subgroups

Abstract

Novel tools for development and operation of ETL (Extract Transform Load) systems for populating databases. An embodiment uses metadata tables to describe relationships between jobs to run for processing data. These relationships can include parent-child job relation, and priority. These tools create a DataStage Service Architecture (DSA) that helps automate and control the ETL process. Other tools allow developers to easily view and update the ETL process.

Claims

1. A system for processing a plurality of ETL jobs comprising:

a job table including a plurality of job identifiers, each job identifier identifying a job to run, wherein the job table further includes information regarding which jobs to complete before running other jobs;

a priority table including priority information for at least one job identifier in the job table;

wherein if a job completes and a plurality of jobs may now run, the system utilizes priority information in the priority table to determine one of said plurality of jobs to run.

2. The system of claim 1 further including:

a status table including information about which jobs have run, and information regarding a status result of each such job.

3. The system of claim 2 wherein if a job has run and the status table indicates the job has completed, then the job will not be run again.

4. The system of claim 1 wherein the information regarding which jobs to complete before running other jobs includes a parent/child indication between a first job identifier and a second job identifier.

5. The system of claim 1 wherein the system may be stopped before all jobs have completed, whereupon restart, jobs that have not completed will be run.

6. The system of claim 2 further including display interface, to generate a display of real time information regarding the plurality of ETL jobs.

7. The system of claim 6 wherein such information includes identifying which jobs have completed.

8. The system of claim 1 wherein the system limits a number of jobs that may run at one time to a predetermined value.

9. The system of claim 1 further including:

an integration component, to extract information from an application regarding the plurality of ETL jobs, and insert the information into the job table.

10. The system of claim 1 further include a diagram generator, the diagram generator to extract information from the job table and generate a diagram of jobs, the diagram including an indication of which jobs must run before other jobs.

11. The system of claim 10 wherein the diagram generator uses Visio®

macros to generate the diagram in Visio.

12. A method for processing a plurality of ETL jobs comprising:

determining from a job table a plurality of jobs to run, wherein the job table further includes information regarding which jobs to complete before running other jobs;

determining from a status table a status for the plurality of jobs to run, wherein the status table includes an indication of which jobs have completed successfully and which jobs need to be run again;

determining a subset of jobs to run based on the information from the job table and the status table

commencing processing the subset of jobs;

if a job in the subset of jobs completes, determining another job to run, wherein the determination includes utilizing job priority information if a plurality of jobs may now run; and

updating the status table based on processing of the plurality of jobs.

13. The method of claim 12 wherein the step of determining a subset of jobs to run includes determining which jobs have been completed based on the indication from the status table.

14. The method of claim 12 further including:

ceasing processing of the plurality of jobs before the plurality of jobs have been completed successfully, whereupon again commencing processing of the plurality of jobs, completed jobs will not be run again.

15. The method of claim 12 further including:

generating a display of real time information regarding the plurality of jobs, wherein a status of each of the plurality of jobs is displayed.

16. The method of claim 15 wherein such status includes identifying whether a job has completed.

17. The method of claim 12 wherein the subset of jobs to run is below a predetermined value.

18. The method of claim 12 further including:

extracting information from an application regarding the plurality of jobs, the information including job names and job parameters; and

inserting the information into the job table.

19. The method of claim 12 further including:

extracting information from the job table, the information including job names and which jobs to complete before running other jobs; and

generating a diagram of jobs, the diagram including job names and an indication of which jobs must run before other jobs.

20. The method of claim 19 wherein Visio®

macros are utilized to generating the diagram.

21. A computer readable storage medium, including instructions that when supplied to a processor, cause the processor to perform:

determining from a job table a plurality of jobs to run, wherein the job table further includes information regarding which jobs to complete before running other jobs;

determining from a status table a status for the plurality of jobs to run, wherein the status table includes an indication of which jobs have completed successfully and which jobs need to be run again;

determining a subset of jobs to run based on the information from the job table and the status table

commencing processing the subset of jobs;

if a job in the subset of jobs completes, determining another job to run, wherein the determination includes utilizing job priority information if a plurality of jobs may now run; and

updating the status table based on processing of the plurality of jobs.

22. The computer readable storage medium of claim 21, further including instructions to cause the processor to determine which jobs have been completed based on the indication from the status table when determining a subset of jobs to run.

23. The computer readable storage medium of claim 21, further including instructions to cause the processor to perform:

ceasing processing of the plurality of jobs before the plurality of jobs have been completed successfully, whereupon again commencing processing of the plurality of jobs, completed jobs will not be run again.

24. The computer readable storage medium of claim 21, further including instructions to cause the processor to generate a display of real time information regarding the plurality of jobs, wherein a status of each of the plurality of jobs is displayed.