System and method for automating ETL applications

FIELD OF THE INVENTION

p-0002The invention relates generally to data processing, and more specifically, to automating the management of ETL (Extract Transform Load) processes for populating databases.

BACKGROUND OF THE INVENTION

p-0003ETL is a general tem used to describe a process in data warehousing (or populating databases) that involves extracting data from sources, transforming it to fit the business needs, and ultimately loading it into the database.

p-0004The first part of an ETL process is to extract the data from the source systems. Many database populating projects consolidate data from many different sources. Each separate source may also use a different data organization structure and/or format. Such data sources may include relational databases and flat files, but may also include non-relational database structures. The extraction step extracts and parses the data into a format for the transform process.

p-0005The transform process typically applies rules and/or functions to the extracted data, to prepare the data to be loaded. The requirements for the transformation process are a function of the extracted data and the ultimate form of the data for the database. Such transformation rules and functions include selecting certain fields, translating coded symbols, encoding new symbols, calculating values, merging sources, etc.

p-0006The load process loads the transformed data into the database. Depending on the requirements of the organization, this process ranges widely. Some databases merely overwrite old information with new data. More complex systems can maintain a history and audit trail of all changes to the database.

p-0007An ETL process can be very complex, with significant operational problems. One such problem is scalability. By its nature, the ETL process may be performed periodically, with increasing amounts of data and data sources to process. Further, the transformation rules and functions may be continuously modified to handle data differently based on the changing needs of the business.

p-0008Another problem with running an ETL process is that it can require a significant amount of processing time, and can easily involve errors and other unforeseen conditions that can halt the process. Typically, a process stage can not be restarted, but must be run again from the beginning. This can significantly lengthen the time to perform the complete ETL process.

p-0009Still another problem with the ETL process is that it requires a large amount of effort to design and build the ETL system for a particular domain. A mid-range data warehouse project can have 200-300 separate jobs. There are few automation tools or techniques to assist the developers, who must design, build, run, and maintain such a complex system.

BRIEF SUMMARY OF THE INVENTION

p-0010The following presents a simplified summary of the invention in order to provide a basic understanding of some aspects of the invention. This summary is not an extensive overview of the invention. It is not intended to identify key or critical elements of the invention or to delineate the scope of the invention. The following summary merely presents some concepts of the invention in a simplified form as a prelude to the more detailed description provided below

p-0011An embodiment of the invention is referred to as “DataStage Service Architecture” and exists on top of IBM DataStage ETL tool. This embodiment of the present invention provides several advantages against traditional Datastage orchestration techniques. It may minimize workflow execution time, for example by optimizing CPU usage by starting an ETL job as soon as all the prerequisite jobs are finished and a job slot is available. Similarly, an embodiment automatically manages the restart activities, in that it will block the execution of all child jobs of an aborted job to prevent inconsistency. Once restarted, the embodiment completes only the jobs pending from the previous execution.

p-0012An embodiment allows all configurations to be defined and updated by using technical tables; as an example, the adding or the removing of a job within a workflow is just a matter of inserting/deleting a value in a table.

p-0013An embodiment of the present invention allows an existing ETL project to be migrated into the new architecture.

p-0014Advantages of embodiments of the present invention include reduced effort for designing, building and testing ETL systems. Depending on the proportion of architecture components and job templates used and the degree of customization applied, several person-months of architecture development can be saved on a new project. Compared to a scenario where no common guidelines, templates, architecture, or services are used across ETL jobs, an estimate is that using the invention may save 40-50% of total ETL development effort.

p-0015Other advantages include: <ul><li id="ul0001-0001" num="0000"><ul><li id="ul0002-0001" num="0015">Decreased time to market, using a pre-built architecture layer which is typically on the critical path of ETL development.</li><li id="ul0002-0002" num="0016">Decreased development risk and increased software quality through proven, comprehensive, and pre-tested framework and service components.</li><li id="ul0002-0003" num="0017">Increased flexibility by isolating ETL management services as much as possible from the ETL job design, avoiding rigid execution schemas such as vertical or horizontal banded ETL.</li><li id="ul0002-0004" num="0018">Increased maintainability through modular and standardized architecture with shared components, as well as through enforced and standardized production of technical metadata and software documentation.</li><li id="ul0002-0005" num="0019">Improved operability through standard and comprehensive operations components.</li></ul></li></ul>

