Another Way to Create an ETL Daily Run Report

  • by Karen Olsen, Business Intelligence Analyst/Developer for W. W. Grainger
  • February 1, 2004
With so many reports that must be run daily, it can be difficult to determine the best way to track jobs in BW. The author shares her success with creating a custom table to track InfoPackages being transported to production. The table provides users with access to vital and accurate information about the status of their jobs.

 

After successfully implementing our first phase of BW (Accounts Payable), my team began to wrestle with how to best manage and monitor jobs. With a well-established data warehouse supporting over 1,000 jobs that ran daily, weekly, and monthly, we fully appreciated the value of job tracking and knew how important it would be to accurately follow loads as they became increasingly complicated and numerous. We began by making use of the many capabilities of the BW Monitor, and we implemented all the available cubes from BW Statistics, but we still found some gaps. We were unable to associate a set of load jobs with a particular set of users, for example, and we could not know for sure if a job finished successfully after loading zero records.

We set out to find a way to provide our users with answers when they called asking about the status of their jobs. For instance, how could we determine with certainty that all the A/P data loaded successfully, and that the one job sitting out in the failed queue was not actually for the particular user calling? Moreover, for those jobs that failed to run, we wanted to be able to find specific problems such as difficulties activating the ODS data. In addition to monitoring which jobs did and did not run, we wanted to be able to identify anomalies such as when a job suddenly started running more slowly than usual, which could indicate a change in the source that we needed to be aware of, or if fewer records were loading than we expected.

The solution was to create a new custom Z table to track all the InfoPackages being transported to production. We constructed the table also to provide other information related to the job such as the implementation date, project name, frequency, comments, source, and more to help us recognize the InfoPackage and why the data is being loaded. In addition, our customized table lets us monitor the minimum and maximum number of records we expect to load in each job as well as the minimum and maximum number of seconds we estimate it will take to run the job. Although there were other options such as improving the BW master data and InfoSources related to OTCT* objects in BW Statistics, we decided to go in this direction.

Karen Olsen

Karen Olsen, Business Intelligence Analyst/Developer for W. W. Grainger located in Lake Forest, IL, has been working in the data warehousing field for the past six years in various roles. Her current responsibilities at W.W. Grainger include implementing SAP Business Warehouse and converting an existing data warehouse to the new BW environment.

See more by this author


Comments

No comments have been submitted on this article. 


Please log in to post a comment.

To learn more about subscription access to premium content, click here.