p-0016An embodiment of the present invention includes a system for processing a plurality of ETL jobs with 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. It may also include 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 the plurality of jobs to run. It may also include a status table including information about which jobs have run, and information regarding a status result of each such job. The system may be stopped before all jobs have completed, whereupon restart, jobs that have not completed will be run. If a job has run and the status table indicates the job has completed, then the job will not be run again. The system may limit a number of jobs that may run at one time to a predetermined value.

p-0017The information regarding which jobs to complete before running other jobs may be in the form of a parent/child indication between a first job identifier and a second job identifier.

p-0018Another embodiment includes a display interface to generate a display of real time information regarding the plurality of ETL jobs, for example identifying which jobs have completed.

p-0019Another embodiment of the present invention includes an integration component, such as a workflow designer, to extract information from an application regarding the plurality of ETL jobs, and insert the information into the job table(s). Also, an embodiment may include a diagram generator, that extracts information from the job table and generate a diagram of jobs, the diagram including an indication of which jobs must run before other jobs. The diagram generator may use Visio® macros to generate the diagram in Visio.

p-0020The present invention may also include a method for processing a plurality of ETL jobs, which may be performed by a Workflow manager. This method may include 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. The subset of jobs to run may be kept below a predetermined value. The method also includes commencing processing the subset of jobs; and 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. It also includes updating the status table based on processing of the plurality of jobs. If the processing is stopped before all jobs are completed, upon again commencing processing of the plurality of jobs, completed jobs will not be run again.

p-0021The step of determining a subset of jobs to run includes determining which jobs have been completed based on the indication from the status table.

p-0022The method may also include generating a display of real time information regarding the plurality of jobs, wherein a status of each of the plurality of jobs is displayed. Such status may include identifying whether a job has completed.

BRIEF DESCRIPTION OF THE DRAWINGS

p-0023A more complete understanding of the present invention and the advantages thereof may be acquired by referring to the following description in consideration of the accompanying drawings, in which like reference numbers indicate like features, and wherein:

p-0024<figref idrefs="DRAWINGS">FIG. 1</figref> illustrates an overview of a business components layer of an embodiment of the present invention;

p-0025<figref idrefs="DRAWINGS">FIG. 2</figref> illustrates an overview of a metadata/semantic layer of an embodiment of the present invention;

p-0026<figref idrefs="DRAWINGS">FIG. 3</figref> illustrates an overview of a services layer of an embodiment;

p-0027<figref idrefs="DRAWINGS">FIG. 4</figref> illustrates several components forming an embodiment referred to as “DataStage Service Architecture”, as related to a Datastage ETL tool component;

p-0028<figref idrefs="DRAWINGS">FIG. 5</figref> illustrates metadata tables according to one embodiment of the present invention;

p-0029<figref idrefs="DRAWINGS">FIGS. 6A</figref> and B illustrates a flow chart of the application logic performed by the Workflow Manager;

p-0030<figref idrefs="DRAWINGS">FIG. 7</figref> illustrates a real time monitoring display according to an embodiment; and

p-0031<figref idrefs="DRAWINGS">FIG. 8</figref> illustrates a sample output of diagramming tool according to an embodiment.

DETAILED DESCRIPTION OF THE INVENTION

p-0032In the following description of the various embodiments, reference is made to the accompanying drawings, which form a part hereof, and in which is shown by way of illustration various embodiments in which the invention may be practiced. It is to be understood that other embodiments may be utilized and structural and functional modifications may be made without departing from the scope of the present invention.

p-0033An embodiment, referred to as DataStage Service Architecture (DSA), provides a complete and robust data-loading environment for different types of ETL applications (e.g. Data Warehousing, Data Integration, Data Migration, etc.) involving simple or complex data loading operations. DataStage Service Architecture is part of broader ETL accelerator suite of assets that also includes the ETL Accelerator framework described in more detail below. The ETL accelerator suite of assets according to one aspect of the invention aims to provide a jumpstart for the design and build of ETL applications, which are typically used for Data Warehouse population, Data Integration, Master Data Synchronization, or other bulk data migrations. The accelerator design works on proven strategies, principles, best practices, and components developed and used in real production environments.

p-0034Data loading in ETL projects involves business transformations and the use of common services. Business transformations are the combination of elementary tasks like: Extract (select), Sort/Filter (order, group, where), Transform (the “true business logic”), and Load (insert or update). The common services are operations to manage architecture tasks like: scheduling, parameterization, restart &amp; SLA management, audit logging, etc. An embodiment of the invention is focused on providing an execution environment for these common services.

p-0035Programming and maintenance of the ETL software can be a complex operation. To support the business component an embodiment of the present invention includes a group of service components specialized in common tasks related to the ETL orchestration. These component may be platform-dependent but business-aware so they are highly reusable. A great advantage of using a proven framework with pre-built and tested service components is that a developer can reduce the design &amp; build efforts while obtaining better software quality. Typically a savings of around 40-50% of the total ETL development effort can be achieved. An embodiment is helpful to set standard design guidelines improving overall software quality and also force the production of technical metadata that can help the developer's work and reduce the effort of documenting the software.

p-0036An overview of ETL Accelerator Framework according to an embodiment will now be provided. The ETL Accelerator Framework comprises <b>3</b> layers: business components, metadata/semantic, and services. The services and metadata layer diagrams are a foundation shared with the Accenture Delivery Architecture Business Intelligence Application Style and the Accenture Delivery Methods for Business Intelligence, provided by Accenture Inc.

p-0037The business components layer for an ETL framework is shown in <figref idrefs="DRAWINGS">FIG. 1</figref>. It contains the core business logic. A high level view of the components include the Extract (select) task <b>22</b>, Sort/Filter (order, group, where) task <b>22</b>, Transform (which includes the utilization of business logic) task <b>24</b>, and Load (insert or update) task <b>26</b>

p-0038The metadata layer, <figref idrefs="DRAWINGS">FIG. 2</figref>, may include a set of tables <b>32</b>-<b>34</b> to hold common definitions, manage common tasks, as well as the primary tool to manage the software configuration and documentation. In many cases a metadata repository <b>36</b> is provided by the ETL tool and also provides functions such as data lineage or impact analysis. Metadata can be imported from an existing environment. Further, metadata can be used to produce documentation of the current ETL application using the tools provided.

p-0039The service components, <figref idrefs="DRAWINGS">FIG. 3</figref>, support the business layer for common tasks related to the ETL orchestration. They are typically platform-dependent but business-aware, thus highly reusable. Normally in a common ETL architecture most of the control and management logic is tied together with the business logic and these are both contained in a data flow component (in DataStage this is called a job). In the approach for the present embodiment, the business logic is separated from the control and management logic, so in the DataStage job one typically puts only the business logic. This approach enables the reuse of the control and management components across multiple implementations, without re-architecting these components. For a DataStage solution, certain components such as the data population services <b>37</b> and batch services <b>38</b> are enabled by the DataStage architecture of an embodiment of the present invention. By their nature, these services <b>37</b>, <b>38</b> may be stopped and re-started as processing is performed to populate the databases.

p-0040A reject data management service <b>39</b> handles invalid data that needs to be rejected at some point during the load so that the remaining data can continue and the process can complete at some point. Reject data management is critical to let the process continue to run despite bad or malformed data. The rejected records are stored in a data container to enable further inspection and resolution of the issues causing the rejections.

p-0041A scheduler <b>42</b> may initiate the ETL process either based on schedule, event, or change data capture notice. This service is often provided either by a production-support team or through an ETL tool. The ETL tool has the advantage of tight integration with the ETL run-time environment.

p-0042Another batch service is restart/recovery <b>40</b>, which according to an embodiment allows for graceful recoveries after a failure, and will prepare the database and ETL server for restart, as described below.

p-0043Other components, grouped as common services <b>44</b>, are typically only run once. Such services may include logging &amp; audit tracing <b>46</b>. Robust logging and tracing services are helpful throughout the ETL process so that application activity and data manipulation results can be exposed to the users. Parameterization services <b>48</b> include a parameter managing service to help create a robust ETL solution. ETL jobs may be designed to accept parameter values from the scheduler rather than hard coding them. The exception handling service <b>50</b> is helpful for proper ETL execution and monitoring. Whenever an unexpected event or invalid data is found during ETL, execution the event is typically recorded. Other components <b>44</b> may include thread management services <b>52</b>, which help the embodiment split execution runs in multiple parallel processes.

p-0044An embodiment of the present invention helps implement the DataStage Service Architecture (DSA), which provides a complete and robust data-loading environment for different types of ETL projects involving simple or complex data loading operations. This DSA helps implement part of the Service Components indicated in <figref idrefs="DRAWINGS">FIG. 3</figref>. The DSA also simplifies ETL programming and maintenance by standardizing ETL job design, handling basic ETL functions such as scheduling, parameterization and restart management, and facilitating IT operations for ETL streams.

p-0045The DataStage Service Architecture may be implemented on top of IBM DataStage® ETL tool. As shown in <figref idrefs="DRAWINGS">FIG. 4</figref>, the DataStage ETL tool suite is composed of several components that can be classified as Server components and Client components.

p-0046DataStage Server Components include: <ul><li id="ul0003-0001" num="0000"><ul><li id="ul0004-0001" num="0051">a. The DataStage Repository <b>53</b>, a central store that contains all the information required to build a data mart or data warehouse. Typically the Repository <b>53</b> is stored in a proprietary layout and is not easily accessible by third-party tools. Alternatively there are some DataStage BASIC functions used to access characteristics of a particular DataStage project and extract technical Meta data. These BASIC functions are those used by the present embodiment.</li><li id="ul0004-0002" num="0052">b. The DataStage Server <b>55</b>, which runs executable jobs that extract, transform, and load data into a data warehouse.</li></ul></li></ul>

p-0047DataStage Client components include: <ul><li id="ul0005-0001" num="0000"><ul><li id="ul0006-0001" num="0054">a. The DataStage Designer <b>56</b>, a design interface used to create DataStage applications (known as jobs). Each job specifies the data sources, the transforms required, and the destination of the data. Jobs are compiled to create executables that are scheduled by the Director and run by the Server (mainframe jobs are transferred and run on the mainframe);</li><li id="ul0006-0002" num="0055">b. The DataStage Director <b>57</b>, a user interface used to validate, schedule, run, and monitor DataStage server jobs;</li><li id="ul0006-0003" num="0056">c. The DataStage Manager <b>56</b>, a user interface used to view and edit the contents of the Repository <b>53</b>;</li><li id="ul0006-0004" num="0057">d. The DataStage Administrator <b>56</b>, a user interface used to perform administration tasks</li></ul></li></ul>

p-0048The Workflow Manager <b>58</b> is a DataStage control job that controls and manages most of Architecture capabilities for this embodiment. DataStage custom routines <b>60</b> help to manage common tasks (such as auditing, error handling). Technical metadata tables <b>62</b> are used to configure the architecture. Also, auditing reports <b>64</b> are used to monitor ETL execution. Each of these components will be discussed further below.

p-0049In one embodiment, the Workflow Manager <b>58</b> implements a majority of the ETL services. These include determining the execution order of the ETL procedures <b>42</b>, <figref idrefs="DRAWINGS">FIG. 3</figref>; restart/recovery for ETL procedures that failed in a previous session <b>40</b>; and auditing ETL procedure events and activity (messages/error/records processed) <b>46</b>. Other such services may include managing exception handling <b>50</b>; retrieving ETL procedure parameters <b>48</b>; and tracking workflow execution <b>46</b> (and also Monitoring but this box has not been numbered)

p-0050In this embodiment, the Workflow Manager <b>58</b> uses a set of technical metadata tables <b>62</b> to configure the behavior of the above-described services. Local and Global execution parameters such as maximum number of running ETL Jobs, maximum number of warning allowed, SLA threshold, are set depending on the characteristics and size of the project and on the target environment. The technical metadata tables <b>62</b> will be described in further detail below.

p-0051The DSA allows ETL procedure development to be divided and distributed to many programmers that follow only few design guidelines. Examples include configuring ETL jobs that are designed to receive a set of external parameters from the scheduler <b>42</b>; and also configuring ETL jobs that are as atomic as possible in order to allow restart &amp; recovery procedures.

p-0052A representation of the logical data model of the technical metadata tables <b>62</b> is shown in <figref idrefs="DRAWINGS">FIG. 5</figref>. As previously described, these tables include parameters and values for jobs to be performed by the Workflow Manager <b>58</b>. In this embodiment, the tables <b>62</b> are populated by data using programming tools (as described below) or by data imported from ETL projects that do not utilize the DataStage Service Architecture (also described below).

p-0053For this embodiment, certain fundamental tables are maintained and utilized for each job. The TORAFULL01TECJOBSTATUS table <b>62</b><i>a </i>is the Job Master Data table and contains information for each job about the source system, subject area, warning level, and information about the job's latest run status including when execution started and status at completion. This information about the last execution run is updated at every run and is used to manage the restart and recovery activities. This table also include a priority level for each job. The priority is useful for selecting an order for which job(s) to run first when multiple jobs are ready to be run at the same time. An example of this is when a parent job finishes, multiple child jobs that depend on the parent job finishing may become eligible to run. Different child jobs may have different running requirements (for example time to run, processor load, etc.) If these child jobs are all started simultaneously, or a subset chosen randomly, the process may be unstable or slow. By providing a priority for one or more child jobs, the embodiment may select and run child jobs in an efficient manner. The parent-child relationship is maintained in the TORAFULL03TECPARENTJOB table <b>62</b><i>c </i>

p-0054The TORAFULL02TECJOBPARAMETER table <b>62</b><i>b </i>contains the list and the values of all the local parameters used by each job during execution. The information in this table is typically read-only at runtime, but in exception cases, a job can be enabled to modify some values, however, typically this modification practice is not recommended

p-0055The TORAFULL03TECPARENTJOB table <b>62</b><i>c </i>records what the parent job are for each dependent or child job. A parent job is a job that must complete before the child job can start. A job can have multiple parents, so all parent jobs should finish before starting a child job. Each parent job can have one or more child jobs. This table is read-only at runtime. This parent-child setup defines what order the jobs may be run in, with the parent jobs preceding the child jobs. When there is more than one child job, the priority level value contained in the table TORAFULL01TECJOBSTATUS table <b>62</b><i>a </i>will decide which job will start first.

p-0056The TORAFULL04TECALLLINKJOB table <b>62</b><i>d </i>contains the names of all the data links and transformers contained in each job. This table is read only at runtime and it is used by the DSA auditing procedures. A data link can be tagged as Input/Output/Error link depending on the position in the data flow logic. The DSA auditing procedures extract the number of rows processed for each links and aggregates this data according to the Input/Output/Error classification to provide meaningful execution statistics.

p-0057Other tables may be created or populated during a job. For example, the TORAFULL01TECHHISTORYJOBSTATUS table <b>62</b><i>e </i>contains the history of all job execution statistics for the entire workflow session. The TORAFULL05TECHEVENTLOG table <b>62</b><i>f </i>includes runtime error information, which allows the embodiment to process and analyze the cause that generates the error, and restart the job once the error is corrected

p-0058The TORAFULL07TECKPI table <b>62</b><i>g </i>contain the threshold value for the proposed JOB and AREA KPI to support SLA management. This table is optional and we do not need to use it for normal operations. It is useful when a user wants to enhance the reporting and monitoring capabilities of the DSA, especially when a job reads data from an external source systems or when a job must finish within a particular time window.

p-0059The Workflow Manager <b>58</b> typically uses SQL (Structured Query Language) to access the tables <b>62</b>. When the Workflow Manager <b>58</b> starts to process the workflow, a new session identifier value is generated and stored in the SESSIONID field.

p-0060An embodiment of the Workflow Manager processes jobs as set out in <figref idrefs="DRAWINGS">FIG. 6</figref>. As previously described, the present invention as set forth in this embodiment allows ETL jobs to be processed in a manner that allows prioritization, restarts and recovery, and the ability to audit jobs during and after running. As such, the Workflow Manager performs job execution and monitoring in accordance with the steps as set forth in <figref idrefs="DRAWINGS">FIG. 6</figref>.

p-0061The Workflow Manager <b>58</b> initializes the workflow, step <b>200</b>, and then determines the number of jobs pending from previous runs, step <b>202</b>. Typically pending job are those that have not been executed due to error condition on the job itself or in the parent job.

p-0062At step <b>204</b>, the Workflow Manager determines if there are jobs to be run again. These are jobs that were previously started, but did not complete, typically because of errors or priority problems. If there are such jobs, the Workflow Manager updates the SESSIONID field in the metadata tables <b>62</b><figref idrefs="DRAWINGS">FIG. 5</figref>, and updates the pilot data. The pilot data is a datetime value managed globally by the Workflow manager, it can be used by each job to manage incremental loads, step <b>208</b>.

p-0063If there are not jobs to be run again, but there are new jobs to commence, the Workflow Manager initializes the status of all such jobs, step <b>206</b>. Next, the SESSIONID field and pilot data are updated, step <b>208</b>. The Workflow Manager then determines the number of jobs to run, step <b>210</b>. Information about limits on numbers of jobs to run will be described below.

p-0064At this point, the Workflow Manager commences a loop to allow job processing in accordance with an embodiment. If there are no jobs to run, step <b>212</b>, then the process is done, and the process ends, step <b>214</b>. However, if there are jobs to run, the process continues. The Workflow Manager determines the next job to execute, based on the parent-child relationship and priority values, step <b>216</b>. Once the next job is selected, the Workflow Manager checks whether the job exists in the repository. This is necessary to avoid Workflow Manager failure, because a job can be listed in the technical tables but could be absent from the DataStage repository or have a different name. In the case where the job does not exist, the STATUS field on the TORAFULL01TECJOBSTATUS table is updated step <b>220</b> and the process ends. All the other pending jobs will not be executed to avoid process inconsistency. This conservative behavior allows an operator to check and correct the workflow structure.

p-0065If at step <b>218</b> it is determined that an executable job exists, the next step <b>222</b> is to determine if this job has a runnable status (compiled). If not (job aborted or not compiled) the Workflow Manager stops the process executions, halting all the running jobs. Then as in the previous bullet the STATUS field in the TORAFULL01TECJOBSTATUS table for the affected job is updated, step <b>224</b>. This update allows the operators to know what is happening in the process using the predefined reports.

p-0066At step <b>226</b>, the Workflow Manager prepares the job and commences execution. In this embodiment, up to ten jobs may execute at one time, however the invention is not limited to any such number of jobs. The Workflow Manager keeps track of the number of jobs running, and if more slots to run jobs are available, step <b>228</b>, the process goes back to step <b>212</b> to start up other pending jobs. Otherwise the process continues until a running job finishes, step <b>230</b>, at which time the Workflow Manager performs post-execution operations, including the update of the job status fields in the TORAFULL01TECJOBSTATUS, recording of job execution information in the TORAFULL01TECHISTJOBSTATUS table and the recording of auditing information in the TORAFULL05TECEVENTLOG table step <b>232</b>.

p-0067This process as described allows the Workflow Manager to process multiple jobs that have priorities and sequence requirements, as well as work with jobs that may be stopped and restarted multiple times as errors and other conditions are addressed.

p-0068An embodiment of the present invention includes a user console with a GUI that provides configuration and control over the process. This console allows users to gain access by providing a username and password. The user can then perform certain tasks including: <ul><li id="ul0007-0001" num="0000"><ul><li id="ul0008-0001" num="0079">Job Search: to perform a search on ETL database for given job name.</li><li id="ul0008-0002" num="0080">Job Management: to configure, modify, delete a job and related attributes.</li><li id="ul0008-0003" num="0081">Kpi Search: to perform a search on ETL database for given Kpi name (KPI=Key Performance Indicator).</li><li id="ul0008-0004" num="0082">Kpi Management: to configure, modify, delete Kpi.</li><li id="ul0008-0005" num="0083">Add user: to add a new user.</li><li id="ul0008-0006" num="0084">Password management: to manage user's password.</li><li id="ul0008-0007" num="0085">Tools to enter data for and modify meta data tables <b>62</b>.</li></ul></li></ul>

p-0069<figref idrefs="DRAWINGS">FIG. 7</figref> illustrates a display provided by the console according to one embodiment. This display shows a predefined reporting table to allow real-time monitoring of current sessions. Information about the status of each job is presented, including start and end times, status of run, error counts, etc. Examples of other reports include execution trend reports and SLA monitoring at the session level, job level and subject area level.

p-0070A feature of another embodiment is shown in <figref idrefs="DRAWINGS">FIG. 8</figref>. This embodiment, called Workflow Designer, may generate a workflow diagram based on information automatically extracted from the metadata tables <b>62</b>. The workflow diagram visually lays out the job procession ordering and dependency for a process, allowing a user to easily understand complicated job requirements and ordering. In this embodiment, the workflow diagram is created by extracting data from the metadata tables <b>62</b>, and the diagram is automatically generated in Microsoft Visio® using macros. However, this workflow diagram may be created in a variety of different ways, and may be configured to provide more or less information. The Workflow designer uses SQL statements and temporary tables to generate a tabular representation of the workflow structure. The tool uses Microsoft Visio Object Model to draw each job as a box and connect the boxes according to the parent-child relationship. Within each box some useful information about the job is written, including the job name and the execution parameters. The Workflow designer can be considered an extension of the DSA because is based on the same technical tables that support the DSA.

p-0071Another embodiment of the present invention includes tools to help migrate and convert pre-existing DataStage ETL applications to an architecture based on the DataStage Service Architecture. A complex ETL integration project can have hundreds of jobs, thus the data entry can be non-trivial and error prone. A utility referred to as ReadCurrentConfiguration may read an existing architecture, and extract some information regarding job names, parameter names, and data links and transformers names within every job. Details for this information is inserted in the metadata tables, for example TORAFULL01TECJOBSTATUS <b>62</b><i>a</i>, TORAFULL02TECJOBPARAMETER <b>62</b><i>b</i>, TORAFULL03TECPARENTJOB <b>62</b><i>c</i>, and TORAFULL04TECALLLINKJOB <b>62</b><i>d</i>. Typically, the extracted information is not exhaustive for correct process execution and should be reviewed and completed manually, as indicated in the next point.

p-0072Once the metadata tables <b>62</b> are completely loaded, the population architecture may be designed. This may include categorizing jobs in subject areas, determining the parent-child relationship based on functional dependencies, and determine the importance of a job in terms of duration and in terms of number of child jobs.

p-0073The DataStage Service Architecture can be applied in any DataStage implementation and doesn't require a particular ETL job design approach. Typically the jobs should be designed to receive a set of external parameters from the scheduler. If existing jobs have their own parameter management, it is helpful to understand how to integrate this with the service architecture. Another useful general guideline is that ETL procedures should be designed to be as atomic as possible, in order to better manage restart &amp; recovery processes. This can be accomplished in an embodiment by using frequent staging points. Based on the final population architecture and on the server size a maximum number of processes that can be executed in parallel can be determined and set.

p-0074One or more aspects of the invention may be embodied in computer-usable data and computer-executable instructions, such as in one or more program modules, executed by one or more computers or other devices. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular abstract data types when executed by a processor in a computer or other device. The computer executable instructions may be stored on a computer readable medium such as a hard disk, optical disk, removable storage media, solid state memory, RAM, etc. As will be appreciated by one of skill in the art, the functionality of the program modules may be combined or distributed as desired in various embodiments. In addition, the functionality may be embodied in whole or in part in firmware or hardware equivalents such as integrated circuits, field programmable gate arrays (FPGA), and the like. Particular data structures may be used to more effectively implement one or more aspects of the invention, and such data structures are contemplated within the scope of computer executable instructions and computer-usable data described herein.

p-0075Although the subject matter has been described in language specific to structural features and/or methodological acts, it is to be understood that the subject matter defined in the appended claims is not necessarily limited to the specific features or acts described above. Rather, the specific features and acts described above are disclosed as example forms of implementing the claims.